lump numeric values to totals within a range in Excel 2010

Given a column of numeric values, is it possible to sum values until the total reaches a particular threshhold, then reset the sum total and continue? for example

Val Sum
103 
52  155
47  
60  
103 210
100 
76  176
163 163
169 169
87  
103 190
84  
31  
59  174
87  
49  
67  203

This sums groups o开发者_开发知识库f numbers until the value exceeds 150, then resets the counter.

i'll post it more for the proof of concept than a real answer or for the case i could improve this (Issun may have an idea too).

Here it is: Let assume your Val is in column A and Sum in column B and Titles are in row 1.
In column C, we will set a formula that will tell which is the "last" cell where you sumed it up > in cell C3, put:

{=MAX(IF($B$2:B2=0;0;ROW($B$2:B2)))}

This is an array formula, you will have to validate with CtrlShiftEnter.

In cell B3, you will have to put the first value (155 in your example), sorry, i didn't find a proper workaround.
In cell B4, put:

=IF(SUM($A$1:A4)-SUM(INDIRECT("$B$1:"&ADDRESS(C4;2)))>150;SUM($A$1:A4)-SUM(INDIRECT("$B$1:"&ADDRESS(C4;2)));0)

Then drag & drop the formulas till the end of your values.

[EDIT] Actually, you could put it all in a single formula in cell B2:

{=IF(SUM($A$1:A2)-SUM(INDIRECT("$B$1:"&ADDRESS(MAX(IF($B$1:B1=0;0;ROW($B$1:B1)));2)))>150;SUM($A$1:A2)-SUM(INDIRECT("$B$1:"&ADDRESS(MAX(IF($B$1:B1=0;0;ROW($B$1:B1)));2)));0)}

and drag and drop it till the end of your values. Seems to work too.

Simplest way to do it (no need for array formulas let alone VBA):

In B2, type =IF(B1<150,B1+A2,A2) and drag down. This will sum until it reaches 150 or more, and then restart at zero. Of course, the intermediate sums (i.e. on the way up to 150) will show as well. If this bothers you, a couple of options spring to mind:

• Type =IF(B2>=150,B2,"") in C2 and drag down, and hide column B.
• Apply conditional formatting to column B such that numbers below 150 get shown in white font.

Here's a solution in VBA (sorry I know you wanted a forumula). You can obviously tweat the range to be whatver you need it to be. Please note I used LONG as data type (and I am casting to long to avoid some errors), but you need to use DOUBLE if you have any floating point numbers.

Sub SumIt()

Dim cell As range
Dim currentCount As Long

For Each cell In range("A2:A100")
    currentCount = currentCount + CLng(cell.Value)
    If currentCount > 150 Then
        cell.Offset(0, 1).Value = currentCount
        currentCount = 0
    End If
Next

End Sub

How it works: I loop through each cell in the range and add the value to a variable called currentCount. I then check if it's over 150, if it is I paste the currentCount to the B column (same row) and reset the count and move to the next cell. Simple!