# blank cell versus zero

#### royhern

##### Board Regular
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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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

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

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?

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 !!!

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.

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

Any other ideas ??

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?

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 ?

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),"")

Replies
7
Views
274
Replies
7
Views
816
Replies
4
Views
228
Replies
5
Views
210
Replies
9
Views
714

1,202,984
Messages
6,052,916
Members
444,613
Latest member
questionexcelz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back