Pick a # by Rank within a Range?

K

Ken

Excel 2000 ... I have a list of random numbers (event
readings) ... Criteria may be AVERAGE, SELECTED, or a
particular RANK within the RANGE.

My ? to the board ... if RANK criteria happens to be
Lowest number (MIN will do it) ... Highest number (MAX
will do it) ... But what if I am looking for the 2nd
lowest value ... or 5th lowest ... or 3rd highest etc etc.

Is there a formula that will do this?

Thanks ... Kha
 
L

L. Howard Kittle

Hi Ken,

In addition to Frank's advice if you want to return multiple values, such as
the top 3 largest numbers (or smallest) you can do this.

In A1 thru A10 enter 1 thru 10.
In C1 enter =LARGE(A1:A10,{1,2,3}) and hit Enter (Will return 10)
Now select C1, D1 and E1.
Now hit F2
Now do a CTRL + ALT + ENTER (Array Enter)
C1 = 10
D1 = 9
E1 = 8
Change the 3 to a 6 and E1 returns 5.

Remember to select all three cells when making a change and to use the Array
Enter after each change.

HTH
Regards,
Howard
 
K

Ken

A clarifier ...

I said random numbers ... which they are ... However, many
of the numbers repeat ... this is why I was unable to use
SMALL or LARGE ...

1.07
2.02
1.07
3.56
2.01
4.27

2nd lowest value is 2.01 ... SMALL(range,2) = 1.07

Thanks ... Kha
 
P

Peo Sjoblom

One way
assuming that your numbers are in A2:A7

=SMALL(IF(FREQUENCY($A$2:$A$7,$A$2:$A$7)>0,$A$2:$A$7),ROW(1:1))

copy down until you get an error
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top