Formula to return date if cumulative amount meets criteria

jumbledore

Active Member
Joined
Jan 17, 2014
Messages
262
I have 2 columns for dates and amounts paid
I want a formula which will determine the date when the cumulative of the amounts just exceeds or equals a given value. So suppose I have the following:

Dates Amt
16-Jul-14 10000
1-Aug-14 4567
3-Aug-14 3000
Now suppose I have 14,000 as my "cut-off" amount. I want a formula which should return the date, 1-Aug-14 as the cumulative amount until 1-Aug is 14,567 which is just above the cutoff of 14,000. The formula should not return 3-Aug-14 even though it meets the criteria.
Thanks
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I think there's a shorter way to do this but until someone comes along with a different method try this.
Use a Helper column.

in C1
=IF(SUM(B$1:B1)<14000,0,1)
and copy down the column

in D1
=INDEX(A1:A3,MATCH(1,C1:C3,0),1)

D1 returns the date where the sum exceeds 14000

I'm sure there's a quicker way that doesn't use a Helper column maybe using SUMPRODUCT
 
Upvote 0
There maybe away to do it without a helper column, but I can't :)

This would be one option:

Excel 2010
DEFGHI
3DatesAmountCumulativeCumulativeDate
416/07/20141000010000>=1400001/08/2014
501/08/2014456714567
603/08/2014300017567
7
Sheet1
Cell Formulas
RangeFormula
F4=IFERROR(F3+E4,0+E4)
I4=DMIN(D3:F6,1,H3:H4)
 
Upvote 0
I have 2 columns for dates and amounts paid
I want a formula which will determine the date when the cumulative of the amounts just exceeds or equals a given value. So suppose I have the following:

Dates Amt
16-Jul-14 10000
1-Aug-14 4567
3-Aug-14 3000
Now suppose I have 14,000 as my "cut-off" amount. I want a formula which should return the date, 1-Aug-14 as the cumulative amount until 1-Aug is 14,567 which is just above the cutoff of 14,000. The formula should not return 3-Aug-14 even though it meets the criteria.
Thanks

DatesAmt 14,000
16-Jul-1410000 1-Aug-14
1-Aug-144567
3-Aug-143000

<COLGROUP><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3697" width=104><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3413" width=96><TBODY>
</TBODY>

D2, control+shift+enter, not just enter:
Rich (BB code):
=INDEX($A$2:$A$4,MATCH(TRUE,SUBTOTAL(9,OFFSET($B$2,0,0,
  ROW($B$2:$B$4)-ROW($B$2)+1))>=D1,0))
 
Last edited:
Upvote 0
Dates
Amt
14,000
16-Jul-14
10000
1-Aug-14
1-Aug-14
4567
3-Aug-14
3000

<tbody>
</tbody>

D2, control+shift+enter, not just enter:
Rich (BB code):
=INDEX($A$2:$A$4,MATCH(TRUE,SUBTOTAL(9,OFFSET($B$2,0,0,
  ROW($B$2:$B$4)-ROW($B$2)+1))>=D1,0))

wow great code! thank you so much. Can you pls explain the logic behind it as well? Thanks
 
Upvote 0
wow great code! thank you so much.
You are welcome.

Can you pls explain the logic behind it as well? Thanks

In:

=INDEX($A$2:$A$4,MATCH(TRUE,SUBTOTAL(9,OFFSET($B$2,0,0,
ROW($B$2:$B$4)-ROW($B$2)+1))>=D1,0))

SubTotal runs SUM (that's what 9 means ) on the subreferences/subranges OFFSET creates

OFFSET(B2,0,0,1) ==> B2:B2
OFFSET(B2,0,0,2) ==> B2:B3
OFFSET(B2,0,0,3) ==> B2:B4

yielding the following subtotals (cumulative totals) - SUM of B2:B2, SUM of B2:B3, and SUM of B2:B4...

{10000;14567;17567}

Testing whether the foregoing with > D1 yields the following truth values:

{10000;14567;17567} > D1

==>

{FALSE;TRUE;TRUE}

The MATCH bit matches TRUE against the foregoing array with 2 as the position result.

Feeding the position result of 2 to INDEX looking at A2:A4, we get the date, 1-Aug-14, we are looking for.

Hope this helps.
 
Last edited:
Upvote 0
wow great code! thank you so much.
You are welcome.



In:

=INDEX($A$2:$A$4,MATCH(TRUE,SUBTOTAL(9,OFFSET($B$2,0,0,
ROW($B$2:$B$4)-ROW($B$2)+1))>=D1,0))

SubTotal runs SUM (that's what 9 means ) on the subreferences/subranges OFFSET creates

OFFSET(B2,0,0,1) ==> B2:B2
OFFSET(B2,0,0,2) ==> B2:B3
OFFSET(B2,0,0,3) ==> B2:B4

yielding the following subtotals (cumulative totals) - SUM of B2:B2, SUM of B2:B3, and SUM of B2:B4...

{10000;14567;17567}

Testing whether the foregoing with > D1 yields the following truth values:

{10000;14567;17567} > D1

==>

{FALSE;TRUE;TRUE}

The MATCH bit matches TRUE against the foregoing array with 2 as the position result.

Feeding the position result of 2 to INDEX looking at A2:A4, we get the date, 1-Aug-14, we are looking for.

Hope this helps.

nice explanation. so "match" takes "TRUE" as the lookup value and returns the index of the first condition where the sum of the sub range is above the cutoff. But looks like there is a flaw. If you add the row, with date as 30-Jul-14 and amount 4001 just at the bottom it still returns 1-Aug-14. I have modified the code as =INDEX($A$2:$A$5,MATCH(TRUE,SUBTOTAL(9,OFFSET($B$2,0,0,ROW($B$2:$B$5)-ROW($B$2)+1))>=D1,0))
 
Upvote 0
wow great code! thank you so much.

nice explanation. so "match" takes "TRUE" as the lookup value and returns the index of the first condition where the sum of the sub range is above the cutoff. But looks like there is a flaw. If you add the row, with date as 30-Jul-14 and amount 4001 just at the bottom it still returns 1-Aug-14. I have modified the code as =INDEX($A$2:$A$5,MATCH(TRUE,SUBTOTAL(9,OFFSET($B$2,0,0,ROW($B$2:$B$5)-ROW($B$2)+1))>=D1,0))

Cumulative is the operative word:

SUM(B1:B1)
SUM(B1:B2)
SUM(B1:B3)
SUM(B1:B4)

You get then the date corresponding to the first cumulative total that is > D1.

Maybe you are after something totally different...
 
Upvote 0
Cumulative is the operative word:

SUM(B1:B1)
SUM(B1:B2)
SUM(B1:B3)
SUM(B1:B4)

You get then the date corresponding to the first cumulative total that is > D1.

Maybe you are after something totally different...
I am sorry may be I shouldn't have used the word "cumulative" but is there any way to do this, though full points to your code.

You should sort your data so that it is in date order.
yes sorting should solve the problem but if there is a way to use the code without sorting it would be more than fantastic. The problem with sorting is that if I forget to do it (even once) it could create a big problem for everyone. but thanks anyway
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

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