Índice de linhas inversas em um intervalo como uma variável em uma fórmula

Question

Eu tenho uma tabela de entradas para cada dia. A célula B (n) deve conter uma soma de cálculos intermediários para os dias anteriores (n1 - nn), em que um dos argumentos é um delta em número de dias entre n e nx.

Por exemplo, dada tabela

Day  A  B
1    3  0
2    5  3
3    7  11
4    8  26
5    9  49

Os valores de

para os dias n = 4 e n = 5 são calculados da seguinte forma:

B4 = A1*3 + A2*2 + A3*1 + A4*0
B5 = A1*4 + A2*3 + A3*2 + A4*1 + A5*0

Onde multiplicadores no lado direito são deltas em dias entre n e nx. Eu imagino que esta seja uma posição inversa em um intervalo.

ipos(A2,A1:A5)=3

Talvez algo como

B(n) = SUM(A1:An * ipos())

Alguma idéia de como escrever uma fórmula para isso? Obrigado.

EDITAR: Obrigado pelas respostas. Talvez eu não tenha descrito o problema suficientemente. Na verdade, estou tentando calcular a concentração máxima aproximada do medicamento administrado para cada dia, dada a dose para cada dia e a meia-vida da droga.

Day Dosage Peak
1   2,50   2,50
2   3,17   5,67
3   4,00   9,67
4   4,00   13,67
5   5,00   17,906

meia-vida (horas):

hl = 80

número de meias-vidas decorridas:

he(days_ago) = days_ago * 24 / 80

quantidade restante após a metade da vida ter decorrido:

q_n(dosage, he(days_ago)) = MIN(1; 1 / MAX(1; he(days_ago))^2) * dosage

E, finalmente, o pico de concentração total para o dia n:

q(n) = q_1(dosage_1, he(n-1)) + q_2(dosage_2, he(n-2)) + .. q_n(dosage_n, he(0))

Exemplo, dia n = 5:

q(5) = q_1(2,50, he(4)) + q_2(3,17, he(3)) + q_3(4,00, he(2)) + q_4(4,00, he(1)) + q_5(5,00, he(0))

q(5) = q_1(2,50, 1.2) + q_2(3,17, 0.9) + q_3(4,00, 0.6) + q_4(4,00, 0.3) + q_5(5,00, 0)

q(5) = MIN(1; 1 / MAX(1; 1.2)^2) * 2,50 + MIN(1; 1 / MAX(1; 0.9)^2) * 3,17 

    + MIN(1; 1 / MAX(1; 0.6)^2) * 4,00 + MIN(1; 1 / MAX(1; 0.3)^2) * 4,00 

    + MIN(1; 1 / MAX(1; 0)^2) * 5,00

q(5) = 0.6944 * 2,50 + 1 * 3,17 + 1 * 4,00 + 1 * 4,00 + 1 * 5,00
q(5) = 17.906

    

Answer 1

Experimente o poder das referências mistas.

= SE (ISNUMBER (C1), C1 + SUM ($ B $ 1: B1), 0)

Copie para baixo.

    

Answer 2

Esta é uma função definida pelo usuário do Visual Basic (UDF) ;

Public Function iSum(rng As Range) As Long
    Dim K As Long
    K = rng.Count - 1

    For Each r In rng
        iSum = iSum + K * r
        K = K - 1
    Next r
End Function

Provavelmente, é possível fazer isso com SUMPRODUCT () , mas não consigo descobrir como fazer os multiplicadores.

    

Answer 3

1. Realce suas células, incluindo os títulos das colunas
2. Inserir tabela (verifique se você tem uma linha de cabeçalho)
3. Altere o nome da sua tabela para "MinhaTabela" ou algo assim. (Embora sua seleção esteja dentro da tabela, o nome da tabela deve estar na extremidade esquerda da faixa de opções Ferramentas de Tabela.)
4. Para cada célula em B, altere a fórmula para =IF(ROW()-ROW(MyTable[#Headers])=1,0,OFFSET([@B],-1,0)+SUM(OFFSET(MyTable[[#Headers],[A]],1,0,ROW()-ROW(MyTable[#Headers])-1)))

Edit: Eu deveria explicar essa fórmula. Traduzido para o inglês diz ...

• Se eu for a primeira linha de dados na tabela, retorne 0
• Caso contrário, retorne a célula acima de mim mais a soma dos valores em A acima de mim.