#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 12
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 12
Default 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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 347
Default 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.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 611
Default 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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reconciliation of Data With Two Conditions Tiziano Excel Worksheet Functions 3 December 13th 06 03:36 AM
Excel Macro to Copy & Paste [email protected] Excel Worksheet Functions 0 December 1st 05 02:56 PM
From several workbooks onto one excel worksheet steve Excel Discussion (Misc queries) 6 December 1st 05 09:03 AM
Help PLEASE! Not sure what answer is: Match? Index? Other? baz Excel Worksheet Functions 7 September 3rd 05 03:47 PM
Sort pages? David Excel Discussion (Misc queries) 15 May 13th 05 11:33 PM


All times are GMT +1. The time now is 09:44 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"