Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reconciliation of Data With Two Conditions | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) |