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





--
bill gras

Try something like this:

With
Col_G containing numbers (pos or neg) or blanks, beginning in G3

Try this:
H3: =IF(G3=0,0,IF(G2="",H1,N(H2))-G3)

Copy that formula down as far as needed

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"bill gras" wrote:

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





--
bill gras

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)


"bill gras" wrote in message
...
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





--
bill gras

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

copy from H2 down as far as needed


"bill gras" wrote:

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





--
bill gras

Thanks Ron for your input
There's one thing with your formula , it needs to accumulate
all the entries in column "H" from zero to zero .
G H
-5.0 5.0
-5.0 10.0
-5.0 15.0

22.5 0.0 (because of positive figure in column (G)
-7.0 7.0
-7.0 14.0

-5.0 19.0
-5.0 24.0
-5.0 29.0

-7.0 36.0 ....(sum because of negative figures in
12.0 0.0 column (G) from zerro to zerro)
-7.0 7.0
I apologise for not making my last post very clear

thank you
regards bill
--
bill gras


"Ron Coderre" wrote:

Try something like this:

With
Col_G containing numbers (pos or neg) or blanks, beginning in G3

Try this:
H3: =IF(G3=0,0,IF(G2="",H1,N(H2))-G3)

Copy that formula down as far as needed

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"bill gras" wrote:

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





--
bill gras

Thanks Teethless mama for your input
There's one thing with your formula , it needs to accumulate
all the entries in column "H" from zero to zero .
G H
-5.0 5.0
-5.0 10.0
-5.0 15.0

22.5 0.0 (because of positive figure in column (G)
-7.0 7.0
-7.0 14.0

-5.0 19.0
-5.0 24.0
-5.0 29.0

-7.0 36.0 ....(sum because of negative figures in
12.0 0.0 column (G) from zerro to zerro)
-7.0 7.0
I apologise for not making my last post very clear

thank you
regards bill
--
bill gras

--
bill gras


"Teethless mama" wrote:

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

copy from H2 down as far as needed


"bill gras" wrote:

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





--
bill gras

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(G20,0,IF(G1="",H1,N(H1))-G2)
and copied down

--
Regards

Roger Govier


"bill gras" wrote in message
...
Thanks Ron for your input
There's one thing with your formula , it needs to accumulate
all the entries in column "H" from zero to zero .
G H
-5.0 5.0
-5.0 10.0
-5.0 15.0

22.5 0.0 (because of positive figure in column (G)
-7.0 7.0
-7.0 14.0

-5.0 19.0
-5.0 24.0
-5.0 29.0

-7.0 36.0 ....(sum because of negative figures in
12.0 0.0 column (G) from zerro to zerro)
-7.0 7.0
I apologise for not making my last post very clear

thank you
regards bill
--
bill gras


"Ron Coderre" wrote:

Try something like this:

With
Col_G containing numbers (pos or neg) or blanks, beginning in G3

Try this:
H3: =IF(G3=0,0,IF(G2="",H1,N(H2))-G3)

Copy that formula down as far as needed

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"bill gras" wrote:

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





--
bill gras

I think you'll need to clarify what you need. I think I'm missing
something....

These are the Col_G values (G3:G17) and the Col_H results from my formula:

Col_G Col_H
-5 5
-5 10
-5 15
0
22.5 0
-7 7
-7 14
0
-5 19
-5 24
-5 29
0
-7 36
12 0
-7 7

The calculated values match what you posted.....hence my puzzlement.

***********
Regards,
Ron

XL2002, WinXP


"bill gras" wrote:

Thanks Ron for your input
There's one thing with your formula , it needs to accumulate
all the entries in column "H" from zero to zero .
G H
-5.0 5.0
-5.0 10.0
-5.0 15.0

22.5 0.0 (because of positive figure in column (G)
-7.0 7.0
-7.0 14.0

-5.0 19.0
-5.0 24.0
-5.0 29.0

-7.0 36.0 ....(sum because of negative figures in
12.0 0.0 column (G) from zerro to zerro)
-7.0 7.0
I apologise for not making my last post very clear

thank you
regards bill
--
bill gras


"Ron Coderre" wrote:

Try something like this:

With
Col_G containing numbers (pos or neg) or blanks, beginning in G3

Try this:
H3: =IF(G3=0,0,IF(G2="",H1,N(H2))-G3)

Copy that formula down as far as needed

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"bill gras" wrote:

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





--
bill gras

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(G20,0,IF(G1="",H1,N(H1))-G2))

--
Regards

Roger Govier


"Ron Coderre" wrote in message
...
I think you'll need to clarify what you need. I think I'm missing
something....

These are the Col_G values (G3:G17) and the Col_H results from my
formula:

Col_G Col_H
-5 5
-5 10
-5 15
0
22.5 0
-7 7
-7 14
0
-5 19
-5 24
-5 29
0
-7 36
12 0
-7 7

The calculated values match what you posted.....hence my puzzlement.

***********
Regards,
Ron

XL2002, WinXP


"bill gras" wrote:

Thanks Ron for your input
There's one thing with your formula , it needs to accumulate
all the entries in column "H" from zero to zero .
G H
-5.0 5.0
-5.0 10.0
-5.0 15.0

22.5 0.0 (because of positive figure in column (G)
-7.0 7.0
-7.0 14.0

-5.0 19.0
-5.0 24.0
-5.0 29.0

-7.0 36.0 ....(sum because of negative figures in
12.0 0.0 column (G) from zerro to zerro)
-7.0 7.0
I apologise for not making my last post very clear

thank you
regards bill
--
bill gras


"Ron Coderre" wrote:

Try something like this:

With
Col_G containing numbers (pos or neg) or blanks, beginning in G3

Try this:
H3: =IF(G3=0,0,IF(G2="",H1,N(H2))-G3)

Copy that formula down as far as needed

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"bill gras" wrote:

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





--
bill gras

I think you might be right....we'll see.


***********
Regards,
Ron

XL2002, WinXP


"Roger Govier" wrote:

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(G20,0,IF(G1="",H1,N(H1))-G2))

--
Regards

Roger Govier


"Ron Coderre" wrote in message
...
I think you'll need to clarify what you need. I think I'm missing
something....

These are the Col_G values (G3:G17) and the Col_H results from my
formula:

Col_G Col_H
-5 5
-5 10
-5 15
0
22.5 0
-7 7
-7 14
0
-5 19
-5 24
-5 29
0
-7 36
12 0
-7 7

The calculated values match what you posted.....hence my puzzlement.

***********
Regards,
Ron

XL2002, WinXP


"bill gras" wrote:

Thanks Ron for your input
There's one thing with your formula , it needs to accumulate
all the entries in column "H" from zero to zero .
G H
-5.0 5.0
-5.0 10.0
-5.0 15.0

22.5 0.0 (because of positive figure in column (G)
-7.0 7.0
-7.0 14.0

-5.0 19.0
-5.0 24.0
-5.0 29.0

-7.0 36.0 ....(sum because of negative figures in
12.0 0.0 column (G) from zerro to zerro)
-7.0 7.0
I apologise for not making my last post very clear

thank you
regards bill
--
bill gras


"Ron Coderre" wrote:

Try something like this:

With
Col_G containing numbers (pos or neg) or blanks, beginning in G3

Try this:
H3: =IF(G3=0,0,IF(G2="",H1,N(H2))-G3)

Copy that formula down as far as needed

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"bill gras" wrote:

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





--
bill gras

Hi Ron
The little adjustment that Roger made to your formula done the trick .
Thank you for doing the ground work.
What would I do with out people like your self and roger?
Once again thanks
regards bill
--
bill gras


"Ron Coderre" wrote:

I think you'll need to clarify what you need. I think I'm missing
something....

These are the Col_G values (G3:G17) and the Col_H results from my formula:

Col_G Col_H
-5 5
-5 10
-5 15
0
22.5 0
-7 7
-7 14
0
-5 19
-5 24
-5 29
0
-7 36
12 0
-7 7

The calculated values match what you posted.....hence my puzzlement.

***********
Regards,
Ron

XL2002, WinXP


"bill gras" wrote:

Thanks Ron for your input
There's one thing with your formula , it needs to accumulate
all the entries in column "H" from zero to zero .
G H
-5.0 5.0
-5.0 10.0
-5.0 15.0

22.5 0.0 (because of positive figure in column (G)
-7.0 7.0
-7.0 14.0

-5.0 19.0
-5.0 24.0
-5.0 29.0

-7.0 36.0 ....(sum because of negative figures in
12.0 0.0 column (G) from zerro to zerro)
-7.0 7.0
I apologise for not making my last post very clear

thank you
regards bill
--
bill gras


"Ron Coderre" wrote:

Try something like this:

With
Col_G containing numbers (pos or neg) or blanks, beginning in G3

Try this:
H3: =IF(G3=0,0,IF(G2="",H1,N(H2))-G3)

Copy that formula down as far as needed

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"bill gras" wrote:

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





--
bill gras

Hi Roger
Your amendment to Ron's formula works perfect .
I want to thank you both for the hard work you done
for me !
p.s.
The " maybe what he really wants is " that formula
is even better!!
Once again thanks
regards bill
--
bill gras


"Roger Govier" wrote:

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(G20,0,IF(G1="",H1,N(H1))-G2)
and copied down

--
Regards

Roger Govier


"bill gras" wrote in message
...
Thanks Ron for your input
There's one thing with your formula , it needs to accumulate
all the entries in column "H" from zero to zero .
G H
-5.0 5.0
-5.0 10.0
-5.0 15.0

22.5 0.0 (because of positive figure in column (G)
-7.0 7.0
-7.0 14.0

-5.0 19.0
-5.0 24.0
-5.0 29.0

-7.0 36.0 ....(sum because of negative figures in
12.0 0.0 column (G) from zerro to zerro)
-7.0 7.0
I apologise for not making my last post very clear

thank you
regards bill
--
bill gras


"Ron Coderre" wrote:

Try something like this:

With
Col_G containing numbers (pos or neg) or blanks, beginning in G3

Try this:
H3: =IF(G3=0,0,IF(G2="",H1,N(H2))-G3)

Copy that formula down as far as needed

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"bill gras" wrote:

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





--
bill gras