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. |
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. |
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. |
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. |
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 |
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. |
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 |
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 |
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 |
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. |
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 |
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. |
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. |
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 |
All times are GMT +1. The time now is 11:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com