zero as data value
 

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.

zero as data value
 

It looks like what I want can't be done. I will just fake it. Thanks for
your help.

"CLR" wrote:

FWIW, the SUM() function is not affected by text values in the range. But
here is a simpler Conditional Formatting formula for you, replace column C
with the one of your choice.........

=LEN($C1)=0

hth
Vaya con Dios,
Chuck, CABGx3




"LisaVH" wrote:

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.

zero as data value
 

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.

zero as data value
 

Hi,

I just can't see CF treating a blank cell and a cell with 0 value the same.
What formula are you using for the production values??

Regards
Jean-Guy

"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.

zero as data value
 

Lisa,

You don't say what "run a conditional format" is in this case. If you're
using Format - Conditional Formatting to highlight empty cells, try this.
It will not highlight a zero cell, but will highlight an empty cell.

Select the cells. Note the active (white) cell of your selection
Format - Conditional formatting
Select "Formula is:" =C1="" Include both quote marks. In this case,
C1 is the active cell.
Click Format and set the formatting you want to indicate an empty cell.
OK
OK

OK?
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"LisaVH" wrote in message
...
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.