MIN formula that excludes 0

**albatross32** · 08-13-2011, 05:22 AM

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

**beaunydal** · 08-13-2011, 05:58 AM

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

Beau Nydal

**Marcol** · 08-13-2011, 05:58 AM

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.

**beaunydal** · 08-13-2011, 07:46 PM

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

Beau Nydal

**DonkeyOte** · 08-14-2011, 01:51 AM

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.

**Marcol** · 08-14-2011, 05:29 AM

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.