excel formula 1

[cmills5767]

I need to make a excel formula witch will total a column and when the total reached a set number then the next field in the same column will return to zero and start over again. Can anyone help???

Thanks
Chris

 

[ETID]

Paste this function into a module,
-----------------------------
-------------------------
Function Count_to_x(InRange, criteria)
Dim total As Long

Set SubSetRange1 = _
Intersect(InRange.Parent.UsedRange, InRange)

For Each cell In SubSetRange1
total = total + cell.Value
If total >= criteria Then Count_to_x = total: total = 0 Else Count_to_x = total
Next cell

End Function
------------------------



then in the spreadsheet....if data is in col A

paste this formula in b1...

=count_to_x($A$1:A1,$F$1)

note: that the criteria or max to count is in cell f1 and the first cell in the range ($a$1) and the criteria ($f$1) are ranged absolute.

fill down

 

[Zathras]

....excel formula witch (sic) will total a column and when the total reached a set number then the next field in the same column will return to zero and start over again....

A formula can only set the value of the cell in which the formula is entered. The "next field" would need a separate formula. Perhaps you can use the MOD function in the "next field" to accomplish what you describe.

For example if your "set number" is 52 and the SUM function is in cell B30, then you could put this formula in cell B31:

   =MOD(B30,52)

If this misses the point, perhaps you could post a specific example of what you are trying to do.

 

[RobBroekhuis]

Perhaps this is all you're looking for (assuming your column to be totaled is A):

in cell B2: =if(B1>100,A2,B1+A2)

in other words, start a new total once the previous total exceeds 100.
Rob

 

[ETID]

Nice one Rob...

I was re-inventing the wheel,...Doh!