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

S

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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
242
SBExcel123
S
N
  • Solved
Opening of excel file takes to much time
Replies
6
Views
443
Fluff
Fluff
C
  • Question Question
Formula not working to show total sales per day, #VALUE! Can't find the issue (Ignore Conditionals) I3 works fine, J3&J4 are what I have tried.
2
Replies
15
Views
599
Asbestos_Jen
Asbestos_Jen
S
  • Solved
How to Autofill columns with Sum and Indirect formula
Replies
1
Views
479
Tetra201
Tetra201
A
  • Question Question
Daily Interest Loan Scheduler | Missed Payments
Replies
0
Views
241
aurelius33
A

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

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