How do I make this display a "0" value
 

In Cell G18 I have the formula =SUM(G9:G17,-36) but the cells G9-G17 are
blank yet G18 displays a -36 value. I know this must be simple but is there a
way to get the G18 cell to display a "0" value if the cells G9-G17 are blank?
--
Geo

One way to do it is to use an IF() function:
=IF(SUM(G9:G17)=0,0,SUM(G9:G17,-36))

This instructs Excel to return a zero value if the sum of cells g9 thru
g17 is zero, and performs your original SUM(G9:G17,-36) operation if
the sum is not zero.

Hi
=IF(COUNT(G9:G17),SUM(G9:G17,-36),0)

or maybe
=MAX(0,SUM(G9:G17,-36))

--
Regards
Frank Kabel
Frankfurt, Germany
"Geo" schrieb im Newsbeitrag
...
In Cell G18 I have the formula =SUM(G9:G17,-36) but the cells G9-G17 are
blank yet G18 displays a -36 value. I know this must be simple but is
there a
way to get the G18 cell to display a "0" value if the cells G9-G17 are
blank?
--
Geo

This solution worked great but I still have a problem in another area. This
formula in G35 generates a number when the cell G32 is
"0"=SUM(G32,-69.4*113/119) which is probably what I'm telling it to do.
(Doing the formula on my calculator yields -65.9008 which is what excell G35
does.) I have tried substituting my other fix(Below) but am having trouble
making it also display "0".

"Dave O" wrote:

One way to do it is to use an IF() function:
=IF(SUM(G9:G17)=0,0,SUM(G9:G17,-36))

This instructs Excel to return a zero value if the sum of cells g9 thru
g17 is zero, and performs your original SUM(G9:G17,-36) operation if
the sum is not zero.

Not sure I follow you: you want g35 to return 0 when g32 = 0? If
that's the case another IF() function can do it for you:

=IF(g32=0,0,SUM(G32,-69.4*113/119))

Is that what you need?


Geo wrote:
This solution worked great but I still have a problem in another
area. This
formula in G35 generates a number when the cell G32 is
"0"=SUM(G32,-69.4*113/119) which is probably what I'm telling it to
do.
(Doing the formula on my calculator yields -65.9008 which is what
excell G35
does.) I have tried substituting my other fix(Below) but am having
trouble
making it also display "0".

"Dave O" wrote:

One way to do it is to use an IF() function:
=IF(SUM(G9:G17)=0,0,SUM(G9:G17,-36))

This instructs Excel to return a zero value if the sum of cells g9
thru
g17 is zero, and performs your original SUM(G9:G17,-36) operation
if
the sum is not zero.