как суммировать значения в Excel, пока они не достигнут указанной суммы, а затем добавить следующую ячейку в столбце

Question

Я хочу иметь пороговое значение в одной ячейке (A1) и использовать его как ссылку для добавления ячеек.

Предположим, у меня есть

A1 - 10
A2 - 4
A3 - 2
A4 - 3
A5 - 4
A6 - 6

Я хочу добавить ячейки на основе A1(порог).

Если A1 равен 10, это добавит A2:A5 , сумма = 13
Если A1 равен 9, это добавило бы A2:A4 , sum = 9

СУММА формула

=SUM(OFFSET($A$2,0,0,MAX(INDEX((SUBTOTAL(9,OFFSET($A$2, 0,0,ROW(1:99),1))<$A$1)*ROW(1:99),,))+1,1))

У меня есть формула для этой первой части ...

Затем я хочу, чтобы формула добавляла процент от значения в следующую ячейку после достижения порога:

Если A1 равен 10, то следует добавить ячейки из A2:A5 плюс 50% от A6 ; сумма = 16

Answer 1

Самый простой способ сделать это - использовать косвенный элемент в той ячейке, который вы уже нашли.

=SUM(OFFSET($A$2,0,0,MAX(INDEX((SUBTOTAL(9,OFFSET($A$2, 0,0,ROW(1:99),1))
<$A$1)*ROW(1:99),,))+1,1),
(0.5)*INDIRECT("A"&MAX(INDEX((SUBTOTAL(9,OFFSET($A$2,0,0,ROW(1:99),1))  
<$A$1)*ROW(1:99),,))+3))

Я думаю, что вторая часть могла бы быть лучше или лучше, чем первая, но я еще не понял этого.