Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Don't want NA visible (Excel 2000)

    If I do not want NA to show up in a cell just because there is not data in reliant cells yet, how can I get it to go away. For example, I have copied a formula down a column to calculate hours worked, but I have not typed in the hours worked in the adjacent cells yet, so I want to see nothing in the calculated cell or a dash or something unless I do type in the actual hours worked in the adjacent cells.
    Thank you
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Don't want NA visible (Excel 2000)

    Say that you have a formula
    <code>
    =BLAHBLAH
    </code>
    that may result in #N/A. To avoid the #N/A, change the formula to
    <code>
    =IF(ISNA(BLAHBLAH),"",BLAHBLAH)
    </code>
    If you also expect other error values, you can use ISERROR instead of ISNA. And you can replace <code>""</code> with <code>"-"</code> or whatever you prefer.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't want NA visible (Excel 2000)

    If your formula is =A2-A1, then you could change it to:

    =IF(ISNA(A2-A1),"",A2-A1)

    or if you want to suppress all errors:

    =IF(ISERROR(A2-A1),"",A2-A1)
    Legare Coleman

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't want NA visible (Excel 2000)

    Works wonderful on my time subtraction cells, however i must be typing it wrong for the vlookup...

    =IF(ISNA(E4),"",VLOOKUP(E4,InstructorPay,2))

    So if E4 has nothing in it yet, (an Instructor Name)...I want an empty cell or dash, else go look in the InstructorPay RangeName and tell me their pay in column 2

    Thank you Hans
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't want NA visible (Excel 2000)

    Yup:

    =IF(ISNA(VLOOKUP(E4,InstructorPay,2)),"",VLOOKUP(E 4,InstructorPay,2))
    Legare Coleman

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't want NA visible (Excel 2000)

    If E4 has nothing in it yet, (an Instructor Name)... then I presume it is an empty cell, not an #N/A error value. In this case, I'd suggest:

    =IF(TRIM(E4)="","",VLOOKUP(E4,InstructorPay,2))

    Alan

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't want NA visible (Excel 2000)

    That worked great on the VLookup formula, but changed the "" empty cells in another formula i have going...from the formula Hans gave me to VALUE
    Attached spreadsheet
    Your VOOKUP in column F
    Hans Formula in column J
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't want NA visible (Excel 2000)

    The formula you want in column J is:

    =IF(ISERROR(I2*F2),"",I2*F2)
    Legare Coleman

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't want NA visible (Excel 2000)

    Bless you
    You guys sure know your stuff. And I appreciate your patience.
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't want NA visible (Excel 2000)

    I have tried pretty much the same formulas you have given me in column B to get rid of the Saturdays unless a date is actually typed in column A, but again I'm still getting NAME or FALSE. Any thoughts on column B and getting that column to hide as well unless I type it in?
    I am typing currently:
    =IF(ISNA(VLOOKUP(A2,PayIs,2)),"",VLOOKUP(A2,PayIs, 2))
    Oh
    And I tried the TRIM as well with same NAME result
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't want NA visible (Excel 2000)

    How about:

    =IF(A2="","",VLOOKUP(B2,DayIs,2))
    Legare Coleman

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Don't want NA visible (Excel 2000)

    Beautiful...Thanks a million Legare
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •