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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 08:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com