Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default Custom number code [conditional] help

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.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default Custom number code [conditional] 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.


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default Custom number code [conditional] 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.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default Custom number code [conditional] 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.



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,202
Default Custom number code [conditional] 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


  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default Custom number code [conditional] 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.



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default Custom number code [conditional] help

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


  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default Custom number code [conditional] help

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


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default Custom number code [conditional] help

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


  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Custom number code [conditional] help

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.





  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,202
Default Custom number code [conditional] help

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



  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,651
Default Custom number code [conditional] 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.





  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 17
Default Custom number code [conditional] 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.




  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,202
Default Custom number code [conditional] 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Convert a Number Code to a Text Code Traye Excel Discussion (Misc queries) 3 April 6th 07 09:54 PM
How can I see custom formula code? Lee Harris Excel Worksheet Functions 2 December 6th 05 03:23 PM
can't find custom function code nathan Excel Worksheet Functions 7 November 2nd 05 11:15 PM
custom function-code for percentage srinivasan Excel Discussion (Misc queries) 1 June 8th 05 02:18 PM
Custom dat & time code Wuddus Excel Discussion (Misc queries) 1 February 16th 05 02:15 AM


All times are GMT +1. The time now is 09:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"