Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Microsoft MVP - Excel

Posting Permissions

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