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

steedev

New Member
Joined
Mar 6, 2018
Messages
5
Hi,

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

Spreadsheet.jpg


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.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Welcome to the board.

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

CDEFG

<tbody>
[TD="align: center"]4[/TD]
[TD="align: right"]1-Mar[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1-Mar[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2-Mar[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]3-Mar[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]4-Mar[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4-Mar[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]5-Mar[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]6-Mar[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]7-Mar[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]8-Mar[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]9-Mar[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]10-Mar[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]11-Mar[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]G4[/TH]
[TD="align: left"]=INDEX(D4:D14,MATCH(F4,C4:C14,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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



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.
 
Last edited:
Upvote 0
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
 
Upvote 0
OK, give this a shot:

CDEFG

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: right"]8:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]9:00[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]11:00[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]14:00[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]12:00[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]13:00[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]14:00[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]15:00[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G4[/TH]
[TD="align: left"]=INDEX(D4:D14,MATCH(F4,C4:C14,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F4[/TH]
[TD="align: left"]{=MIN(IF(C4:C14>NOW()-TODAY(),C4:C14))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G7[/TH]
[TD="align: left"]{=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)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top