add up negative numbers

[Guest]

Hi
I have a column "G" with negative numbers, positive numbers and zero's.
Column "H" is the column that adds up all the negative numbers from
column "G" . When there is a positive number greater than zero i need
column "H" to show a zero from the corresponding cell in column "G"
e.g
G H
-5.0 5.0
-5.0 10.0
-5.0 15.0
22.5 0.0

0.0 0.0
0.0 0.0
-7.0 7.0

42.5 0.0
-7.0 7.0
-8.0 15.0

-6.0 21.0
0.0 0.0
5.5 0.0

-12.5 12.5
-20.0 32.5
-8.0 40.5
There is a empty row between each set of three entrie's
Could any one help me with this please

regards bill

 

[Bob Phillips]

h1: =MIN(G1,0)
H2: =IF(G2="","",IF(G2<0,G2+H1,0))

copy H down

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Guest]

H1 =ABS(MIN(0,G1))
H2 =IF(G2="","",IF(G2<0,SUM(ABS(G2),H1),0))

copy from H2 down as far as needed

 

[Roger Govier]

Hi Bill

Just amend Ron's formula to >0 rather than >=0 to account for the blank
rows that you say you have after every set of 3 numbers. Adjusting to
start in H2 the formula would be
=IF(G2>0,0,IF(G1="",H1,N(H1))-G2)
and copied down

 

[Roger Govier]

Hi Ron

I think it is possibly the additional 0's that get created with your
formula in column H that is misleading the OP.
My slight amendment repeated the previous value when there is a blank or
0 in column G.
Maybe what he really wants is
=IF(G2=0,"",IF(G2>0,0,IF(G1="",H1,N(H1))-G2))