Formula to return corresponging date value in row 1 once row 2 payment sum reaches a value

[wannabeexcelguy]

Hey gurus,

I need a formula that will look look at payments in row 2 and return the date in row 1 at the column where the sum of payments is greater than a cell value.

E.G. In the example attached, the amount owed to me is in DU2 - received payments are in DV2:AIX2. The last payment which brings the sum of payments to the amount in DU2 is in cell GO2.

I want the formula to return the date in GO1 because the payment in GO2 brought the total of payments to equal to or more than the value in DU2.

This way I will know that any payments received after the date in GO1 need to be refunded.

Please help, I can't figure it out! Thanks very much to anyone who takes the time to look at this.

Yours truly,
wannabeexcelguy

[Izadi]

Hi,please check this file....

[FDibbins]

Izandi Sorry for off-topic interjection:

Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[Izadi]

Hi,
At first you type this formula in cell DV3 and then autofill to end of row to calculate comulative sum.
=SUM($DV$2:DV2)
Finally type this formula to DU3 cell and press Ctrl+Shift+Enter
=INDEX(DV1:AIX1,1,MATCH(TRUE,(DV3:AIX3)>=DU2,0))

[wannabeexcelguy]

Thank you Izadi! Is there any way to combine the two formulas in to one?

[Izadi]

Now,I have no idea but if I find it I will send it to you.

[wannabeexcelguy]

I figured out how to do it. Thank you Izadi, your formula helped me get there. Obviously the below formula is for dates down column A and payments down column B (rather than across rows like above).

First, the below returns the amount of payment days
=MATCH(TRUE,INDEX(SUMIF(OFFSET(B$127,1,,ROW(B$127:B917)-ROW(B$127),1),"<>0")>=B$126,0),0)

Secondly, the below adds those payments days to the start date
=WORKDAY($A918,B918)