A
Alex
I am linking data from another worksheet, but I want any value which is 0 to
show a blank cell. What would be the easies way to do this?
show a blank cell. What would be the easies way to do this?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
M
Mike
=IF(Sheet3!A1<>"",Sheet3!A1,"")
=IF(Sheet3!A1=0,"",Sheet3!A1)#
try either of these
=IF(Sheet3!A1=0,"",Sheet3!A1)#
try either of these
A
Alex
Worked beautifully, is there any way of not getting those 0's to not show on
a chart as well?
a chart as well?
M
Mike
Sorry I don't do charts. Thanks for the feedback.
R
Roger Govier
Hi Alex
If you want the chart to ignore zero values you would need to use NA()
in place of Null in Mike's formulae
=IF(Sheet3!A1<>"",Sheet3!A1,NA())
This will show #N/A in the cells, which the Chart will ignore as a
datapoint.
If the aesthetics of having #N/A showing on the sheet bothers you, then
you could apply conditional formatting on the cells to set the Font the
same colours as the background of the cell.
In the CF, use Formula Is and =NOT(ISNUMBER(A1))
If you want the chart to ignore zero values you would need to use NA()
in place of Null in Mike's formulae
=IF(Sheet3!A1<>"",Sheet3!A1,NA())
This will show #N/A in the cells, which the Chart will ignore as a
datapoint.
If the aesthetics of having #N/A showing on the sheet bothers you, then
you could apply conditional formatting on the cells to set the Font the
same colours as the background of the cell.
In the CF, use Formula Is and =NOT(ISNUMBER(A1))
A
Alex
I did that, but it gets stranger, the value pn the chart also shows as #N/A.
Why would this be?
Why would this be?
C
Catherine
I am having a problem similar to Alex's. I am trying to hide the #N/A
symbol. I tried the conditional formatting angle, but it didn't work. Any
suggestions?
symbol. I tried the conditional formatting angle, but it didn't work. Any
suggestions?
R
Roger Govier
Hi Catherine
Mark the range of cells where your #N/A's appear.
Format>Conditional Formatting>use dropdown for Formula is>
=NOT(ISNUMBER(A1))
Replace A1 with the cell address of the first cell in your marked range.
Mark the range of cells where your #N/A's appear.
Format>Conditional Formatting>use dropdown for Formula is>
=NOT(ISNUMBER(A1))
Replace A1 with the cell address of the first cell in your marked range.
C
Catherine
Thank you very much. That worked perfectly.
Roger Govier said:Hi Catherine
Mark the range of cells where your #N/A's appear.
Format>Conditional Formatting>use dropdown for Formula is>
=NOT(ISNUMBER(A1))
Replace A1 with the cell address of the first cell in your marked range.
G
Gary
assuming that the 0 is a result of a formula
=if((yourformula)=0,"",(yourformula))
=if((yourformula)=0,"",(yourformula))