Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I formulaically set a cell to blank (non-Text, no value)?
There is an old post from 2003 with the same heading - you may wish to
have a look at the responses there as well. I am trying to make the value of a cell blank as the result of a formula: eg: =if(a13,BLANK,a2-a3) If I use "" or nothing at all, I get a text value or 0 which when graphed will graph as 0. I want these cells to be blank, so that if they are referenced in a graph (or chart) they don't plot on the graph at all. At the moment they are plotting as 0. Any suggestions besides a macro which I think is overkill for my purposes? Thanks and Kind regards Mick |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I formulaically set a cell to blank (non-Text, no value)?
On 2 May 2006 01:01:46 -0700, "
wrote: There is an old post from 2003 with the same heading - you may wish to have a look at the responses there as well. I am trying to make the value of a cell blank as the result of a formula: eg: =if(a13,BLANK,a2-a3) If I use "" or nothing at all, I get a text value or 0 which when graphed will graph as 0. I want these cells to be blank, so that if they are referenced in a graph (or chart) they don't plot on the graph at all. At the moment they are plotting as 0. Any suggestions besides a macro which I think is overkill for my purposes? Thanks and Kind regards Mick I believe the graph will ignore NA values, so you could use, in your cell, =IF(A13,NA(),A2-A3) --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I formulaically set a cell to blank (non-Text, no value)?
That is correct - #N/A will not be charted on a graph
For visual purposes, you can also set the font to white using conditional formatting and testing for ISNA e.g. Select chart data Go FormatConditional Formatting change dropdown to "Formula Is" enter =ISNA(A1) where the top left cell in your chart data is A1. Set font to white et voila -- Rgds, Geoff "A crash reduces Your expensive computer To a simple stone" "Ron Rosenfeld" wrote: On 2 May 2006 01:01:46 -0700, " wrote: There is an old post from 2003 with the same heading - you may wish to have a look at the responses there as well. I am trying to make the value of a cell blank as the result of a formula: eg: =if(a13,BLANK,a2-a3) If I use "" or nothing at all, I get a text value or 0 which when graphed will graph as 0. I want these cells to be blank, so that if they are referenced in a graph (or chart) they don't plot on the graph at all. At the moment they are plotting as 0. Any suggestions besides a macro which I think is overkill for my purposes? Thanks and Kind regards Mick I believe the graph will ignore NA values, so you could use, in your cell, =IF(A13,NA(),A2-A3) --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I formulaically set a cell to blank (non-Text, no value)?
This should leave the cell blank but not sure if graph will work as you want.
=IF(A13,"",A2-A3) TC " wrote: There is an old post from 2003 with the same heading - you may wish to have a look at the responses there as well. I am trying to make the value of a cell blank as the result of a formula: eg: =if(a13,BLANK,a2-a3) If I use "" or nothing at all, I get a text value or 0 which when graphed will graph as 0. I want these cells to be blank, so that if they are referenced in a graph (or chart) they don't plot on the graph at all. At the moment they are plotting as 0. Any suggestions besides a macro which I think is overkill for my purposes? Thanks and Kind regards Mick |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I formulaically set a cell to blank (non-Text, no value)?
No, I tried that. It still leaves a value in there that is graphed as
0. The suggestion above with the #N/A values should work from my testing. Thanks TC |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I formulaically set a cell to blank (non-Text, no value)?
Thanks Geoff.
That is exactly what I was after. Regards MT |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I formulaically set a cell to blank (non-Text, no value)?
To continue on from these posts;
If your chart data is like this A B s 5 d N/A f 7 g 10 then as stated in the other posts your graph won't plot the N/A but it still leaves an empty space marked "d". Can you get the graph to ignore all N/A/0/blank cells and the headings? wrote in message ups.com... No, I tried that. It still leaves a value in there that is graphed as 0. The suggestion above with the #N/A values should work from my testing. Thanks TC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Limitations ? Formatting and Size when exporting to Tab based Text file? | Excel Worksheet Functions | |||
Shade cell according to text? | Excel Discussion (Misc queries) | |||
Instead of a negative number, I'd like to show zero... | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
adding a formula in a cell but when cell = 0 cell is blank | Excel Worksheet Functions |