return min. of range except 0
 

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.

return min. of range except 0
 

One way:

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

--
Jim
"Melissa" wrote in message
...
| 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.

return min. of range except 0
 

=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.))

Melissa wrote:

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.

--

Dave Peterson