como somar valores no excel até que eles atinjam um total especificado, em seguida, adicione a célula próxima na coluna

Question

Eu quero ter um valor limite em uma célula ( A1 ) e tomá-lo como referência para adicionar células.

Suponha que eu tenha

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

Eu quero adicionar células com base em A1 (limite).

Se A1 for 10, adicionará A2:A5 , sum = 13
Se A1 for 9, adicionará A2:A4 , sum = 9

A fórmula SUM é

=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))

Eu tenho uma fórmula para essa parte da primeira parte ...

Depois, quero que a fórmula adicione uma porcentagem do valor na próxima célula depois que o limite for atingido:

Se A1 for 10, então células de A2:A5 devem ser adicionadas, mais 50% de A6 ; soma = 16

    

Answer 1

A maneira mais simples de fazer isso seria usar uma indireta na célula da maneira que você já encontrou -

=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))

Eu acho que a segunda parte poderia ser melhor ou sair da primeira parte, mas ainda não descobri.