Индекс обратной строки в диапазоне как переменная в формуле

Question

У меня есть таблица записей на каждый день. Ячейка B(n) должна содержать сумму промежуточных вычислений за предыдущие дни (n1 - nn), где один из аргументов представляет собой дельту в количестве дней между n и nx.

Например, приведенная таблица

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

Значения для дней n = 4 и n = 5 рассчитываются следующим образом:

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

Где множители справа - это дельты в днях между n и nx. Я представляю, что это обратная позиция в диапазоне.

ipos(A2,A1:A5)=3

Возможно что-то вроде

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

Есть идеи, как написать формулу для этого? Благодарю.

РЕДАКТИРОВАТЬ: Спасибо за ответы. Возможно я не описал проблему достаточно. На самом деле, я пытаюсь рассчитать приблизительную пиковую концентрацию вводимого лекарства для каждого дня, учитывая дозировку для каждого дня и период полураспада препарата.

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

период полураспада (часы):

hl = 80

Количество прошедших периодов полураспада:

he(days_ago) = days_ago * 24 / 80

количество, оставшееся после истечения периода полураспада:

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

И, наконец, общая пиковая концентрация для дня n:

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

Пример, день 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

Попробуйте силу смешанных ссылок.

= ЕСЛИ (ЕЧИСЛО (С1), С1+SUM ($ B $ 1: В1), 0)

Скопируйте вниз.

Answer 2

Это пользовательская функция 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

Вероятно, это можно сделать с помощью SUMPRODUCT(), но я не могу понять, как сделать множители.

Answer 3

1. Выделите ваши ячейки, включая заголовки столбцов
2. Вставить таблицу (убедитесь, что у вас есть строка заголовка)
3. Измените имя таблицы на "MyTable" или что-то в этом роде. (Пока ваш выбор находится внутри таблицы, имя таблицы должно быть слева на ленте ленты инструментов для работы с таблицами.)
4. Для каждой ячейки в B измените формулу на =IF(ROW()-ROW(MyTable[#Headers])=1,0,OFFSET([@B],-1,0)+SUM(OFFSET(MyTable[[#Headers],[A]],1,0,ROW()-ROW(MyTable[#Headers])-1)))

Изменить: я должен объяснить эту формулу. В переводе на английский написано ...

• Если я первая строка данных в таблице, просто верните 0
• В противном случае верните ячейку выше меня плюс сумму значений в A выше меня.