Formula to return date if cumulative amount meets criteria

[jumbledore]

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

 

[Special-K99]

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

 

[Comfy]

There maybe away to do it without a helper column, but I can't

This would be one option:

Excel 2010
	D	E	F	G	H	I
3	Dates	Amount	Cumulative		Cumulative	Date
4	16/07/2014	10000	10000		>=14000	01/08/2014
5	01/08/2014	4567	14567
6	03/08/2014	3000	17567
7
Sheet1

Cell Formulas
Range	Formula
F4	=IFERROR(F3+E4,0+E4)
I4	=DMIN(D3:F6,1,H3:H4)

 

[Aladin Akyurek]

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

[TABLE="width: 246"]
<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>[TR]
[TD="class: xl65, width: 104, bgcolor: transparent"]Dates[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Amt[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 96, bgcolor: transparent, align: right"]14,000[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]16-Jul-14[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10000[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1-Aug-14[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]1-Aug-14[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]4567[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: right"]3-Aug-14[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]3000[/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

D2, control+shift+enter, not just enter:

=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))

 

[jumbledore]

[TABLE="width: 246"]
<tbody>[TR]
[TD="class: xl65, width: 104, bgcolor: transparent"]Dates
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Amt
[/TD]
[TD="class: xl66, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl67, width: 96, bgcolor: transparent, align: right"]14,000
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]16-Jul-14
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]10000
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]1-Aug-14
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, align: right"]1-Aug-14
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]4567
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent, align: right"]3-Aug-14
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]3000
[/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[TD="class: xl66, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

D2, control+shift+enter, not just enter:

=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

 

[Aladin Akyurek]

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.

 

[jumbledore]

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))

 

[Aladin Akyurek]

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...

 

[Comfy]

You should sort your data so that it is in date order.

 

[jumbledore]

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