Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you use a formula to make a truly "blank" cell
I have a setup that (simplified slightly) looks like:
A1: 20 B1: =if(A1<50,"",A1) C1: =isblank(B1) C1: produces "false". Why? Isn't "" supposed to put "nothing" into a cell? The point of all this: I want the cell to be truly blank, so that it doesn't graph as a "zero" in a chart. (Yes, I have the option under ToolsOptions checked, so that blank values are not plotted as zeros.) However, it is graphing my "not quite blank" cells, generated by the above formula, as zeros. It does skip truly blank values (if I delete the formula altogether), but doesn't when I try to force blank values using a formula like this. Any ideas how to get a formula to make a cell think it is blank? Thank you, Heidi |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you use a formula to make a truly "blank" cell
Hi!
Try this: =if(A1<50,#N/A,A1) That'll keep your chart from getting messed up but the downside is that you'll have #N/A's in some cells. You can hide them with conditional formatting, though. Select the cell(s) Goto FormatConditional Formatting Formula is: ISNA(cell_reference) Click the Format button Set the font color to be the same as the fill color OK out. Biff "Heidi" wrote in message ... I have a setup that (simplified slightly) looks like: A1: 20 B1: =if(A1<50,"",A1) C1: =isblank(B1) C1: produces "false". Why? Isn't "" supposed to put "nothing" into a cell? The point of all this: I want the cell to be truly blank, so that it doesn't graph as a "zero" in a chart. (Yes, I have the option under ToolsOptions checked, so that blank values are not plotted as zeros.) However, it is graphing my "not quite blank" cells, generated by the above formula, as zeros. It does skip truly blank values (if I delete the formula altogether), but doesn't when I try to force blank values using a formula like this. Any ideas how to get a formula to make a cell think it is blank? Thank you, Heidi |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you use a formula to make a truly "blank" cell
On Mon, 23 Jan 2006 13:13:03 -0800, "Heidi"
wrote: I have a setup that (simplified slightly) looks like: A1: 20 B1: =if(A1<50,"",A1) C1: =isblank(B1) C1: produces "false". Why? Isn't "" supposed to put "nothing" into a cell? The point of all this: I want the cell to be truly blank, so that it doesn't graph as a "zero" in a chart. (Yes, I have the option under ToolsOptions checked, so that blank values are not plotted as zeros.) However, it is graphing my "not quite blank" cells, generated by the above formula, as zeros. It does skip truly blank values (if I delete the formula altogether), but doesn't when I try to force blank values using a formula like this. Any ideas how to get a formula to make a cell think it is blank? Thank you, Heidi Well, if a cell contains a formula, then, by definition, it is not blank. If your goal is to have it ignored in a graph, then set the cell to NA. You can then use conditional formatting to make the cell appear blank, if you wish. e.g.: B1: =IF(A1<50,NA(),A1) Format/Conditional Formatting/Formula Is: =ISNA(B1) Format the font color to the same as the background color. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you use a formula to make a truly "blank" cell
That works great! Thanks so much! Thank you also for thinking ahead to make
the spreadsheet look prettier by "hiding" the error values with conditional formatting. Heidi "Biff" wrote: Hi! Try this: =if(A1<50,#N/A,A1) That'll keep your chart from getting messed up but the downside is that you'll have #N/A's in some cells. You can hide them with conditional formatting, though. Select the cell(s) Goto FormatConditional Formatting Formula is: ISNA(cell_reference) Click the Format button Set the font color to be the same as the fill color OK out. Biff "Heidi" wrote in message ... I have a setup that (simplified slightly) looks like: A1: 20 B1: =if(A1<50,"",A1) C1: =isblank(B1) C1: produces "false". Why? Isn't "" supposed to put "nothing" into a cell? The point of all this: I want the cell to be truly blank, so that it doesn't graph as a "zero" in a chart. (Yes, I have the option under ToolsOptions checked, so that blank values are not plotted as zeros.) However, it is graphing my "not quite blank" cells, generated by the above formula, as zeros. It does skip truly blank values (if I delete the formula altogether), but doesn't when I try to force blank values using a formula like this. Any ideas how to get a formula to make a cell think it is blank? Thank you, Heidi |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can you use a formula to make a truly "blank" cell
Thank you, Ron.
-Heidi "Ron Rosenfeld" wrote: On Mon, 23 Jan 2006 13:13:03 -0800, "Heidi" wrote: I have a setup that (simplified slightly) looks like: A1: 20 B1: =if(A1<50,"",A1) C1: =isblank(B1) C1: produces "false". Why? Isn't "" supposed to put "nothing" into a cell? The point of all this: I want the cell to be truly blank, so that it doesn't graph as a "zero" in a chart. (Yes, I have the option under ToolsOptions checked, so that blank values are not plotted as zeros.) However, it is graphing my "not quite blank" cells, generated by the above formula, as zeros. It does skip truly blank values (if I delete the formula altogether), but doesn't when I try to force blank values using a formula like this. Any ideas how to get a formula to make a cell think it is blank? Thank you, Heidi Well, if a cell contains a formula, then, by definition, it is not blank. If your goal is to have it ignored in a graph, then set the cell to NA. You can then use conditional formatting to make the cell appear blank, if you wish. e.g.: B1: =IF(A1<50,NA(),A1) Format/Conditional Formatting/Formula Is: =ISNA(B1) Format the font color to the same as the background color. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is it possible? | Excel Worksheet Functions | |||
copy and pasting a find all list into another column | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
FORMULA TO MAKE CELL COLOR CONTINGENT | Excel Discussion (Misc queries) | |||
how do i make a cell date sensitive to execute a formula or input. | Excel Discussion (Misc queries) |