Please help - need to return number of months for sum of cell that exceeds a given number

[olga123]

Hi there,

First - a huge fan @Chandoo, watching your youtube videos all the time, very informative, TY!!

I need some help, pretty please looked at some formulas on here but still can't figure it out unfortunately

The task:
Given - two rows of data, dynamic array (a new column is added to the end every month)

based on the value in a certain cell in the second row, i need to add values in the first row (starting with next month) till sum exceeds the corresponding value from the second row and return number of months it would take me to recover that value


Huge huge thank you for any help/suggestions!!

Olga

 

[bosco_yip]

Try,

In C8, non-array formula copied across right:

=IF(D$4="","",IFERROR(MIN(MATCH(C$5,INDEX(PROB(COLUMN(D$4:$R$4)-COLUMN(C$4),D$4:$R$4/SUM(D$4:$R$4),,COLUMN(D$4:$R$4)-COLUMN(C$4))*SUM(D$4:$R$4),)),COUNT(D$4:$R$4)-1),0)+1)

 

[olga123]

Try,

In C8, non-array formula copied across right:

=IF(D$4="","",IFERROR(MIN(MATCH(C$5,INDEX(PROB(COLUMN(D$4:$R$4)-COLUMN(C$4),D$4:$R$4/SUM(D$4:$R$4),,COLUMN(D$4:$R$4)-COLUMN(C$4))*SUM(D$4:$R$4),)),COUNT(D$4:$R$4)-1),0)+1)

View attachment 82597

Amazing !!!!!
Thank you sooooo much!!!!!!!!!!! works perfectly well for my purpose!!!
Huge thank you, really appreciate it!
Olga