+ Reply to Thread
Results 1 to 7 of 7

MIN formula that excludes 0

  1. #1
    Registered User
    Join Date
    04-23-2010
    Location
    Ashford, England
    MS-Off Ver
    Excel 2007
    Posts
    27

    MIN formula that excludes 0

    Is there a formula that will search a range and return the cell containing the lowest number but excluding zero.

  2. #2
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: MIN formula that excludes 0

    MIN(IF(A1:A10<>0,A1:A10)) entered using Ctrl+Shift+Enter does that for A1:A10.

    Beau Nydal

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: MIN formula that excludes 0

    Try this
    Please Login or Register  to view this content.
    This should return the smallest value in the range including negative numbers but excluding zero.

    or, to allow for all the values being zero or blank and therefore avoid a #NUM! error
    Please Login or Register  to view this content.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  4. #4
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: MIN formula that excludes 0

    Thanks for those Marcol, first time I've ever seen non-array formulae for this task.

    Beau Nydal

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: MIN formula that excludes 0

    Quote Originally Posted by Marcol
    or, to allow for all the values being zero or blank and therefore avoid a #NUM! error
    Code:
    =IF(SUM(A1:A20)=0,"",IF(MIN(A1:A20)=0,SMALL(A1:A20,COUNTIF($A$1:$A$20,0)+1),MIN(A1:A20)))
    I am not sure using SUM = 0 to test for all 0's is feasible if we assume the range could theoretically net to 0 (-2; 2)
    perhaps better to use COUNTIF(range,0)=COUNT(range) ? AVERAGE would be feasible test if you know you will always have min of 1 number in range

    In this instance given OP use of XL2007 you could simply wrap an IFERROR around your original IF.

    Please Login or Register  to view this content.

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: MIN formula that excludes 0

    Aye Don, as ever I've gone off half-cocked.
    Thanks for pointing that out.

    Perhaps this could be another way.
    Please Login or Register  to view this content.
    As for IFERROR(), agreed, but there are still a fair amount of 2003 users about, so I tend to offer solutions to suit them.
    I'm working on a theory I have, that once 2007 users, myself included, discover IFERROR(), it's never forgotten, so they will "fix" it themselves.

    Thanks again.

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: MIN formula that excludes 0

    SUMPRODUCT(ABS(A1:A20))=0 is another possibly test
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ 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