how formula subtract text w/o get #VALUE!

  • Thread starter Thread starter Ian Elliott
  • Start date Start date
I

Ian Elliott

I have a cell whose formula is =G11-G22
G11 is blank and G22 has "NA" in it.
(Another combination is "NA" in both cells)
(I have many of these cells, with the possibility in the
future that a number rather than "NA"-"Not Applicable"
will be the value)
The cell w/the formula becomes "#VALUE!"
Is there a way so that NA won't cause "#VALUE!" but will
just have "NA" in the cell w/the formula?
Perhaps something with =if(iserror?
Thanks very much.
 
Hi Ian!

You were nearly there.

Try:
=IF(ISERROR(G11-G22),"NA",G11-G22)

Note the general structure of the IF > ISERROR approach

On it's own:
=ISERROR(G11-G22)
Returns TRUE or FALSE depending upon whether G11-G22 returns an error.

IF function conditions must evaluate to TRUE or FALSE so this is ideal
for returning alternatives based upon the error arising or not.

Rather than use a manual entry of NA, you might be better off using
the function NA(). I say this because then you can use ISNA in error
checking rather than the brute force ISERROR that might exclude errors
that you really ought to know about.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Holidays and Observances Friday 18th July: Mexico (Day of Mourning
death of Benito Juarez), Spain (Labor Day), Uruguay (Constitution Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top