return min. of range except 0

M

Melissa

What formula should I use to return the minimum no. in a range of numbers,
except 0?
e.g. 1, 4, 7, 0, 6, 8
The min should be 1 and not 0.
 
J

Jim Rech

One way:

=SMALL(A1:A6,IF(MIN(A1:A6)=0,2,1))

--
Jim
| What formula should I use to return the minimum no. in a range of numbers,
| except 0?
| e.g. 1, 4, 7, 0, 6, 8
| The min should be 1 and not 0.
 
D

Dave Peterson

=min(if(a1:a10<>0,a1:a10))
or if there's a chance you won't have any non-zero numbers:
=IF(COUNT(A1:A10)-COUNTIF(A1:A10,0)=0,"No data!",MIN(IF(A1:A10<>0,A1:A10)))

These are both array formulas. Hit ctrl-shift-enter instead of enter. If you
do it correctly, excel will wrap curly brackets {} around your formula. (don't
type them yourself.)

Adjust the range to match--but you can't use the whole column.))
 
Top