Nicolaf
03-05-2013, 02:32 AM
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
:dunno
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
:dunno