counting number of cells where sum meets certain condition

K

kugmo

1 50,000.00
2 (20,000.00)
3 (10,000.00)
4 (50,000.00)
5 2,000.00
6 10,000.00
7 10,000.00
8 30,000.00
9 20,000.00


Given the above data, how do I count the number of data
until the sum starts going positive?

manually, sum starts to be positive with the result 22,000
which is data no. 8.

What formula do I use to get result 8?
 
M

Mallycat

One problem you will have is that the answer is positive for 1, 2, 3, 8
and 9. So if you want a formula that calcs when it moves from negative
to positive, you may need to do a few steps.

Assume data A1:A9
B1 enter =SUM($A$1:A1) and copy down the column - creates a running
total
C2 enter =IF(AND(B1<0,B2>0),ROW(),) checks to see when it changes
from negative to positive. Creates 0 if the situation doesn't exist,
and the row number if it does.
Sum column C to get the answer

Here is an example
ftp://members.optusnet.com.au/Files/cellchange.xls

Matt
 
B

Bernard Liengme

Here is a simple user-defined function; call it with =POS(A1:A10) for
example
Function Pos(myrange As Range)
myflag = 0
mysum = myrange(1)
mycount = myrange.Count
For j = 2 To mycount
mysum = mysum + myrange(j)
If Sgn(mysum) = -1 Then myflag = 1
If myflag = 1 And Sgn(mysum) = 1 Then
myhold = j
Exit For
End If
Next j
Pos = myhold
End Function

best wishes
 
Top