Finding the three smallest numbers

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

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.

Jan Kronsell wrote:
>
> 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

--

Dave Peterson

Thank you. The array formula did the trick.

Jan

"Dave Peterson" <petersod@verizonXSPAM.net> skrev i en meddelelse
news:44660B42.E2799ECE@verizonXSPAM.net...
> 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.
>
> Jan Kronsell wrote:
>>
>> 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
>
> --
>
> Dave Peterson