Using the IsError Function

  • i am trying to incorporate the isError function with the following code:



    IF(C45>350,LOG(C45,10000000),IF(C45>200,LOG(C45,100000),IF(C45>20,LOG(C45,1000),IF(C45<21,LOG(C45,5),0))))



    How would I do that? When the C45 contains zero, I get a #Num error in the cells


    thanks

  • Re: Using the IsError Function


    How about something like this?


    IF(C45=0,"invalid",IF(C45>350,LOG(C45,10000000),IF(C45>200,LOG(C45,100000),IF(C45>20,LOG(C45,1000),IF(C45<21,LOG(C45,5),0)))))


    or a shorter version:


    IF(C45=0,"invalid",LOG(C45,IF(C45>350,10000000,IF(C45>200,100000,IF(C45>20,1000,5)))))

  • Re: Using the IsError Function


    How would I incorporate that into this formula:


    IF(C45>350,LOG(C45,10000000),IF(C45>200,LOG(C45,100000),IF(C45>20,LOG(C45,1000),IF(C45<21,LOG(C45,5),0))))


    I put If(IsError(C45),"",...)))) but I got an error message.


    Should i have put If(IsError(C45=0) instead?

  • Re: Using the IsError Function



    The formula is very long, so I enclosed it in code tags so you can simply highlight the entire line in the code window and paste it into the appropriate cell in your worksheet. Use the scroll buttons to read through the whole thing. A brief explanation follows the formula.


    Code
    =IF(ISERROR(IF(C45>350,LOG(C45,10000000),IF(C45>200,LOG(C45,100000),IF(C45>20,LOG(C45,1000),IF(C45<21,LOG(C45,5),0))))),"",IF(C45>350,LOG(C45,10000000),IF(C45>200,LOG(C45,100000),IF(C45>20,LOG(C45,1000),IF(C45<21,LOG(C45,5),0)))))


    The IF(ISERROR(your formula) evaluates the formula you quoted. If an error occurs, his will leave nothing in the cell it is placed in if an error occurs (the "" part does that). If no error occurs, then your formula is evaluated and the result is placed in the cell.


    Let me know if you need further help. :)

  • Re: Using the IsError Function


    Hi,


    Since there is only one possible error in the formula (the final IF), the simplest usage of 'iserror' is:


    =IF(C45>350,LOG(C45,10000000),IF(C45>200,LOG(C45,100000),IF(C45>20,LOG(C45,1000),IF(C45<21,IF(ISERROR(LOG(C45,5)),"",LOG(C45,5))))))


    and the way to do this formula without 'iserror' would be to 'AND' the last IF:


    =IF(C45>350,LOG(C45,10000000),IF(C45>200,LOG(C45,100000),IF(C45>20,LOG(C45,1000),IF(AND(C45<21,C45>0),LOG(C45,5),""))))


    Also, I couldn't help notice that the last two statements are: IF c45>20 and then IF c45<21...?



    ...ducking while running...


    Cheers,



    dr

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!