Finding the three smallest numbers

J

Jan Kronsell

Hi NG

I can find the smallest number in a range by using SMALL, fx
=SMALL(A1:A10;1) and so on, but how can I find the smallest value, that are
not 0.

If I have the following numbers, 0, 1, 2, 0, 0.5, 0, 9, 2, 0, 1

I like this result =SMALL(range;1) = 0.5
Small(range;2) = 1, Small(range;3) = 1

Jan
 
D

Dave Peterson

Are your numbers always non-negative?

If yes:
=SMALL(A1:A10,1+COUNTIF(A1:A10,0))
=SMALL(A1:A10,2+COUNTIF(A1:A10,0))
=SMALL(A1:A10,3+COUNTIF(A1:A10,0))

If you can have negative numbers:
=SMALL(IF(A1:A10<>0,A1:A10),1)
=SMALL(IF(A1:A10<>0,A1:A10),2)
....

This is an array formula. 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.

In fact, the array formula will work if the range only contains non-negative
numbers, too.
 
Top