# Solved: Need Help Formula with N/A & Numbers

Discussion in 'Business Applications' started by MCBride, Apr 17, 2008.

Not open for further replies.

Joined:
Jul 10, 2004
Messages:
284
What formula do I use if I have columns that have N/A in them. Please see example.

Thanks

File size:
21.2 KB
Views:
42
2. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
You could use the IF() function together with ISNA() something like this

=IF(ISNA(VLOOKUP(A3,D3,1)),0,VLOOKUP(A3,D3,1))

Where the vlookup returns #NA, we put the ISNA() around the original formula and if it returns #NA, we ask the IF () function to place a 0 or anything you like as a result in the cell. If the ISNA check does not return #NA, then we ask the if statement to carry out the original formula.

Hope that makes sense.

3. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
incidentally, theer is a whole suite of IS????? functions

Function Returns TRUE if
ISBLANK Value refers to an empty cell.
ISERR Value refers to any error value except #N/A.
ISERROR Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
ISLOGICAL Value refers to a logical value.
ISNA Value refers to the #N/A (value not available) error value.
ISNONTEXT Value refers to any item that is not text. (Note that this function returns TRUE if value refers to a blank cell.)
ISNUMBER Value refers to a number.
ISREF Value refers to a reference.
ISTEXT Value refers to text.

Joined:
Nov 28, 2005
Messages:
64,244
First Name:
Chuck
I am not sure that ISNA() would work. The cells in the screen shot show N/A, not #N/A. This makes me think that the cell contains a manually entered text string of "N/A" and not an error condition. Then again I could be 100% wrong.

It might be nice to know exactly what the current formula is that MCBride is trying to fix.

Joined:
Jul 10, 2004
Messages:
284
Data to be entered should a number value unless it does not apply then would enter N/A

Joined:
Nov 28, 2005
Messages:
64,244
First Name:
Chuck
Assuming you are using MS-Excel, and the Net Tuition is in column G, then this formula might be what you want in cell G2 and then copied down the sheet for more rows:

=C2-IF(ISTEXT(D2),0,D2)-IF(ISTEXT(E2),0,E2)-IF(ISTEXT(F2),0,F2)

The formula does not specifically look for the N/A text in a cell. It just uses a number value of zero for any cell that has any kind of text.

Joined:
Jul 10, 2004
Messages:
284
Thank you so very, very much. Here is my donation.

Joined:
Nov 28, 2005
Messages:
64,244
First Name:
Chuck
You are very welcome.

If you consider this issue resolved, you can use the drop down Thread Tools menu to mark this thread as "Solved".

9. ### The Villan

Joined:
Feb 20, 2006
Messages:
2,255
Thats the great thing about those suite of functions. If one doesn't work, the other will.
Must admit, I didn't look too closely at the example uploaded, just tried to provide a possible solution. Should have noticed it was N/A.
Well spotted cwwozniak

Joined:
Nov 28, 2005
Messages:
64,244
First Name:
Chuck
Yep. I could have used ISNUMBER instead of ISTEXT to reverse the logic used by the IF functions but then I would have had to have typed a total of 6 extra letters to get the same result.

11. ### maxflia10

Joined:
Feb 24, 2003
Messages:
331
See the Help files for the N function,

=N(A1)-N(A2)-N(A3)

As Seen On

### Welcome to Tech Support Guy!

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!