#N/A values...

  • Thread starter Thread starter BigAl
  • Start date Start date
B

BigAl

is there a formula/option to turn off the display of this error? i have
a formula which shows this value when no data is entered, but its not
likely that the table will ever be full.

any ideas?
 
Searching in the Excel help (Excel 2002), you will find this:

=IF(ISERROR(formula),"",formula)
 
BigAl said:
is there a formula/option to turn off the display of this error? i have
a formula which shows this value when no data is entered, but its not
likely that the table will ever be full.
Click to expand...

Rewrite your formulas like this:
=IF(ISNA(YourFormula),"",YourFormula)
 
An example of ISNA
=IF(ISNA(VLOOKUP(C6,F4:J13,2,FALSE)),"",VLOOKUP(C6,F4:J13,2,FALSE))
The "" in the middle can be whatever you want eg Blank (""), zero or a
mesaage like "No Data"
Regards,
Alan.
 
Try 2 ways:

a. Use something like: =IF(ISNA(yourformula),"",yourformula)

b. Use conditional formatting (CF) to mask the #NA values

Select cell(s), say, A1

Click Format > Conditional Formatting

Set under Condition 1:
Formula is | =ISNA(A1)
Click Format > Font tab > Font Color (white) > OK
(or a font color which matches the cell fill / background color)

Click OK

The above will "mask" the #NA value from appearing

To copy the CF to other cells,
just select the cell already formatted
double click on the format painter button (brush icon)
then select to "paint" over other cell(s)

Press Esc when done to cancel
 
Max's second technique is especially useful if you need to create a chart from the data. Excel will interpret "" as zero, and plot points for these formulas. However, any cell with a #N/A error will be ignored when plotting.

Good Luck,
Mark Graesser
(e-mail address removed)

----- Max wrote: -----

Try 2 ways:

a. Use something like: =IF(ISNA(yourformula),"",yourformula)

b. Use conditional formatting (CF) to mask the #NA values

Select cell(s), say, A1

Click Format > Conditional Formatting

Set under Condition 1:
Formula is | =ISNA(A1)
Click Format > Font tab > Font Color (white) > OK
(or a font color which matches the cell fill / background color)

Click OK

The above will "mask" the #NA value from appearing

To copy the CF to other cells,
just select the cell already formatted
double click on the format painter button (brush icon)
then select to "paint" over other cell(s)

Press Esc when done to cancel
 
If you use conditional formatting to hide your errors, you may also wan
to make sure and suppress errors errors when printing:

Page Setup > Sheet > Cell errors as: <blank>

Otherwise many laser printers will print the text as black even thoug
you have it formatted as white
 
Back
Top