How to hide a '0' showing in a cell if no data entered

**divingscubaboy** · 10-06-2010, 04:13 AM

Hi All

I'm still a noob in Excel although I have floundered around relatively succesfully for a while. I have a formula "=SUM(E28+F28+G28+H28)/(4)". If nothing is entered into cells E-H then a zero is displayed in the sum cell. I dont want to hide the cell or column, how can I get a blank cell / hide the '0' if no data is entered? Thanks

**DonkeyOte** · 10-06-2010, 04:30 AM

There are numerous options here

=SUM(E28:H28)/4
and use a Custom Number Format to mask 0

Or double evaluate the SUM and return a Formula Null (text string)

=IF(SUM(E28:H28)=0,"",SUM(E28:H28)/4)

which would also be written as

=IF(SUM(E28:H28),SUM(E28:H28)/4,"")

Note: in using SUM there is no need for the + operator (this is one of the reasons it is used - ie to avoid explicit coercion).
Where the ranges are contiguous use a contiguous reference else delimit ranges with comma

**royUK** · 10-06-2010, 07:56 AM

You can also choose not to display zeros in Excel's Options

**Cutter** · 10-06-2010, 08:42 AM

Another option:

Use Conditional Formatting to change font colour to background colour if cell value = 0

**froment** · 10-06-2010, 10:23 AM

Several solutions for this simple questions; here is my penny; I personally always use DonkeyOte's method, with two variations: sometimes I want destination cell to show value as soon as one of the cells is filled in; but sometimes I want all cells to be filled in before value in destination cell is shown; so I use these formulas:

=IF(OR(E28="",F28="",G28="",H28=""),"",SUM(E28:H28)/4)

=IF(AND(E28="",F28="",G28="",H28=""),"",SUM(E28:H28)/4)

Obviously, the second one produces the same result as DonkeyOte's formula, with exception that his one is simpler, but first one will keep the cell empty untill all four related cells are filled in.

Edit: BTW, why SUM(E28:H28)/4; why not just AVERAGE(E28:H28) ? That way, if you add new columns in between E and H, they will be automatically included in average, you won't have to edit the formula.

**DonkeyOte** · 10-06-2010, 10:54 AM

Originally Posted by froment

first one [or based] will keep the cell empty untill all four related cells are filled in.

@froment

If the intention is to average then we assume values are to be numeric in which case the below might be simpler:

=IF(COUNT(E28:H28)<4,"",SUM(E28:H28)/4)

or as you say

=IF(COUNT(E28:H28)<4,"",AVERAGE(E28:H28))

I think the point about use of AVERAGE is more than valid though at this stage we don't know if 3 numbers and a blank should say result in divisor of 3 or 4 ?
The answer to that determines viability of AVERAGE versus fixed divisor I think.

Whilst I am here... regards the other suggestions, FWIW, my two pennies:

Conditional Formatting is Super Volatile so I would always use this as method of last resort.
In this instance given the singular condition - if the intention is to "mask" whilst maintaining underlying numeric I would say a Number Format would be preferable.

Display Zeroes option - I would just add the caveat that this is a worksheet level setting and as such may / may not be appropriate.