Changing Sum Problem (Excel 2003)

Topic

New Reply

Good day to all the fine minds on this great website…

I have another problem that seems relatively easy at first glance but that I just cannot find a reasonable answer to. I have attached a file that illustrates the problem.

Basically, I have a predefined amount that will change in cell A1. In B2:B37 I have a column of dates and in C2:C37 I have a column of values. I need to construct a formula that begins adding the values in C2, C3 and so on, until the predefined amount in A1 is reached (say C9), and then I need to return the date for that last amount in the C column.

Hopefully the attached file illustrates the problem with more clarity.

Thanks in advance for any help on this…

Reply | Quote

Replies

See the attached version. I added an auxiliary column to calculate the cumulative values, then used a combination of MATCH and INDEX to find the date.

Reply | Quote

Hi Hans,

Great solution. I don’t know why the thought of creating a column of the added numbers eluded me, but it did. Thankfully, it didn’t elude you.

Thanks once again for a nice insightful solution…

Regards,

Reply | Quote

Since the OP is on Excel 2003, he can convert B1:D37 into a list by means of Data|List|Create List to obtain automatic formula copying and range adjustment in formulas that reference the list area.

BTW, although not a serious issue here, it would be better to invoke:

=INDEX($B$2:$B$37,MATCH(F1,$D$2:$D$37,1)+(LOOKUP(F1,$D$2:$D$37)F1))

where F1 houses the condition number.

Reply | Quote