Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Location
    Vancouver, Br. Columbia
    Posts
    223
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ignoring 0's and getting min (Excel 2000)

    I am wondering if there is a way I can write a min fuction which ignores 0's and give me an actual value. ex. I have column with the numbers

    0
    1
    2
    3
    4
    5
    min returns 0
    max returns 5

    I woul like min to return 1 instead of 0 here,

    ??
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ignoring 0's and getting min (Excel 2000)

    Say your values are in A1:A10. You can use the following array formula (confirm with Ctrl+Shift+Enter instead of just Enter):
    <code>
    =MIN(IF(A1:A10>0,A1:A10))</code>

  3. #3
    Lounger
    Join Date
    Nov 2004
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ignoring 0's and getting min (Excel 2000)

    You may be able to use the SMALL function. Say your data are in A1..A10, then SMALL(A1:A10,2) gives the 2nd smallest number. However, if you have multiple zeros, they are counted each time.
    ruth

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: ignoring 0's and getting min (Excel 2000)

    You could count the zeroes and use small:
    <pre>=SMALL(A1:A10,COUNTIF(A1:A10,0)+1)</pre>


    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ignoring 0's and getting min (Excel 2000)

    Hi there

    yet another suggestion <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Similar to Hans' suggestion where his acts on positive values, you can use this:
    =MIN(IF(A1:A7<>0,A1:A7,FALSE)) (ctrl+shift+enter to confirm)

    which checks for negative values as well but ignores zeros
    Jerry

  6. #6
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ignoring 0's and getting min (Excel 2000)

    wow, well tank you all, i dont really know which one to use now
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ignoring 0's and getting min (Excel 2000)

    Try all of them, and see which one you like best. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ignoring 0's and getting min (Excel 2000)

    The formulas do different things if there could be negative numbers in the list. Hans' formula will give the minimum positive number, ignoring zeros and negative numbers. Steve's formula will give the minimum ignoring only zero values. Which one you use depends on what your numbers are and what you want if there are negative numbers.
    Legare Coleman

Posting Permissions

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