autorank

[Hannah Madsen]

Hi
I have a column of names, with a column of values next to it that
corresponds. The column is about 100 values long.

I want a table that says
Highest Value / name
2nd Highest Value / name
3rd Highest Value / name
3rd Lowest Value / name
2nd Lowest Value / name
Lowest Value / name

I don't want to use the rank function because i don't want a seperate column
next to my data with its ranks, also because the column is always being
added to.

I just want a function that does "look in column B, calculate the ranks,
find rank x, return name
from column a", that ignores blank values.

Thanks!

Hannah

 

[Frank Kabel]

Hi
one way: put the following in your first cell
=INDEX('sheet1'!$B$1:$B$100,MATCH(LARGE('sheet1'!$A$1:$A$100,ROW(1:1)),
'sheet1'!$A$1:$A$100,0))
and copy down

Note: this only works if there are no ties in column A

 

[hannahmadsen]

Frank, thank you for your help - but it doen't seem to work, i ge
#num!

this is what i entered:
=INDEX('College Stats'!$D$1:$D$100,MATCH(LARGE('Colleg
Stats'!$A$1:$A$100,ROW(1:1)),'College Stats'!$A$1:$A$100,0))

i changed to column d because that is what i want to find out which i
highest and lowest etc.

also, how do i get it to show me the lowest - remember i want the
names from colA with the highest values in colB and 3 names with th
lowest too.

Thanks!

Hanna

 

[Frank Kabel]

Hi
if your values are in column D and your names in column A use:

=INDEX('College Stats'!$A$1:$A$100,MATCH(LARGE('College
Stats'!$D$1:$D$100,ROW(1:1)),'College Stats'!$D$1:$D$100,0))

For the lowest values replace LARGE with SMALL

 

[Hannah Madsen]

Frank, that is great and works fine except that it repeats values, despite
the names in column A being unique...
my 1st 2nd 3rd and 4th place are all the same name, as are my 5th, 6th, 7th
etc

 

[Frank Kabel]

Hi
so you have ties in your values. See:
http://www.cpearson.com/excel/rank.htm

 

[Hannah Madsen]

i've just realised what the problem is, if there are multiple entries with
the same values but different names, the cell returns just one name for all
of them
i.e. if the top three names all have a value of 20 points, it just returns
the first name three times :-(
anyway to avoid that?

 

[Hannah Madsen]

Hi Frank, whilst that article was interesting, it still assumed that you had
a column where the rank values were returned - i have too many columns that
i am trying to make a 'top five' table of to have to insert a rank next to
each column.

Is there a way of returning the top five from a list, where there are some
members with the same value, where the same name is returned for the
different values?

i don't mind there being ties, or excel caclulating 3 4th places instead of
2nd 3rd 4th,
i just want the list to be, for example:

jon
edward
hannah
mary

rather than:
jon
edward
edward
edward

(where jon got 20 and edward hannah and mary got 18)
whic is what it is returning now

 

[Hannah Madsen]

Does this function not work if there are blanks in the column?

 

[Frank Kabel]

Hi
enter the following formula as array formula (entered with
CTRL+SHIFT+ENTER):
=INDEX($B$1:$B$11,MATCH(LARGE($A$1:$A$11+ROW($A$1:$A$11)/10000,ROW(1:1)
),$A$1:$A$11+ROW($A$1:$A$11)/10000,0))

and copy down

 

[Hannah Madsen]

ok cool in the end i actually added a random number between 0.001 and 0.01
so that each value was unique!

but now i have a more interesting problem - i want to do the bottom 5 ranks,
but those that have a value. ie the bottom five names, not including the
names whose value is 0!

thanks for all your patience and help!

=INDEX(CollegeList,MATCH(SMALL(CollegeMarketingResponseYR2,ROW(1:1)),College
MarketingResponseYR2))