1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Need Help Formula with N/A & Numbers

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

Thread Status:
Not open for further replies.
Advertisement
  1. MCBride

    MCBride Thread Starter

    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
     

    Attached Files:

  2. The Villan

    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

    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.
     
  4. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    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.
     
  5. MCBride

    MCBride Thread Starter

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

    cwwozniak Trusted Advisor Spam Fighter

    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.
     
  7. MCBride

    MCBride Thread Starter

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

    cwwozniak Trusted Advisor Spam Fighter

    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

    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 (y)
     
  10. cwwozniak

    cwwozniak Trusted Advisor Spam Fighter

    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. :D
     
  11. maxflia10

    maxflia10

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

    =N(A1)-N(A2)-N(A3)
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

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

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/704648

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice