Solved: Smallest 3 numbers in range

[Nicolaf]

Hi,

I have a range of numbers and would like to have a formula that gives me back number if it is one of 3 smallest or else gives me zero.

Numbers are in range A1:A10 and formula will be in range B1:B10.

I have done the following formula:

=IF(OR(A1=SMALL($A$1:$A$10,1),A1=SMALL($A$1:$A$10,2),A1=SMALL($A$1:$A$10,3) ),A1,"")

This formula works but it is very long and if I wanted for example 6 smallest numbers the formula would double in size.

I would like to know if there is a way to make this formula shorter!

Thanks,
Nix

[GTO]

I am utterly unsure about this, but this seems to work:

=SMALL(A1:A10,TRANSPOSE({1,2,3,4,5,6}))

...entered as an array formula.

[Teeroy]

Hi Nicolaf,

An easy way is to use a helper column (say range c1:C6), which you can hide, to identify the SMALL numbers with formula
=SMALL(A$1:A$15,ROW())
then use the following formula in B1 (and copy down)
=IF(ISERROR(MATCH(A1,$C$1:$C$6,0)),0,A1). Adjust the $C$1:$C$6 range for the number of items you want to test for.

You can also use the IFERROR function if you have excel 2007 or later (but I've only got excel 2003 at home to give a tested formula).

[mikerickson]

Try =IF(A1<=SMALL($A$1:$A$10,3), A1, "")

[Nicolaf]

It works great thanks!