How to hide zeroes in cells

Hi Ralph,

'sound' advice depends on 'clear' problem statements.

"…in columns with a nil or zero value…" could mean 'some cells have no value entered' or 'some cells have a zero entered' or 'the formula in some cells has generated a zero value.'

"…have as yet no values added." implies that any value in these cells is an entered (ie. not calculated) value and that an 'empty' cell in "These cells" is actually empty and contains neither an entered datum nor a formula.

There is no menu command to ignore 'empty' cells. That must be built into the formulas referencing those cells.

There is no formula that can produce a truly 'empty' cell. The closest possible result is a cell containing either an invisible character (eg. a space) or a null string (a text item with zero length).

ISBLANK can be used to detect a truly empty (ie. blank) cell, but will not detect a cell containing a formula (which is, of course, not empty).

LEN(cell ref)<1 will return TRUE for a blank (empty) cell or for one containing a null string, but FALSE for one containing the numerical value zero.

AVERAGE, SUM, MIN, MAX, and some other functions will ignore cell containing text values (such as a space character, or a null string).

The arithmetic operators will throw an error on encountering a text value.

Here's an example of two formulas, one placed in D2, the other in E2, that will return the product of B2 and C2 if both contain a value, and will place a null string in D2 (making it visibly, but not actually 'empty') if either B2 or C2 are 'empty' (including cases where B2 or C2 contain a null string). Note the results in the bottom cell of each column.

In the bottom row, B10 contains a single character text string consisting of one space character. The multiplication operator expects two numbers, and throws an error when it gets a text value. The operator does not get the text values (null strings) in rows 4 and 7, as they are intercepted by IF.

PRODUCT ignores cells with text entries, including the single space in B10, and returns the correct product for all the remaining terms in it's list—PRODUCT(2)=2.

The best way to ignore zeroes in columns B and C is to avoid entering them (if the data is directly entered) or to use a formula that returns an empty string in place of a zero result. Numbers assumes that if you have put a zero in a cell (by hand or by formula), that zero is a legitimate piece of data, and should be included in any calculations referencing that cell.

Regards,

Barry

Ralph,

Not showing zeros can be quickly done with Conditional Format. My preference is to mute rather than completely blank out the zero, but that's just how I like to see it. You can choose to make the text color the same as the background fill, so it completely disapears, when the cell value is zero, or you can, as I do, set the opacity to 20% to fade the zero so that it doesn't catch the eye, but you can still tell what is going on.

Jerry

Hi Ralph,

Looks like I over-interpreted your use of "ignore". Zero values that are 'hidden' are still contained in the cell, and will be used in calculations depending on that cell. In some cases, this presents no problem, but in others (eg. AVERAGE(B3:B7) ) there will be a significant difference in the result where B4 and B6 contain (hidden) zeroes from that when B4 and B6 contain text values or are truly empty of data.

As your description mentioned calculations, I thought I'd better cover that base.

Regards,

Barry