Another question relating to ranking. Does anybody know of an easy formular
to show a ranking by showing the max (#1) at the top, then the next down (#2)
under it, then the next down (#3) under that, and so on? In my example, it
would be finding the values from another worksheet in the same workbook. For
example:

Student Test Grade Rank
Meep 100% 1
Batman 98% 2
Superman 97% 3
Willy Wonka 95% 4
Veruca Salt 43% 5

So, in this example, it is getting "Test Grade" and "Student" from another
worksheet. Any formula that would be able to do this? Anything that could
pull over the top test grade to put next to rank # 1, as well as that
person's name, then the second highest test grade and the person's name next
to rank #2, and so on? (Note: My spreadsheet isn't actually using test
grades, that is just the example I'm using.)

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

Paul,

You need a column of formulas on your other worksheet (preferably the first column of your table)
that ranks the scores. Then you can extract any information using VLOOKUP formulas, linked to that
first column, along the lines of

=VLOOKUP(Row(A1),Scoring!$A$1:$J$100,COLUMN(A1),FA LSE)

Copy that down and across, and you will get the table from Scoring, cells A1:J100, sorted in order.

HTH,
Bernie
MS Excel MVP


"Paul (ESI)" wrote in message
...
Another question relating to ranking. Does anybody know of an easy formular
to show a ranking by showing the max (#1) at the top, then the next down (#2)
under it, then the next down (#3) under that, and so on? In my example, it
would be finding the values from another worksheet in the same workbook. For
example:

Student Test Grade Rank
Meep 100% 1
Batman 98% 2
Superman 97% 3
Willy Wonka 95% 4
Veruca Salt 43% 5

So, in this example, it is getting "Test Grade" and "Student" from another
worksheet. Any formula that would be able to do this? Anything that could
pull over the top test grade to put next to rank # 1, as well as that
person's name, then the second highest test grade and the person's name next
to rank #2, and so on? (Note: My spreadsheet isn't actually using test
grades, that is just the example I'm using.)

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

Paul,

Oh, I also forgot to mention that you need to ensure that you have a tie-breaking mechanism in place
for the VLOOKUP solution (in my previous post) to work.

HTH,
Bernie
MS Excel MVP


"Paul (ESI)" wrote in message
...
Another question relating to ranking. Does anybody know of an easy formular
to show a ranking by showing the max (#1) at the top, then the next down (#2)
under it, then the next down (#3) under that, and so on? In my example, it
would be finding the values from another worksheet in the same workbook. For
example:

Student Test Grade Rank
Meep 100% 1
Batman 98% 2
Superman 97% 3
Willy Wonka 95% 4
Veruca Salt 43% 5

So, in this example, it is getting "Test Grade" and "Student" from another
worksheet. Any formula that would be able to do this? Anything that could
pull over the top test grade to put next to rank # 1, as well as that
person's name, then the second highest test grade and the person's name next
to rank #2, and so on? (Note: My spreadsheet isn't actually using test
grades, that is just the example I'm using.)

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

"Bernie Deitrick" wrote:

You need a column of formulas on your other worksheet (preferably the first column of your table)
that ranks the scores. Then you can extract any information using VLOOKUP formulas, linked to that
first column, along the lines of

=VLOOKUP(Row(A1),Scoring!$A$1:$J$100,COLUMN(A1),F ALSE)

Copy that down and across, and you will get the table from Scoring, cells A1:J100, sorted in order.

I'm not sure that I understand. What kind of formula would I need to rank
the scores? That is part of my question to begin with. I'm trying to figure
out how to get it to associate each score with an appropriate rank based on
the other scores. You've told me how to get it to list the way I displayed
once I have them ranked, but I still don't understand how to assign each one
a rank to begin with. If you answered that, I don't understand.

"Bernie Deitrick" wrote:

Paul,

Oh, I also forgot to mention that you need to ensure that you have a tie-breaking mechanism in place
for the VLOOKUP solution (in my previous post) to work.

Just out curiosity, how would I do this? Oh, also, what if a tie were
allowed in some of the fields? So, for example, say several people could be
tied for number one in average test score and attendance. Would there also be
a way of doing this. Here is an example of what I'd want if that were the
case:

Student Test Grade Rank
Meep 100% 1
Batman 100% 1
Superman 100% 1
Willy Wonka 95% 4 (or 2, if that makes it easier)
Veruca Salt 43% 5 (or 3, if that makes it easier)

Also, just an interesting unrelated question open to all: I'm interested in
your opinion on a situation like the example above. When three people are
tied for number 1, does that make the next person down number 2, or number 4?
Technically, it is the second highest score, but it is the fourth ranked
person, it just so happens the first three are tied.


--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

Say your list is on Sheet1, A2:B6.

On Sheet2, starting in C2 to C6, enter:
1, 2, 3, 4, 5

Then, enter this formula in A2 of Sheet2:

=INDEX(Sheet1!$A$2:$B$6,MATCH(LARGE(Sheet1!$B$2:$B $6,$C2),Sheet1!$B$2:$B$6,0
),COLUMN(A:A))

Then copy this formula across to B2, and then select A2 and B2, and drag
down to copy both to Row 6.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Paul (ESI)" wrote in message
...
"Bernie Deitrick" wrote:

You need a column of formulas on your other worksheet (preferably the
first column of your table)
that ranks the scores. Then you can extract any information using VLOOKUP
formulas, linked to that
first column, along the lines of

=VLOOKUP(Row(A1),Scoring!$A$1:$J$100,COLUMN(A1),F ALSE)

Copy that down and across, and you will get the table from Scoring, cells
A1:J100, sorted in order.

I'm not sure that I understand. What kind of formula would I need to rank
the scores? That is part of my question to begin with. I'm trying to
figure
out how to get it to associate each score with an appropriate rank based
on
the other scores. You've told me how to get it to list the way I displayed
once I have them ranked, but I still don't understand how to assign each
one
a rank to begin with. If you answered that, I don't understand.

"Bernie Deitrick" wrote:

Paul,

Oh, I also forgot to mention that you need to ensure that you have a
tie-breaking mechanism in place
for the VLOOKUP solution (in my previous post) to work.

Just out curiosity, how would I do this? Oh, also, what if a tie were
allowed in some of the fields? So, for example, say several people could
be
tied for number one in average test score and attendance. Would there also
be
a way of doing this. Here is an example of what I'd want if that were the
case:

Student Test Grade Rank
Meep 100% 1
Batman 100% 1
Superman 100% 1
Willy Wonka 95% 4 (or 2, if that makes it easier)
Veruca Salt 43% 5 (or 3, if that makes it easier)

Also, just an interesting unrelated question open to all: I'm interested
in
your opinion on a situation like the example above. When three people are
tied for number 1, does that make the next person down number 2, or number
4?
Technically, it is the second highest score, but it is the fourth ranked
person, it just so happens the first three are tied.


--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

Awesome! Thanks, RagDyer! That worked wonderfully. Just one prolem, though.
It doesn't work if two people have the same score. In my example, I changed
it to give Batman and Willy Wonka a 95%. Since Batman came first, it just
listed him ranked twice.

Also, is there a way to change it, in a case like this, to show both of them
ranked the same if they are tied? For example, instead of one being 3 and one
being 4, they'd both be 3. I think maybe I'm making things too complicated
now, so I'll understand if this is not possible.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy


"RagDyer" wrote:

Say your list is on Sheet1, A2:B6.

On Sheet2, starting in C2 to C6, enter:
1, 2, 3, 4, 5

Then, enter this formula in A2 of Sheet2:

=INDEX(Sheet1!$A$2:$B$6,MATCH(LARGE(Sheet1!$B$2:$B $6,$C2),Sheet1!$B$2:$B$6,0
),COLUMN(A:A))

Then copy this formula across to B2, and then select A2 and B2, and drag
down to copy both to Row 6.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Paul (ESI)" wrote in message
...
"Bernie Deitrick" wrote:

You need a column of formulas on your other worksheet (preferably the
first column of your table)
that ranks the scores. Then you can extract any information using VLOOKUP
formulas, linked to that
first column, along the lines of

=VLOOKUP(Row(A1),Scoring!$A$1:$J$100,COLUMN(A1),F ALSE)

Copy that down and across, and you will get the table from Scoring, cells
A1:J100, sorted in order.

I'm not sure that I understand. What kind of formula would I need to rank
the scores? That is part of my question to begin with. I'm trying to
figure
out how to get it to associate each score with an appropriate rank based
on
the other scores. You've told me how to get it to list the way I displayed
once I have them ranked, but I still don't understand how to assign each
one
a rank to begin with. If you answered that, I don't understand.

"Bernie Deitrick" wrote:

Paul,

Oh, I also forgot to mention that you need to ensure that you have a
tie-breaking mechanism in place
for the VLOOKUP solution (in my previous post) to work.

Just out curiosity, how would I do this? Oh, also, what if a tie were
allowed in some of the fields? So, for example, say several people could
be
tied for number one in average test score and attendance. Would there also
be
a way of doing this. Here is an example of what I'd want if that were the
case:

Student Test Grade Rank
Meep 100% 1
Batman 100% 1
Superman 100% 1
Willy Wonka 95% 4 (or 2, if that makes it easier)
Veruca Salt 43% 5 (or 3, if that makes it easier)

Also, just an interesting unrelated question open to all: I'm interested
in
your opinion on a situation like the example above. When three people are
tied for number 1, does that make the next person down number 2, or number
4?
Technically, it is the second highest score, but it is the fourth ranked
person, it just so happens the first three are tied.


--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy

Try this.

Let's rank on *Sheet1*, in an adjoining column, say you use Column C.
In C2, enter this formula:

=RANK(B2,$B$2:$B$6)

And copy down to C6.

Now, on *Sheet2*, enter this formula in C2:

=SMALL(Sheet1!$C$2:$C$6,ROW(A1))

And copy down to C6.

NOW, enter this *array* formula on *Sheet2* in A2:

=INDEX(Sheet1!A$2:A$6,LARGE(IF(Sheet1!$C$2:$C$6=$C 2,ROW(Sheet1!$A$1:$A$5)),C
OUNTIF($C2:$C$6,$C2)))
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Then copy this formula across to B2, and then select A2 and B2, and drag
down to copy both to Row 6.

This should give you what you asked for.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Paul (ESI)" wrote in message
...
Awesome! Thanks, RagDyer! That worked wonderfully. Just one prolem,
though.
It doesn't work if two people have the same score. In my example, I
changed
it to give Batman and Willy Wonka a 95%. Since Batman came first, it just
listed him ranked twice.

Also, is there a way to change it, in a case like this, to show both of
them
ranked the same if they are tied? For example, instead of one being 3 and
one
being 4, they'd both be 3. I think maybe I'm making things too complicated
now, so I'll understand if this is not possible.

--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy


"RagDyer" wrote:

Say your list is on Sheet1, A2:B6.

On Sheet2, starting in C2 to C6, enter:
1, 2, 3, 4, 5

Then, enter this formula in A2 of Sheet2:


=INDEX(Sheet1!$A$2:$B$6,MATCH(LARGE(Sheet1!$B$2:$B $6,$C2),Sheet1!$B$2:$B$6,0
),COLUMN(A:A))

Then copy this formula across to B2, and then select A2 and B2, and drag
down to copy both to Row 6.
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



"Paul (ESI)" wrote in message
...
"Bernie Deitrick" wrote:

You need a column of formulas on your other worksheet (preferably the
first column of your table)
that ranks the scores. Then you can extract any information using
VLOOKUP
formulas, linked to that
first column, along the lines of

=VLOOKUP(Row(A1),Scoring!$A$1:$J$100,COLUMN(A1),F ALSE)

Copy that down and across, and you will get the table from Scoring,
cells
A1:J100, sorted in order.

I'm not sure that I understand. What kind of formula would I need to
rank
the scores? That is part of my question to begin with. I'm trying to
figure
out how to get it to associate each score with an appropriate rank
based
on
the other scores. You've told me how to get it to list the way I
displayed
once I have them ranked, but I still don't understand how to assign
each
one
a rank to begin with. If you answered that, I don't understand.

"Bernie Deitrick" wrote:

Paul,

Oh, I also forgot to mention that you need to ensure that you have a
tie-breaking mechanism in place
for the VLOOKUP solution (in my previous post) to work.

Just out curiosity, how would I do this? Oh, also, what if a tie were
allowed in some of the fields? So, for example, say several people
could
be
tied for number one in average test score and attendance. Would there
also
be
a way of doing this. Here is an example of what I'd want if that were
the
case:

Student Test Grade Rank
Meep 100% 1
Batman 100% 1
Superman 100% 1
Willy Wonka 95% 4 (or 2, if that makes it easier)
Veruca Salt 43% 5 (or 3, if that makes it easier)

Also, just an interesting unrelated question open to all: I'm
interested
in
your opinion on a situation like the example above. When three people
are
tied for number 1, does that make the next person down number 2, or
number
4?
Technically, it is the second highest score, but it is the fourth
ranked
person, it just so happens the first three are tied.


--
Have a nice day!

~Paul
Express Scripts,
Charting the future of pharmacy