Excel Formula Question - SUM future payments until specified value is reached

[steedev]

Hi,

This is my first time posting, hopefully someone can help.

I have a list of times and a matching list of payments - Times in cells C4:C14 and payments in cells D4:D14.
I have formulas to show the time of the next payment in cell F4 {=MIN(IF(C4:C14>(NOW()-TODAY()),C4:C14))}
and one to show the next payment amount in cell G4 =INDEX(D4:D14,MATCH(F4,C4:C14,0))

The bit I am struggling with is a formula to find the time when my balance (in cell F7) plus the sum of future payments reaches 10.
At the moment I have the following formula which involves many nested IF statements.

=IF(F7+INDIRECT("D"&H7)<10,
IF(F7+INDIRECT("D"&H7+1)<10,
IF(F7+INDIRECT("D"&H7+2)<10,
IF(F7+INDIRECT("D"&H7+3)<10,
IF(F7+INDIRECT("D"&H7+4)<10,
IF(F7+INDIRECT("D"&H7+5)<10,
IF(F7+INDIRECT("D"&H7+6)<10,
IF(F7+INDIRECT("D"&H7+7)<10,
IF(F7+INDIRECT("D"&H7+8)<10,
IF(F7+INDIRECT("D"&H7+9)<10,
IF(F7+INDIRECT("D"&H7+10)<10,
"OVER 10",
INDIRECT("C"&H7+10)),
INDIRECT("C"&H7+9)),
INDIRECT("C"&H7+8)),
INDIRECT("C"&H7+7)),
INDIRECT("C"&H7+6)),
INDIRECT("C"&H7+5)),
INDIRECT("C"&H7+4)),
INDIRECT("C"&H7+3)),
INDIRECT("C"&H7+2)),
INDIRECT("C"&H7+1)),
INDIRECT("C"&H7))

In this formula the cell H7 is used as the row counter for the next payment, derived from cell F4.
It is functional in this example data but not practical in my real data as there are many more than 64 entries which I believe is the maximum number of nested IF statements.

Is there an easier and nicer way to find the next payment and then add the SUM of future payments to my balance until a value of 10 is reached? At this point I would like to display the time at which a balance of 10 is reached.

I hope this is clear and thanks in advance for any help.

 

[steedev]

Apologies, dropbox was being weird with the link. Here is the screenshot of my data

 

[Eric W]

Welcome to the board.

I couldn't see your screenshot, and your description was a little unclear to me, but maybe something like this:

	C	D	E	F	G
4	1-Mar	1		1-Mar	1
5	2-Mar	2
6	3-Mar	3
7	4-Mar	4		3	4-Mar
8	5-Mar	5
9	6-Mar	6
10	7-Mar	7
11	8-Mar	8
12	9-Mar	9
13	10-Mar	10
14	11-Mar	11

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula G4 =INDEX(D4:D14,MATCH(F4,C4:C14,0)) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula F4 {=MIN(IF(C4:C14>NOW()-TODAY(),C4:C14))} G7 {=INDEX(C4:C14,MATCH(10,F7+SUBTOTAL(9,OFFSET(D4,0,0,ROW(D4:D14)-ROW(D4)+1)))+1)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The formula shows the date where the sum of F7 and D:D first exceeds 10. If the sum is exactly 10, it shows the next row. It's probably not exactly what you need, since I couldn't be sure of your requirements, but you may be able to adapt it.

 

[steedev]

Thanks Eric

I'll give this a try

 

[steedev]

Hi Eric,
This formula appears to work well, thank you.
The only adaptation I would like to make is so that it only counts values that are in the future based on the current time of day.

Thanks for your help

 

[Eric W]

OK, give this a shot:

	C	D	E	F	G
4	8:00	1		12:00	5
5	9:00	2
6	10:00	3
7	11:00	4		3	14:00
8	12:00	5
9	13:00	1
10	14:00	7
11	15:00	8
12	16:00	9
13	17:00	10
14	18:00	11

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula G4 =INDEX(D4:D14,MATCH(F4,C4:C14,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula F4 {=MIN(IF(C4:C14>NOW()-TODAY(),C4:C14))} G7 {=INDEX(C4:C14,MATCH(10,F7+SUBTOTAL(9,OFFSET(INDEX(D4:D14,MATCH(NOW()-TODAY(),C4:C14)+1),0,0,ROW(D4:D14)-ROW(D4)+1)))+MATCH(NOW()-TODAY(),C4:C14)+1)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[steedev]

Thanks again, I’ll have a play with this