One time I had a similar problem and solved it by using a very small number,
like .00001 instead of a zero. With the proper formatting, the display still
looks like a zero, and the small number does not materially affect any math
done to the numbers...........or, you could always but an X in those cells to
show acknowledgement of "No Production".

hth
Vaya con Dios,
Chuck, CABGx3




"LisaVH" wrote:

I have a spreadsheet I use to track production of a given facility. Some
facility's production is zero. I want to make a distinction between zero
production and a blank cell. Is this possible? I run a conditional format
to tell me when data is missing, however it sees the zero values the same as
a blank cell.

I was just about to post that. My sum formula doesn't like the text value in
place of the zero and if I use a decimal, I run the risk of it affecting my
sum columns (over time of course). Can I put in some type of =IS NULL into
the formula I am using for the conditional format. Here is what I am using
now. It is supposed to change the shading of the entire row if a value
within that row is blank (data missing). Right now, it changes the shading
if the cell is blank or zero.

=(COLUMNS(25:25)-COUNTBLANK(25:25))<MATCH(LOOKUP(2,1/(25:25<""),(25:25)),25:25,0)

"Marvin P. Winterbottom" wrote:

dividing by a very small number (instead of 0) will result in a very large
number which will really look weird. You need to use the IF statement to
check for 0 in division formulas. Also, putting an X instead of 0 will
result in #VALUE! in the cell with the division formula.

"CLR" wrote:

One time I had a similar problem and solved it by using a very small number,
like .00001 instead of a zero. With the proper formatting, the display still
looks like a zero, and the small number does not materially affect any math
done to the numbers...........or, you could always but an X in those cells to
show acknowledgement of "No Production".

hth
Vaya con Dios,
Chuck, CABGx3




"LisaVH" wrote:

I have a spreadsheet I use to track production of a given facility. Some
facility's production is zero. I want to make a distinction between zero
production and a blank cell. Is this possible? I run a conditional format
to tell me when data is missing, however it sees the zero values the same as
a blank cell.