+ Reply to Thread
Results 1 to 4 of 4

Adding up a row of numbers until they are > than a cell and then return adjacent date

  1. #1
    Registered User
    Join Date
    08-24-2012
    Location
    Indiana
    MS-Off Ver
    Excel 2010
    Posts
    2

    Adding up a row of numbers until they are > than a cell and then return adjacent date

    I think I need match index offset sum, but I have no idea in what sequence.

    I have dates in cell F2:S2, Numbers in cells F3:S3, Reference cell is c3.

    In cell B3 I need a formula that will add up F3 to U3 until they are greater than cell C3, and then return the corresponding date in cell F2:U2 which ever date is adjacent to the number at the time its total is greater than C3.

    I hope this is clear.Book3.xlsx

  2. #2
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Adding up a row of numbers until they are > than a cell and then return adjacent date

    I would recommend a helper row to keep a cumulative sum of the numbers. So, in F4 of your example, put in this formula:
    =F3+E4
    Then drag that all the way to S4.

    Then in C3, use this formula:
    =INDEX(F2:S2,MATCH(MAX(C3,F4),F4:S4,1))
    Pauley
    --------
    If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).

  3. #3
    Registered User
    Join Date
    08-24-2012
    Location
    Indiana
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Adding up a row of numbers until they are > than a cell and then return adjacent date

    Thanks PauleyB, I did add the Helper Row below the date, but I ended up using this

    =+IF(ISERROR(INDEX(F2:S2,MATCH(TRUE,F4:AL4>C3,0))),"",INDEX(F2:S2,MATCH(TRUE,F4:AL4>C3,0)))

    entered as an array, to make matters worse, I needed to put it into Visual basic and it ended up looking like this

    Selection.FormulaArray = _
    "=+IF(ISERROR(INDEX(R[-1]C[4]:R[-1]C[17],MATCH(TRUE,R[1]C[4]:R[1]C[36]>RC[1],0))),"""",INDEX(R[-1]C[4]:R[-1]C[17],MATCH(TRUE,R[1]C[4]:R[1]C[36]>RC[1],0)))"

    Your suggestion of the helper row was great, I just wished I could do a formula getting around that, but all is good.

    thanks again.

  4. #4
    Forum Expert
    Join Date
    06-26-2010
    Location
    Austin, TX
    MS-Off Ver
    Excel 2010
    Posts
    1,673

    Re: Adding up a row of numbers until they are > than a cell and then return adjacent date

    Yeah, I tried to come up with something elegant without the helper row and it just ended up being odd. Not sure why my original formula didn't work as needed, but I assume there was something in your data which required the change. By the way, since you have Excel 2010, you can use the =IFERROR() function to simplify your equation a bit.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1