+ Reply to Thread
Results 1 to 4 of 4

Formula function for ignoring cells/results equal to 0 (zero) ?

  1. #1
    Registered User
    Join Date
    04-02-2009
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2007
    Posts
    40

    Formula function for ignoring cells/results equal to 0 (zero) ?

    Assume I want to setup a formula for a cell which should calculate the result from several other cells
    similar to

    C1=K5/k6 + U8/U9 + M12/M13

    As you can see if the value of the cells K6 or U9 or M13 are zero there is a division by zero
    and the calculation crashes. In this case the terms should be ignored.

    What I need now is a precalculation-and-ignore-instruction which excludes the terms similar to

    C1=ignoreiftrue(K6=0,K5/k6) + ignoreiftrue(U9=0,U8/U9) + ignoreiftrue(M13=0,M12/M13)

    In more general form the function

    ignoreiftrue(<condition>,<expression>)

    should evaluate at first the condition and if it is NOT true uses the expression(=function) as usual.

    Is there such a function in Excel?

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,627

    Re: Formula function for ignoring cells/results equal to 0 (zero) ?

    try =IF(K6*U9*M13=0;"can't divide by zero";C1=K5/K6 + U8/U9 + M12/M13)

    Or =IF(AND(K6=0;M13=0;U9=0);.....)

  3. #3
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

    Re: Formula function for ignoring cells/results equal to 0 (zero) ?

    Or maybe

    =IF(ISERROR(K5/K6),0,K5/K6)+IF(ISERROR(U8/U9),0,U8/U9)+IF(ISERROR(M12/M13),0,M12/M13)
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  4. #4
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: Formula function for ignoring cells/results equal to 0 (zero) ?

    Try it as its working properly

    =IF((K6=0),0,(K5/K6))+IF((M13=0),0,(M12/M13))+IF((U9=0),0,(U8/U9))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1