I hate #N/A

[Jaykill]

Hi

First Mr Excel poster long time Mr Excel problem solver abuser.

I hate #N/A results for three reasons:

1. They stuff up count, sum and other formulas when they are included in the array;

2. They look ugly; and

3. They make my charts look messy.

I have been sprawling the posts for hours trying to find out what is the best way to avoid seeing #N/A results in excel 2003. I am working on a spreadsheet where I need the #N/A so that my graphs won't look broken and incorrect but I hate seeing the result.

I tried using conditional formatting to make the #N/A the same colour as my background so it looks all neat and tidy. However for some reason it won't apply a conditional formula when Formula Is =A1="#N/A". It will do it if you say anything else just not #N/A. It also won't do it if you say Cell Is Equal to #N/A or "#N/A" or NA(). Its driving me crazy.

Does anyone have any suggestions for avoiding these results and still maintaining good graphs? If not, what solutions do people have to hide the results.

Thanks for letting me vent. I look forward to hearing your trick little strategies. Cookies for the best idea.

 

[stanleydgromjr]

Jaykill,

Please post one, or several, of the formulae that are displaying the "#N/A" errors.


Have a great day,
Stan

 

[J Ericson]

Have a look at the =ISNA() function. You can nest it in your existing functions if need be, and I think you should be able to use it for conditional formatting (don't have 2003 anymore for verification)

 

[Jaykill]

Deleted

 

[Jaykill]

So the situation I am in. I have tested the following formulas in Conditional Formatting.

So I am subtotalling my monthly purchasers in Row 10 and in Row 11 I am finding the cumulative totals. Using the following formula.
=IF(L10>0,SUM(K11+L10),NA())

I include NA() so that I can achieve the #N/A result because I have a graph pointing at these results.

I tried the following conditional formatting formulas to try and make the text white (same colour as background) if the cell result was #N/A:

Cell Value Is Equal to #N/A
Formula Is =$L$11=#N/A
Formula Is =$L$11="#N/A"
Formula Is =$L$11=NA()

The only other way I can try to hide the #N/A cells is by creating a new line underneath that is used for display purposes and hiding the line above used for graphing purposes. I guess I am just looking for an alternative that does not require duplicating data for seperate purposes.

 

[Greg Truby]

Welcome to the board. As J Ericson has already mentioned, one could use the ISNA() function to make your conditional formatting work.

Another common trick is to just create another worksheet that will serve as your actual chart source data and leave the #N/A errors in plain site and just hide the worksheet.

<SUP>edit</SUP> In your example - set your conditional formatting formula is to be =isna(L11) <SUB>/edit</SUB>

 

[Jaykill]

Aha! Thank you!

That works a treat. I need to add that into my usual formulas now.

Cheers!