# MIN formula that excludes 0

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

4. ## 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. ## Re: MIN formula that excludes 0

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. ## 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. ## Re: MIN formula that excludes 0

SUMPRODUCT(ABS(A1:A20))=0 is another possibly test

##### Users Browsing this Thread

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

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