blank cell versus zero

[royhern]

Hey team with my limited knowledge of excel i am stuck with this. This is my formula:

=IF(ISERROR((SUM(G5,L5,Q5,V5,AA5))),"-",(SUM(G5,L5,Q5,V5,AA5)))

Instead of "0" (zero) appearing when their are no numbers to add, i need the cell to be blank. What do i need to change ?

Thanks, Roy

 

[sen_edp]

On 2002-09-21 04:05, royhern wrote:
Hey team with my limited knowledge of excel i am stuck with this. This is my formula:

=IF(ISERROR((SUM(G5,L5,Q5,V5,AA5))),"-",(SUM(G5,L5,Q5,V5,AA5)))

Instead of "0" (zero) appearing when their are no numbers to add, i need the cell to be blank. What do i need to change ?

Thanks, Roy

<pre>

hello Roy

try this

=IF(ISERROR((SUM(G5,L5,Q5,V5,AA5))),"-",if(SUM(G5,L5,Q5,V5,AA5)=0,"",SUM(G5,L5,Q5,V5,AA5)))

HTH

 

[Paul B]

one way would be to use this
=IF(OR(G5,L5,Q5,V5,AA5>0),G5+L5+Q5+V5+AA5,"")

 

[Aladin Akyurek]

On 2002-09-21 04:05, royhern wrote:
Hey team with my limited knowledge of excel i am stuck with this. This is my formula:

=IF(ISERROR((SUM(G5,L5,Q5,V5,AA5))),"-",(SUM(G5,L5,Q5,V5,AA5)))

Instead of "0" (zero) appearing when their are no numbers to add, i need the cell to be blank. What do i need to change ?

Thanks, Roy

Why not just use

=SUM(G5,L5,Q5,V5,AA5)

and custom format the formula cell as

[=0]"";General

which is a lot cheaper?

 

[royhern]

Guys, thanks so much for the help but i think i just open a can of worms. This is what happened.

This was the old formula and it produced zeros no entries were made in g5, l5 etc.):

=IF(ISERROR((SUM(G5,L5,Q5,V5,AA5))),"",(SUM(G5,L5,Q5,V5,AA5)))

I changed the formula to:

=IF(ISERROR((SUM(G5,L5,Q5,V5,AA5))),"-",IF(SUM(G5,L5,Q5,V5,AA5)=0,"",SUM(G5,L5,Q5,V5,AA5))) and it produced no zeros when no entries were made.

But now, what should be the formula if entries are made in G5, L5 etc and the formula should produced zeros? in other words
entries of "0" are keyed in in G5, L5 etc.

Sorry and thanks once again for the help !!!

 

[Anthony1950]

Hi Start your workbook click on tools/options/view and uncheck zero values.
Hey presto all cells with zero values will be blank. I am using Excel 2000 and it works fine I do not know about other versions. Hope this helps.

 

[royhern]

Tried that already but base on entries of zeros i need this option to be checked and zeros reported.

Any other ideas ??

 

[Aladin Akyurek]

On 2002-09-22 05:02, royhern wrote:
Tried that already but base on entries of zeros i need this option to be checked and zeros reported.

Any other ideas ??

What is the reason for the ISERROR test in your original formula?

 

[royhern]

Aladdin, i want to be able to add entries on the specified cells but when no entries are keyed "0" shows up. Also it will be possible that in the specified cells the entries made will be zero for each one, therefore total points accumulated will be zero.

Any thoughts ?

 

[Aladin Akyurek]

On 2002-09-22 12:47, royhern wrote:
Aladdin, i want to be able to add entries on the specified cells but when no entries are keyed "0" shows up. Also it will be possible that in the specified cells the entries made will be zero for each one, therefore total points accumulated will be zero.

Any thoughts ?

=IF(COUNT(G5,L5,Q5,V5,AA5),SUM(G5,L5,Q5,V5,AA5),"")