# Thread: Changing Sum Problem (Excel 2003)

1. ## Changing Sum Problem (Excel 2003)

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...

2. ## Re: Changing Sum Problem (Excel 2003)

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.

3. ## Re: Changing Sum Problem (Excel 2003)

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,

4. ## Re: Changing Sum Problem (Excel 2003)

Since the OP is on Excel 2003, he can convert B137 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(F 1,\$D\$2:\$D\$37)<>F1))

where F1 houses the condition number.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•