Ranking and sorting

N

nave

Suppose I have a two columns of numbers as follows:
A B

1) 1151.1
2) 1162.9
3) 1141.5
4) 1163.7
5) 1160.0
6) 1162.9
7) 1167.1
8) 1150.3
9) 1160.5
10) 1153.5

Now I want Column C to Rank the top 7 in column B in descending orde
and drop the 11 so now i'll have a column C like below but I need A'
correspond number to go along
A C

7) 67.1
4) 63.7
2) 62.9
6) 62.9
9) 60.5
5) 60.0
10) 53.5

Please help
 
F

Frank Kabel

Hi
one way:
put the following formula in C1:
=RANK(B1,$B1:$B$999)
copy down

Now you can sort by column C (ascending)
HTH
Frank
 
N

nave

Frank thanks but it doesn't seem to work. I would like column C to hav
only best 7 numbers from B from highest to lowest and ideally droppin
the 11

make C look like this

67.1
63.7
62.9

etc. and I need Column C to be numbers not tex
 
F

Frank Kabel

Hi
o.k.
then use the following in C1:
=LARGE($B$1:$B$999,ROW())
copy down for the other 6 rows
Now you have a list of the 7 largest numbers in C1:C7

If you want the corresponding number from column A enter the following
in D1
=INDEX($A$1:$A$999,MATCH(C1,$B$1:$B$999,0))
copy down for the next 7 rows

HTH
Frank
 
N

nave

Frank,

thats a bingo my friend

but how can I get rid of the first two digits (the 11) and just leave
the next three digits

1167.1 make 67.1 ?

I need to make calculations based only on the last 3 digits and the 11
throws it off

Thank you for your time!
 
F

Frank Kabel

Hi
If all your numbers range from 1100.00 to 1199.99 just subtract 1100
from the result
But maybe I'm missing something.
another solution would be:
=VALUE(MID(C1),3,30))
or
=(C1/100-INT(C11/100))*100

HTH
Frank

[snip]
 
N

nave

Frank,

sometimes the numbers do go up to 1200 or down to 1000

this works great

thanks agai
 
F

Frank Kabel

Hi
then I'd use the following in C1:
=(LARGE($B$1:$B$999,ROW())/100)-INT(LARGE($B$1:$B$999,ROW())/100))*100


HTH
Frank
 
N

nave

ok thanks again

Frank with the =Large formula how would I write it if the numbers are
in column AN2 down to AN11 and I want the best 7 in AO2:AO8

What changes within the formula you gave me?

sorry
 
F

Frank Kabel

Hi
Frank with the =Large formula how would I write it if the numbers are
in column AN2 down to AN11 and I want the best 7 in AO2:AO8

What changes within the formula you gave me?
Click to expand...

put the following in AO2
=LARGE($AN$2:$AN$11,ROW()-1)
copy down

Frank
 
N

nave

ok thanks one more time
last question

Starting in A2 to A11 I have the numbers 1-10
In AO2 I have the =LARGE formula that you showed me for best 7

Now I want AP2-AP8 to have A's corresponding number for the best 7 i
column AO

I tried the following =index($A$2:$A$11,MATCH(AP2,$AO$2:$AO$999,0))

but of course it doesn't work. Where is my mistak
 
F

Frank Kabel

Hi
according to your previous example the numbers are in column B. Then
try the following for AP2:
=INDEX($A$1:$A$999,MATCH(AO2,$B$1:$B$999,0))
copy down
you actually made two mistakes :)
- wrong search criteria in the MATCH function (AP2 instead of AO2)
- wrong search range in the MATCH function
for more details on this have a look at the Excel helpfile on the MATCH
syntax

HTH
Frank
 
Top