Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Is there a function with in Excel that returns zero for a undefined computation such as in 100 / 0?

    Thanks,
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In Excel 2003 and before, you can use IF in combination with ISERROR, e.g.

    =IF(ISERROR(A1/A2),0,A1/A2)

    In Excel 2007 and later, you can use IFERROR, e.g.

    =IFERROR(A1/A2,0)

  3. #3
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by jstevens View Post
    Is there a function with in Excel that returns zero for a undefined computation such as in 100 / 0?
    John
    Or try this,

    can be used in all Excel versions and without ISERROR testing function :

    =IF(A2,A1/A2,0)

    Regards
    Bosco

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by bosco_yip View Post
    Or try this,

    can be used in all Excel versions and without ISERROR testing function :

    =IF(A2,A1/A2,0)

    Regards
    Bosco
    While your solution works with numbers entered in A1:A2; if text is entered in either cell an error is returned. The solution offered by Hans "=IF(ISERROR(A1/A2),0,A1/A2)" does not succumb to this trap.
    Regards
    Don

  5. #5
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Don Wells View Post
    While your solution works with numbers entered in A1:A2; if text is entered in either cell an error is returned. The solution offered by Hans "=IF(ISERROR(A1/A2),0,A1/A2)" does not succumb to this trap.
    But, the OP asked for : " How to return zero when computation in 100 / 0 ? ( or A1/A2 )"

    I think the cell A1:A2 ( 100 / 0 ) were numbers

    The ISERROR() definite not required

    Regards
    Bosco

Posting Permissions

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