How to have cell values of 0 showing as blank
 

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?

How to have cell values of 0 showing as blank
 

=IF(Sheet3!A1<"",Sheet3!A1,"")


=IF(Sheet3!A1=0,"",Sheet3!A1)#

try either of these


"Alex" wrote:

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?

How to have cell values of 0 showing as blank
 

Worked beautifully, is there any way of not getting those 0's to not show on
a chart as well?

"Mike" wrote:

=IF(Sheet3!A1<"",Sheet3!A1,"")


=IF(Sheet3!A1=0,"",Sheet3!A1)#

try either of these


"Alex" wrote:

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?

How to have cell values of 0 showing as blank
 

Sorry I don't do charts. Thanks for the feedback.

"Alex" wrote:

Worked beautifully, is there any way of not getting those 0's to not show on
a chart as well?

"Mike" wrote:

=IF(Sheet3!A1<"",Sheet3!A1,"")


=IF(Sheet3!A1=0,"",Sheet3!A1)#

try either of these


"Alex" wrote:

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?

How to have cell values of 0 showing as blank
 

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))

--
Regards

Roger Govier


"Alex" wrote in message
...
Worked beautifully, is there any way of not getting those 0's to not
show on
a chart as well?

"Mike" wrote:

=IF(Sheet3!A1<"",Sheet3!A1,"")


=IF(Sheet3!A1=0,"",Sheet3!A1)#

try either of these


"Alex" wrote:

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?

How to have cell values of 0 showing as blank
 

I did that, but it gets stranger, the value pn the chart also shows as #N/A.
Why would this be?

"Roger Govier" wrote:

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))

--
Regards

Roger Govier


"Alex" wrote in message
...
Worked beautifully, is there any way of not getting those 0's to not
show on
a chart as well?

"Mike" wrote:

=IF(Sheet3!A1<"",Sheet3!A1,"")


=IF(Sheet3!A1=0,"",Sheet3!A1)#

try either of these


"Alex" wrote:

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?

How to have cell values of 0 showing as blank
 

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?

"Roger Govier" wrote:

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))

--
Regards

Roger Govier


"Alex" wrote in message
...
Worked beautifully, is there any way of not getting those 0's to not
show on
a chart as well?

"Mike" wrote:

=IF(Sheet3!A1<"",Sheet3!A1,"")


=IF(Sheet3!A1=0,"",Sheet3!A1)#

try either of these


"Alex" wrote:

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?

How to have cell values of 0 showing as blank
 

Hi Catherine

Mark the range of cells where your #N/A's appear.
FormatConditional Formattinguse dropdown for Formula is
=NOT(ISNUMBER(A1))

Replace A1 with the cell address of the first cell in your marked range.
--
Regards

Roger Govier


"Catherine" wrote in message
...
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?

"Roger Govier" wrote:

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))

--
Regards

Roger Govier


"Alex" wrote in message
...
Worked beautifully, is there any way of not getting those 0's to
not
show on
a chart as well?

"Mike" wrote:

=IF(Sheet3!A1<"",Sheet3!A1,"")


=IF(Sheet3!A1=0,"",Sheet3!A1)#

try either of these


"Alex" wrote:

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?

How to have cell values of 0 showing as blank
 

Thank you very much. That worked perfectly.

"Roger Govier" wrote:

Hi Catherine

Mark the range of cells where your #N/A's appear.
FormatConditional Formattinguse dropdown for Formula is
=NOT(ISNUMBER(A1))

Replace A1 with the cell address of the first cell in your marked range.
--
Regards

Roger Govier


"Catherine" wrote in message
...
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?

"Roger Govier" wrote:

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))

--
Regards

Roger Govier


"Alex" wrote in message
...
Worked beautifully, is there any way of not getting those 0's to
not
show on
a chart as well?

"Mike" wrote:

=IF(Sheet3!A1<"",Sheet3!A1,"")


=IF(Sheet3!A1=0,"",Sheet3!A1)#

try either of these


"Alex" wrote:

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?

How to have cell values of 0 showing as blank
 

assuming that the 0 is a result of a formula

=if((yourformula)=0,"",(yourformula))


"Catherine" wrote in message
...
Thank you very much. That worked perfectly.

"Roger Govier" wrote:

Hi Catherine

Mark the range of cells where your #N/A's appear.
FormatConditional Formattinguse dropdown for Formula is
=NOT(ISNUMBER(A1))

Replace A1 with the cell address of the first cell in your marked range.
--
Regards

Roger Govier


"Catherine" wrote in message
...
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?

"Roger Govier" wrote:

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))

--
Regards

Roger Govier


"Alex" wrote in message
...
Worked beautifully, is there any way of not getting those 0's to
not
show on
a chart as well?

"Mike" wrote:

=IF(Sheet3!A1<"",Sheet3!A1,"")


=IF(Sheet3!A1=0,"",Sheet3!A1)#

try either of these


"Alex" wrote:

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?