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]
[/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.
-- 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
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.