I have a chart that is using numbers on a sheet. One of the columns
has a conditional formula in it that returns either a number or #N/A
using to avoid a divide by zero error. I using the formula N/A() if
the IF Statement formula would be divisible by Zero. I want this
because I don't want the chart to show a value that meets this
condition.

I would like to format the sheet using custom number formating to show
a 0 "zero" instead of #N/A. However I can't figure out how to use the
[Conditional] to work with the #N/A that is returned. I have tried
[=#N/A] 0.00 but excel changes this to [=0] 0.00 when I enter press
ok. Is there any way that this can be accomplished using custom number
formating?

Appreciate any help.

You could change your formula along these lines:

=IF(ISNA(your_formula),"",your_formula)

this will return a blank instead of the #N/A error, so this will not
affect your graph.

Hope this helps.

Pete

On Jan 21, 5:19*pm, wrote:
I have a chart that is using numbers on a sheet. One of the columns
has a conditional formula in it that returns either a number or #N/A
using to avoid a divide by zero error. I using the formula N/A() if
the IF Statement formula would be divisible by Zero. I want this
because I don't want the chart to show a value that meets this
condition.

I would like to format the sheet using custom number formating to show
a 0 "zero" instead of #N/A. However I can't figure out how to use the
[Conditional] to work with the #N/A that is returned. I have tried
[=#N/A] 0.00 but excel changes this to [=0] 0.00 when I enter press
ok. Is there any way that this can be accomplished using custom number
formating?

Appreciate any help.

Unfortunately I'm creating this for someone that wants the cells to
show a 0 "zero" not a "blank" or a "#N/A" I also thought of the same
solution. Thanks for responding. Hopefully I can find a solution using
the custom number formating.


On Jan 21, 10:35 am, Pete_UK wrote:
You could change your formula along these lines:

=IF(ISNA(your_formula),"",your_formula)

this will return a blank instead of the #N/A error, so this will not
affect your graph.

Hope this helps.

Pete

On Jan 21, 5:19 pm, wrote:

I have a chart that is using numbers on a sheet. One of the columns
has a conditional formula in it that returns either a number or #N/A
using to avoid a divide by zero error. I using the formula N/A() if
the IF Statement formula would be divisible by Zero. I want this
because I don't want the chart to show a value that meets this
condition.

I would like to format the sheet using custom number formating to show
a 0 "zero" instead of #N/A. However I can't figure out how to use the
[Conditional] to work with the #N/A that is returned. I have tried
[=#N/A] 0.00 but excel changes this to [=0] 0.00 when I enter press
ok. Is there any way that this can be accomplished using custom number
formating?

Appreciate any help.

Unfortunately I'm creating this for someone that wants the
cells to show a 0 "zero" not a "blank" or a "#N/A"

Why can't you do it this way then?

=IF(ISERR(YourFormula),0,YourFormula)

Rick

Because it then show up on the chart. I forgot to mention that the
chart is set up so the horizontal access crosses at a non zero number.
Sorry for the incomplete question.

On Jan 21, 11:00 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Unfortunately I'm creating this for someone that wants the
cells to show a 0 "zero" not a "blank" or a "#N/A"

Why can't you do it this way then?

=IF(ISERR(YourFormula),0,YourFormula)

Rick

I'm not sure it your Charts will see this as 0 or as not for error
conditions, but give this a try. Use this formula in the cell...

=IF(ISERR(YourFormula),"",YourFormula)

and use this Custom Format on that cell....

0.0#;-0.0#;0;--"0"

Obviously, change the format pattern for positive and negative values to
what you actually need.

Rick


wrote in message
...
Because it then show up on the chart. I forgot to mention that the
chart is set up so the horizontal access crosses at a non zero number.
Sorry for the incomplete question.

On Jan 21, 11:00 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Unfortunately I'm creating this for someone that wants the
cells to show a 0 "zero" not a "blank" or a "#N/A"

Why can't you do it this way then?

=IF(ISERR(YourFormula),0,YourFormula)

Rick

Because it then show up on the chart. I forgot to mention that the
chart is set up so the horizontal access crosses at a non zero number.
Sorry for the incomplete question.

On Jan 21, 11:00 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Unfortunately I'm creating this for someone that wants the
cells to show a 0 "zero" not a "blank" or a "#N/A"

Why can't you do it this way then?

=IF(ISERR(YourFormula),0,YourFormula)

Rick

Because it then show up on the chart. I forgot to mention that the
chart is set up so the horizontal access crosses at a non zero number.
Sorry for the incomplete question.

On Jan 21, 11:00 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Unfortunately I'm creating this for someone that wants the
cells to show a 0 "zero" not a "blank" or a "#N/A"

Why can't you do it this way then?

=IF(ISERR(YourFormula),0,YourFormula)

Rick

Unfortunately I'm creating this for someone that wants the cells to
show a 0 "zero" not a "blank" or a "#N/A" I also thought of the same
solution. Thanks for responding. Hopefully I can find a solution using
the custom number formating.


On Jan 21, 10:35 am, Pete_UK wrote:
You could change your formula along these lines:

=IF(ISNA(your_formula),"",your_formula)

this will return a blank instead of the #N/A error, so this will not
affect your graph.

Hope this helps.

Pete

On Jan 21, 5:19 pm, wrote:

I have a chart that is using numbers on a sheet. One of the columns
has a conditional formula in it that returns either a number or #N/A
using to avoid a divide by zero error. I using the formula N/A() if
the IF Statement formula would be divisible by Zero. I want this
because I don't want the chart to show a value that meets this
condition.

I would like to format the sheet using custom number formating to show
a 0 "zero" instead of #N/A. However I can't figure out how to use the
[Conditional] to work with the #N/A that is returned. I have tried
[=#N/A] 0.00 but excel changes this to [=0] 0.00 when I enter press
ok. Is there any way that this can be accomplished using custom number
formating?

Appreciate any help.

Unfortunately I'm creating this for someone that wants the cells to
show a 0 "zero" not a "blank" or a "#N/A" I also thought of the same
solution. Thanks for responding. Hopefully I can find a solution using
the custom number formating.


On Jan 21, 10:35 am, Pete_UK wrote:
You could change your formula along these lines:

=IF(ISNA(your_formula),"",your_formula)

this will return a blank instead of the #N/A error, so this will not
affect your graph.

Hope this helps.

Pete

On Jan 21, 5:19 pm, wrote:

I have a chart that is using numbers on a sheet. One of the columns
has a conditional formula in it that returns either a number or #N/A
using to avoid a divide by zero error. I using the formula N/A() if
the IF Statement formula would be divisible by Zero. I want this
because I don't want the chart to show a value that meets this
condition.

I would like to format the sheet using custom number formating to show
a 0 "zero" instead of #N/A. However I can't figure out how to use the
[Conditional] to work with the #N/A that is returned. I have tried
[=#N/A] 0.00 but excel changes this to [=0] 0.00 when I enter press
ok. Is there any way that this can be accomplished using custom number
formating?

Appreciate any help.

Perhaps the best bet may be to have one column on your worksheet which shows
a zero to keep your customer happy, and another column which has NA() and
plot that one.
--
David Biddulph

wrote in message
...
Unfortunately I'm creating this for someone that wants the cells to
show a 0 "zero" not a "blank" or a "#N/A" I also thought of the same
solution. Thanks for responding. Hopefully I can find a solution using
the custom number formating.


On Jan 21, 10:35 am, Pete_UK wrote:
You could change your formula along these lines:

=IF(ISNA(your_formula),"",your_formula)

this will return a blank instead of the #N/A error, so this will not
affect your graph.

Hope this helps.

Pete

On Jan 21, 5:19 pm, wrote:

I have a chart that is using numbers on a sheet. One of the columns
has a conditional formula in it that returns either a number or #N/A
using to avoid a divide by zero error. I using the formula N/A() if
the IF Statement formula would be divisible by Zero. I want this
because I don't want the chart to show a value that meets this
condition.

I would like to format the sheet using custom number formating to show
a 0 "zero" instead of #N/A. However I can't figure out how to use the
[Conditional] to work with the #N/A that is returned. I have tried
[=#N/A] 0.00 but excel changes this to [=0] 0.00 when I enter press
ok. Is there any way that this can be accomplished using custom number
formating?

Appreciate any help.

The big issue is that the charts have horizontal access that at a non
zero number. For example it crosses at 2.5 any zero, blank etc creates
a bar on the bar chart. I have to use N/A() to keep it from creating a
bar on the chart. The only solution that I can think of the using the
[conditional] custom format to take the =N/A() from the cell and
change it to 0.00 ie [=N/A()] 0.00. if [<=0] 0.00 or somthing like it
work work. How is the value N/A() returned in excel is it a number, or
text or something else?


On Jan 21, 11:09 am, wrote:
Unfortunately I'm creating this for someone that wants the cells to
show a 0 "zero" not a "blank" or a "#N/A" I also thought of the same
solution. Thanks for responding. Hopefully I can find a solution using
the custom number formating.

On Jan 21, 10:35 am, Pete_UK wrote:

You could change your formula along these lines:

=IF(ISNA(your_formula),"",your_formula)

this will return a blank instead of the #N/A error, so this will not
affect your graph.

Hope this helps.

Pete

On Jan 21, 5:19 pm, wrote:

I have a chart that is using numbers on a sheet. One of the columns
has a conditional formula in it that returns either a number or #N/A
using to avoid a divide by zero error. I using the formula N/A() if
the IF Statement formula would be divisible by Zero. I want this
because I don't want the chart to show a value that meets this
condition.

I would like to format the sheet using custom number formating to show
a 0 "zero" instead of #N/A. However I can't figure out how to use the
[Conditional] to work with the #N/A that is returned. I have tried
[=#N/A] 0.00 but excel changes this to [=0] 0.00 when I enter press
ok. Is there any way that this can be accomplished using custom number
formating?

Appreciate any help.

How is the value N/A() returned in excel is it a number, or
text or something else?

It appears to be "something else"... if you try to add or concatenate
anything to it, you just get the #N/A error back.

Rick

Doesn't the blank "" get plotted as a zero on the graph, Pete?
--
David Biddulph

"Pete_UK" wrote in message
...
You could change your formula along these lines:

=IF(ISNA(your_formula),"",your_formula)

this will return a blank instead of the #N/A error, so this will not
affect your graph.

Hope this helps.

Pete

On Jan 21, 5:19 pm, wrote:
I have a chart that is using numbers on a sheet. One of the columns
has a conditional formula in it that returns either a number or #N/A
using to avoid a divide by zero error. I using the formula N/A() if
the IF Statement formula would be divisible by Zero. I want this
because I don't want the chart to show a value that meets this
condition.

I would like to format the sheet using custom number formating to show
a 0 "zero" instead of #N/A. However I can't figure out how to use the
[Conditional] to work with the #N/A that is returned. I have tried
[=#N/A] 0.00 but excel changes this to [=0] 0.00 when I enter press
ok. Is there any way that this can be accomplished using custom number
formating?

Appreciate any help.