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

1. ## 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. ## 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. ## 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. ## 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. ## 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

6. ## 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. ## 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. ## 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.

#### Posting Permissions

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