• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula Challenge 002 - Calculate Conditional Cumulative Sum

Sajan

Excel Ninja
Challenge Name

-- Calculate the cumulative sum for a range of data, while meeting some conditions


Challenge Description

-- This is a formula challenge. As such, only formula submissions are accepted.

-- If the cumulative sum equals or exceeds the threshold, then the output = sum of all values since the summation started

-- If the cumulative sum exceeds the threshold, the summation starts over


Assumptions you may make

-- You may assume a different location for the data range than cell A1

-- The number of columns in the data are not fixed

-- The threshold value need to be made part of the formula, or referenced from another cell

-- You may not use any helper cells


Sample data and output (space delimited), with Threshold set to 7


[pre]
Code:
# A	  B	C	D	E	F	G	H	I	J	K
1 Header  H1	H2	H3	H4	H5	H6	H7	H8	H9	H10
2 Data	  5	7	4	2	3	1	11	10	7	2
3 Output  0	12	0	0	9	0	12	10	7	0
[/pre]
Some example calculations


Since B2 < Threshold (which is 7), B3=0

B2+C2 >= Threshold. Therefore C3=B2+C2=5+7=12

Since B2+C2 met or exceeded the threshold, the summation starts over

Since D2 is not >= Threshold, D3=0

D2+E2 is also not >= Threshold. As such, E3=0

However, since D2+E2+F2 >= Threshold, F3=D2+E2+F2=4+2+3=9

Since D2+E2+F2 >= Threshold, the summation starts over

Since G2 is not >= Threshold, G3=0

Since G2+H2 >= Threshold, H2=G2+H2=1+11=12


Please verify your formulas against the sample output provided (for Threshold=7)


Enjoy!

-Sajan.
 
Hi Sajan ,


I am sure you expect a better formula than this , but just to get the ball rolling :


=IF(SUM($B$2:B2)-SUM($A$3:A3)>=Threshold,SUM($B$2:B2)-SUM($A$3:A3),0)


Narayan
 
Hi Narayan,

This was not going to be a challenge for you!! I will think of a challenge that you can dig into, but I am sure I will have to think hard for that!


In the meantime, let us see if folks have other ways to accomplish the summation above... I always learn something from these sort of things.


Regards,

Sajan.
 
I actually did come up with the same formula as Narayan independently (great minds thinking alike? <grin>), but you could shorten the length (63 characters vs. 69) and calculation time by writing it as:

=(SUM($B2:B2)-SUM($A3:A3)>=Threshold)*(SUM($B2:B2)-SUM($A3:A3))


Working on seeing if a more elegant solution can be created...
 
Here's mine with no peaking.

=(SUM($B2:B2,-$A8:A8)>=$B5)*SUM($B2:B2,-$A8:A8)


...where the threshold is in $B53


Whoops, made an error. Please hold, caller...
 
Okay, this:

=(SUM($B2:B2)-SUM($A3:A3)>=$B5)*(SUM($B2:B2)-SUM($A3:A3))


Same as Lukes, I see.


Very good challenge. Very challenging, in fact.
 
Also note that the first formula I posted above works if you delete the word 'Output' from column A3. i.e. this:

=(SUM($B2:B2,-$A3:A3)>=$B5)*SUM($B2:B2,-$A3:A3)
 
All,

Great techniques!


Interesting approach of negating a range inside the SUM()!


-Sajan.
 
Hi Jeff,

My first attempt produced a long formula (reflective of the time of night I wrote it!!):


=IF(SUM(OFFSET($B$3,0,INDEX(MAX(MAX(IF($B5:B5>0, COLUMN($B5:B5))),COLUMN($B$3))-COLUMN($B$3)+1, 1),1,INDEX(COLUMN()-MAX(MAX(IF($B5:B5>0, COLUMN($B5:B5))),COLUMN($B$3)), 1)))>=Threshold, SUM(OFFSET($B$3,0,INDEX(MAX(MAX(IF($B5:B5>0, COLUMN($B5:B5))),COLUMN($B$3))-COLUMN($B$3)+1, 1),1,INDEX(COLUMN()-MAX(MAX(IF($B5:B5>0, COLUMN($B5:B5))),COLUMN($B$3)), 1))), 0)

enter with Ctrl + Shift + Enter


My second attempt was similar to that of Narayan and Luke.


Moral of the story... don't attempt to write formulas when you should be sleeping! :)


-Sajan.
 
Back
Top