return min. of range except 0

[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.

[Jim Rech]

One way:

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

--
Jim
"Melissa" <[email protected]> wrote in message
news:[email protected]...
| 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]

=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