Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IS error command to remove N/A (2003)

    I was told there is command IS error to remove the N/A value in a formula, see screenshot.

    Does anyone know of the synatax of this command and if possible could show me an example?
    Attached Images Attached Images

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IS error command to remove N/A (2003)

    You don't need an example sheet just type the following

    1) In A1 type 1
    2) In B1 type 0
    3) In C1 paste the following: =IF(ISERROR(A1/B1),"You cannot divide by zero",A1/B1)

    You could use:

    =IF(ISERROR(A1/B1),"",A1/B1)

    Which just leave a blank cell
    Jerry

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IS error command to remove N/A (2003)

    Hi

    Click in 'Format' in the tool bar and select 'Conditional Formating', 'Formula is' and then type in =iserror(a1) then choose Format and select a font that corrosponds with your background colour, usually white, click OK to exit and then drag cell A1 down as far as you want to.

    Cheers

    Steve

    (See Hans I did learn something <img src=/S/grin.gif border=0 alt=grin width=15 height=15> )
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IS error command to remove N/A (2003)

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15> ...but...but....but

    Mr Hocking, see me after school, whilst very clever <img src=/S/clever.gif border=0 alt=clever width=15 height=15> Isn't that the equivalent of painting over the cracks <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I suppose it could be useful to tidy the sheet up if the data was also being used for charting but couldn't you just hide the column <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  5. #5
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IS error command to remove N/A (2003)

    Thatís great thanks

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: IS error command to remove N/A (2003)

    Note that with many printers that print in Black and white this only works with a white background. For example, If you have colored text on the same colored background and print in shades of gray, the text is black and the background is gray (if in grayscale) if printed in true black and white, the background is white and the text is still black.

    Steve

  7. #7
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IS error command to remove N/A (2003)

    Steve
    Further to Jerry and Steve's observations, your solution leaves the cell virtually unusable as a precedent for further calculations.
    Regards
    Don

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IS error command to remove N/A (2003)

    Hi Jezza

    Although it is above me I know that you can use the iserror in your formula as well but I have never managed to get it working, I saw a post where somebody asked for a prevailing alpha to be removed and the advice was

    =MID(A1,IF(ISERROR(VALUE(MID(A1,2,1))),IF(ISERROR( VALUE(MID(A1,3,1))),IF(ISERROR(VALUE(MID(A1,4,1))) ,IF(ISERROR(VALUE(MID(A1,5,1))),"ERROR",5),4),3),2 ),256)

    which presumambly would not show any errors

    Anyway off on hols for a week, I can hear her screaming from the car for me already

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: IS error command to remove N/A (2003)

    No problems Steve, I was only pulling your leg <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Have a great holiday.
    Jerry

Posting Permissions

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