+ Reply to Thread
Results 1 to 15 of 15

How to omit zeros in MIN Code

  1. #1
    Registered User
    Join Date
    07-07-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    82

    How to omit zeros in MIN Code

    I am using the following code:

    =AA7-MIN(AA7:AA1)

    Occasionally, there are "0" numbers in the range. I don't want to include the "0" when finding the low number. How do I add this to the code?

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: How to omit zeros in MIN Code

    Try utilizing SMALL() with COUNTIF()

    =AA7-SMALL(AA1:AA7,COUNTIF(AA1:AA7,0)+1)

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

    Re: How to omit zeros in MIN Code

    One way:


    =AA7-SMALL(AA7:AA1, COUNTIF(AA7:AA1, 0)+1)

  4. #4
    Registered User
    Join Date
    03-26-2011
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: How to omit zeros in MIN Code

    I'm not sure I'm 100% sure of what you're trying to do but heres a shot in the dark

    are you using the formula "AA7-MIN(AA7:AA1)" to create a new series and you want to exclude all entries where "AA7-MIN(AA7:AA1)=0" then you could nest it in an if command like the following:

    =if(AA7-MIN(AA7:AA1)=0,"",AA7-MIN(AA7:AA1))

    now when AA7-MIN(AA7:AA1) = 0 the calculation will dislay a blank and will not be used in calculating the min.


    in general if you've your series on column A you could create a new cleaned series in an adjacent column by using the formula "if(a1=0,"",a1)"

  5. #5
    Registered User
    Join Date
    07-07-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    82

    Re: How to omit zeros in MIN Code

    Quote Originally Posted by Cutter View Post
    Try utilizing SMALL() with COUNTIF()

    =AA7-SMALL(AA1:AA7,COUNTIF(AA1:AA7,0)+1)
    This works. But, I created another problem with it. It returns a minus number in the row where the "0" is located. How do I eliminate the minus numbers now?

  6. #6
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: How to omit zeros in MIN Code

    It depends on what you mean by eliminate the minus number.

    You can use MAX() to limit the result to zero or whatever other value you want.

    You can use IF() to test for neg result and provide an alternative.

    You'll have to be more specific.

  7. #7
    Registered User
    Join Date
    07-07-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    82

    Re: How to omit zeros in MIN Code

    Quote Originally Posted by Q&A View Post
    I'm not sure I'm 100% sure of what you're trying to do but heres a shot in the dark

    are you using the formula "AA7-MIN(AA7:AA1)" to create a new series and you want to exclude all entries where "AA7-MIN(AA7:AA1)=0" then you could nest it in an if command like the following:

    =if(AA7-MIN(AA7:AA1)=0,"",AA7-MIN(AA7:AA1))

    now when AA7-MIN(AA7:AA1) = 0 the calculation will dislay a blank and will not be used in calculating the min.


    in general if you've your series on column A you could create a new cleaned series in an adjacent column by using the formula "if(a1=0,"",a1)"
    I tried this. It presents another problem. The result, when left blank, does not work with another formula that it is included in.
    The other code is:
    =IF($C7<D$6," ",+IF($C7<(D$6+18),"*",+IF($C7<(D$6+36),"* *","* * *")))

    What happens in the 18 cells using the above code, is each cell get *** included instead of a blank.

    Another brain stormer.

  8. #8
    Registered User
    Join Date
    07-07-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    82

    Re: How to omit zeros in MIN Code

    Quote Originally Posted by Cutter View Post
    It depends on what you mean by eliminate the minus number.

    You can use MAX() to limit the result to zero or whatever other value you want.

    You can use IF() to test for neg result and provide an alternative.

    You'll have to be more specific.
    I don't want the minus to appear. I want a blank if the handicap is "0". Hope this explains it better. Sorry for the vagueness.

  9. #9
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: How to omit zeros in MIN Code

    Try this:

    =IF(SMALL(AA1:AA7,COUNTIF(AA1:AA7,0)+1)>=AA7,"",AA7-SMALL(AA1:AA7,COUNTIF(AA1:AA7,0)+1))

    assuming you mean this "AA7-SMALL(AA1:AA7,COUNTIF(AA1:AA7,0)+1)" is the handicap.

  10. #10
    Registered User
    Join Date
    07-07-2010
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    82

    Re: How to omit zeros in MIN Code

    Quote Originally Posted by Cutter View Post
    Try this:

    =IF(SMALL(AA1:AA7,COUNTIF(AA1:AA7,0)+1)>=AA7,"",AA7-SMALL(AA1:AA7,COUNTIF(AA1:AA7,0)+1))

    assuming you mean this "AA7-SMALL(AA1:AA7,COUNTIF(AA1:AA7,0)+1)" is the handicap.
    Thanks, but this doesn't work with the other code I have for the cells using the *** data.

    I think I am going to just use this code: =AA7-SMALL(AA7:AA1, COUNTIF(AA7:AA1, 0)+1) and deal with the minus number. I can white it out on the few occasions a minus number appears.

    Thank you all for your help...again.

    I have another question using this similar problem.

    I also have a code that provides 80% of the difference from the lowest number in the range. The code is: =ROUND((AA24-MIN(AA24:AA29) )*0.8,0)

    How can I get the same results when there is a "0" in AA24:AA29, and I don't want the "0" included in the MIN range.

    What the range would be:

    AA24 = 29
    AA25 = 0
    AA28 = 11
    AA29 = 0

    The smallest number would be 11 (if I don't count the 0s). The difference, between the 29 and 11 is 18. At 80% it is rounded to 14.

    The code above works fine as long as there are no zeros. With zeros, it is incorrect.

    Anybody want to take a crack at this one?

  11. #11
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: How to omit zeros in MIN Code

    Quote Originally Posted by bobbby1949 View Post
    I am using the following code:

    =AA7-MIN(AA7:AA1)

    Occasionally, there are "0" numbers in the range. I don't want to include the "0" when finding the low number. How do I add this to the code?
    Hi
    Maybe

    =AA7-MAX(1,MIN(AA7:AA1))
    --
    Regards
    Roger Govier
    Microsoft Excel MVP

  12. #12
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: How to omit zeros in MIN Code

    Your new question is the same as your original question. You've just changed the range.

    @Roger
    Your formula will result in AA7-1 when there is a 0 in the range because you are saying use the MAX of 1 and the MIN of the range so 1 will be the MAX of 1 and 0. I don't think OP wants that.
    Last edited by Cutter; 03-26-2011 at 08:04 PM.

  13. #13
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: How to omit zeros in MIN Code

    Quote Originally Posted by Cutter
    @Roger
    Your formula will result in AA7-1 when there is a 0 in the range because you are saying use the MAX of 1 and the MIN of the range so 1 will be the MAX of 1 and 0. I don't think OP wants that.
    You're quite right.
    I had totally misread the Op's requirement.
    Last edited by DonkeyOte; 03-27-2011 at 05:51 AM. Reason: fixed tag

  14. #14
    Valued Forum Contributor
    Join Date
    07-17-2005
    Location
    Abergavenny, Wales, UK
    MS-Off Ver
    XL2003, XL2007, XL2010, XL2013, XL2016
    Posts
    608

    Re: How to omit zeros in MIN Code

    Quote Originally Posted by bobbby1949 View Post
    I am using the following code:

    =AA7-MIN(AA7:AA1)

    Occasionally, there are "0" numbers in the range. I don't want to include the "0" when finding the low number. How do I add this to the code?
    Hi
    I think the array entered formula as follows, will do what you want.
    To array enter, use Control+Shift+Enter (CSE) when you enter or edit the formula.
    When you use CSE, Excel will insert curly braces around the formula - do not type them yourself.

    Please Login or Register  to view this content.

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

    Re: How to omit zeros in MIN Code

    just a question
    AA24 = 29
    AA25 = 0
    AA28 = 11
    AA29 = 0
    will a29 always be the largest? when used =ROUND((AA24-MIN(AA24:AA29) )*0.8,0)
    "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