Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Display the max, then the next down, then the next down, etc.
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
"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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
Wow! That did it exactly! Thank you so very much! I am somewhat shocked. What
I was asking sounded way too complicated to me. I can't believe it was actually do-able! Thank you! -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy "RagDyer" wrote: 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 |
#9
|
|||
|
|||
Thanks for the feed-back.
-- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Paul (ESI)" wrote in message ... Wow! That did it exactly! Thank you so very much! I am somewhat shocked. What I was asking sounded way too complicated to me. I can't believe it was actually do-able! Thank you! -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy "RagDyer" wrote: 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 |
#10
|
|||
|
|||
You know, while I'm thinking of it, can anybody (I guess, particularly
RagDyeR, since you created it) sort of disect and explain these codes to me so that I can understand what they do. I tried myself, but I'm not familiar with several of the functions, and the Office Assistant didn't prove very helpful. For example: =SMALL(Sheet1!$C$2:$C$6,ROW(A1)) What does "ROW" do and what is "A1" there for? Also: =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))) For this one, I think I'd benefit from an explanation of the whole thing. Also, RagDyeR told me I'd need to press Control+Shift+Enter instead of just enter for this formula because it is an array formula and needs curly brackets. I was wondering what the difference is if I don't use the curly brackets. Why wouldn't it work? I know it doesn't work right. I made the mistake of forgetting this, and JUST pressing enter after adding another person and updating the formula, and it did not work. Why, though, does it need curly brackets? -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy "RagDyeR" wrote: Thanks for the feed-back. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Paul (ESI)" wrote in message ... Wow! That did it exactly! Thank you so very much! I am somewhat shocked. What I was asking sounded way too complicated to me. I can't believe it was actually do-able! Thank you! -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy "RagDyer" wrote: 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 |
#11
|
|||
|
|||
I spent some time studying this myself, and I think I can explain it to you.
Though I could not have come up with it on my own. :) The SMALL function, once you copy it down, simply sorts the rankings from lowest to highest. See the help for the SMALL function. The ROW function call (A1 in the first cell) returns "1" (which is the row containing the cell A1). This tells SMALL to find the 1st lowest value. In the next row, the ROW(A2) will give you the second lowest rank, and so on down the line. In this example, this column of ranks is the range C2:C6 on sheet 2 The ROW function is a fancy way to get the numbers 1 thru 5 as you copy the formula down. The IF/ROW functions will return an array to feed into LARGE. As array functions they will cycle through the whole range (C2:C6) looking for the value in C2. For those rows that match C2, it will return the line number in the table (not the row number on the worksheet). With this data: Student Test Grade Rank Meep 100% 1 Batman 90% 4 Superman 95% 2 Willy Wonka 95% 2 Veruca Salt 43% 5 If you put the whole INDEX formula in cell A2 on sheet 2, the IF/ROW call will return this array (internally): 1 false false false false Since "false" evalutes to zero, the "1" (which means the first row of data) is the largest value in this array. The COUNTIF will count only one occurance of rank = 1. The LARGE function will find the largest value in the array - which is 1 - and pass this to the INDEX function, which will pick off the name from the first row of data. When you copy this formula to cell B2 on sheet 2, it will do the same work and pick off the corresponding score. So far, so good? When you copy the INDEX formula to cell C3 on sheet 2, you are now looking for people and scores for rank = 2. The IF/ROW functions return the following array: false false 3 4 false and COUNTIF reports there are 2 occurrances of rank =2. LARGE will pick off the 2nd largest (from COUNTIF) and return the 3 to the INDEX function, picking off the 3rd row of data. When you copy to cell C4 on sheet 2, you still want rank = 2 (since there is a tie for 2nd place). IF/ROW returns: false false 3 4 false BUT - the range for COUNTIF changes and ignores the rows above the current row, so COUNTIF returns 1. LARGE picks off the 1st largest value, and passes 4 to the INDEX function, picking off row 4 of data. A lot of this is done internally due to the way Excel handles array functions, so you won't see most of this at work. When there are ties in rank, this will keep the tied people in the same relative order as they were in the original list. Hope this helps. "Paul (ESI)" wrote in message ... You know, while I'm thinking of it, can anybody (I guess, particularly RagDyeR, since you created it) sort of disect and explain these codes to me so that I can understand what they do. I tried myself, but I'm not familiar with several of the functions, and the Office Assistant didn't prove very helpful. For example: =SMALL(Sheet1!$C$2:$C$6,ROW(A1)) What does "ROW" do and what is "A1" there for? Also: =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))) For this one, I think I'd benefit from an explanation of the whole thing. Also, RagDyeR told me I'd need to press Control+Shift+Enter instead of just enter for this formula because it is an array formula and needs curly brackets. I was wondering what the difference is if I don't use the curly brackets. Why wouldn't it work? I know it doesn't work right. I made the mistake of forgetting this, and JUST pressing enter after adding another person and updating the formula, and it did not work. Why, though, does it need curly brackets? -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy "RagDyeR" wrote: Thanks for the feed-back. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Paul (ESI)" wrote in message ... Wow! That did it exactly! Thank you so very much! I am somewhat shocked. What I was asking sounded way too complicated to me. I can't believe it was actually do-able! Thank you! -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy "RagDyer" wrote: 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 |
#12
|
|||
|
|||
I just had to fix a couple of cell references. Sorry.
****************************************** I spent some time studying this myself, and I think I can explain it to you. Though I could not have come up with it on my own. :) The SMALL function, once you copy it down, simply sorts the rankings from lowest to highest. See the help for the SMALL function. The ROW function call (A1 in the first cell) returns "1" (which is the row containing the cell A1). This tells SMALL to find the 1st lowest value. In the next row, the ROW(A2) will give you the second lowest rank, and so on down the line. In this example, this column of ranks is the range C2:C6 on sheet 2 The ROW function is a fancy way to get the numbers 1 thru 5 as you copy the formula down. The IF/ROW functions will return an array to feed into LARGE. As array functions they will cycle through the whole range (C2:C6) looking for the value in C2. For those rows that match C2, it will return the line number in the table (not the row number on the worksheet). With this data: Student Test Grade Rank Meep 100% 1 Batman 90% 4 Superman 95% 2 Willy Wonka 95% 2 Veruca Salt 43% 5 If you put the whole INDEX formula in cell A2 on sheet 2, the IF/ROW call will return this array (internally): 1 false false false false Since "false" evalutes to zero, the "1" (which means the first row of data) is the largest value in this array. The COUNTIF will count only one occurance of rank = 1. The LARGE function will find the largest value in the array - which is 1 - and pass this to the INDEX function, which will pick off the name from the first row of data. When you copy this formula to cell B2 on sheet 2, it will do the same work and pick off the corresponding score. So far, so good? When you copy the INDEX formula to cell A3 on sheet 2, you are now looking for people and scores for rank = 2. The IF/ROW functions return the following array: false false 3 4 false and COUNTIF reports there are 2 occurrances of rank =2. LARGE will pick off the 2nd largest (from COUNTIF) and return the 3 to the INDEX function, picking off the 3rd row of data. When you copy to cell A4 on sheet 2, you still want rank = 2 (since there is a tie for 2nd place). IF/ROW returns: false false 3 4 false BUT - the range for COUNTIF changes and ignores the rows above the current row, so COUNTIF returns 1. LARGE picks off the 1st largest value, and passes 4 to the INDEX function, picking off row 4 of data. A lot of this is done internally due to the way Excel handles array functions, so you won't see most of this at work. When there are ties in rank, this will keep the tied people in the same relative order as they were in the original list. Hope this helps. |
#13
|
|||
|
|||
Awesome! Thanks, I think I've got it now. You have been a big help.
-- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy "Lewis Clark" wrote: I just had to fix a couple of cell references. Sorry. ****************************************** I spent some time studying this myself, and I think I can explain it to you. Though I could not have come up with it on my own. :) The SMALL function, once you copy it down, simply sorts the rankings from lowest to highest. See the help for the SMALL function. The ROW function call (A1 in the first cell) returns "1" (which is the row containing the cell A1). This tells SMALL to find the 1st lowest value. In the next row, the ROW(A2) will give you the second lowest rank, and so on down the line. In this example, this column of ranks is the range C2:C6 on sheet 2 The ROW function is a fancy way to get the numbers 1 thru 5 as you copy the formula down. The IF/ROW functions will return an array to feed into LARGE. As array functions they will cycle through the whole range (C2:C6) looking for the value in C2. For those rows that match C2, it will return the line number in the table (not the row number on the worksheet). With this data: Student Test Grade Rank Meep 100% 1 Batman 90% 4 Superman 95% 2 Willy Wonka 95% 2 Veruca Salt 43% 5 If you put the whole INDEX formula in cell A2 on sheet 2, the IF/ROW call will return this array (internally): 1 false false false false Since "false" evalutes to zero, the "1" (which means the first row of data) is the largest value in this array. The COUNTIF will count only one occurance of rank = 1. The LARGE function will find the largest value in the array - which is 1 - and pass this to the INDEX function, which will pick off the name from the first row of data. When you copy this formula to cell B2 on sheet 2, it will do the same work and pick off the corresponding score. So far, so good? When you copy the INDEX formula to cell A3 on sheet 2, you are now looking for people and scores for rank = 2. The IF/ROW functions return the following array: false false 3 4 false and COUNTIF reports there are 2 occurrances of rank =2. LARGE will pick off the 2nd largest (from COUNTIF) and return the 3 to the INDEX function, picking off the 3rd row of data. When you copy to cell A4 on sheet 2, you still want rank = 2 (since there is a tie for 2nd place). IF/ROW returns: false false 3 4 false BUT - the range for COUNTIF changes and ignores the rows above the current row, so COUNTIF returns 1. LARGE picks off the 1st largest value, and passes 4 to the INDEX function, picking off row 4 of data. A lot of this is done internally due to the way Excel handles array functions, so you won't see most of this at work. When there are ties in rank, this will keep the tied people in the same relative order as they were in the original list. Hope this helps. |
#14
|
|||
|
|||
As Lewis mentioned at the end of his explanation, ties are returned in their
respective order as entered in the original list. If for some reason, you might prefer to have them (ties) listed in the reverse order of their position in the original list, simply change the Large() function to Small(). -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Paul (ESI)" wrote in message ... Awesome! Thanks, I think I've got it now. You have been a big help. -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy "Lewis Clark" wrote: I just had to fix a couple of cell references. Sorry. ****************************************** I spent some time studying this myself, and I think I can explain it to you. Though I could not have come up with it on my own. :) The SMALL function, once you copy it down, simply sorts the rankings from lowest to highest. See the help for the SMALL function. The ROW function call (A1 in the first cell) returns "1" (which is the row containing the cell A1). This tells SMALL to find the 1st lowest value. In the next row, the ROW(A2) will give you the second lowest rank, and so on down the line. In this example, this column of ranks is the range C2:C6 on sheet 2 The ROW function is a fancy way to get the numbers 1 thru 5 as you copy the formula down. The IF/ROW functions will return an array to feed into LARGE. As array functions they will cycle through the whole range (C2:C6) looking for the value in C2. For those rows that match C2, it will return the line number in the table (not the row number on the worksheet). With this data: Student Test Grade Rank Meep 100% 1 Batman 90% 4 Superman 95% 2 Willy Wonka 95% 2 Veruca Salt 43% 5 If you put the whole INDEX formula in cell A2 on sheet 2, the IF/ROW call will return this array (internally): 1 false false false false Since "false" evalutes to zero, the "1" (which means the first row of data) is the largest value in this array. The COUNTIF will count only one occurance of rank = 1. The LARGE function will find the largest value in the array - which is 1 - and pass this to the INDEX function, which will pick off the name from the first row of data. When you copy this formula to cell B2 on sheet 2, it will do the same work and pick off the corresponding score. So far, so good? When you copy the INDEX formula to cell A3 on sheet 2, you are now looking for people and scores for rank = 2. The IF/ROW functions return the following array: false false 3 4 false and COUNTIF reports there are 2 occurrances of rank =2. LARGE will pick off the 2nd largest (from COUNTIF) and return the 3 to the INDEX function, picking off the 3rd row of data. When you copy to cell A4 on sheet 2, you still want rank = 2 (since there is a tie for 2nd place). IF/ROW returns: false false 3 4 false BUT - the range for COUNTIF changes and ignores the rows above the current row, so COUNTIF returns 1. LARGE picks off the 1st largest value, and passes 4 to the INDEX function, picking off row 4 of data. A lot of this is done internally due to the way Excel handles array functions, so you won't see most of this at work. When there are ties in rank, this will keep the tied people in the same relative order as they were in the original list. Hope this helps. |
#15
|
|||
|
|||
Cool. Thanks for yet another good tip. For what we are creating now, it
should work just fine the way you first gave it to me. However, this actually may come in handy for future reference. Thanks! -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy "RagDyer" wrote: As Lewis mentioned at the end of his explanation, ties are returned in their respective order as entered in the original list. If for some reason, you might prefer to have them (ties) listed in the reverse order of their position in the original list, simply change the Large() function to Small(). -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Paul (ESI)" wrote in message ... Awesome! Thanks, I think I've got it now. You have been a big help. -- Have a nice day! ~Paul Express Scripts, Charting the future of pharmacy "Lewis Clark" wrote: I just had to fix a couple of cell references. Sorry. ****************************************** I spent some time studying this myself, and I think I can explain it to you. Though I could not have come up with it on my own. :) The SMALL function, once you copy it down, simply sorts the rankings from lowest to highest. See the help for the SMALL function. The ROW function call (A1 in the first cell) returns "1" (which is the row containing the cell A1). This tells SMALL to find the 1st lowest value. In the next row, the ROW(A2) will give you the second lowest rank, and so on down the line. In this example, this column of ranks is the range C2:C6 on sheet 2 The ROW function is a fancy way to get the numbers 1 thru 5 as you copy the formula down. The IF/ROW functions will return an array to feed into LARGE. As array functions they will cycle through the whole range (C2:C6) looking for the value in C2. For those rows that match C2, it will return the line number in the table (not the row number on the worksheet). With this data: Student Test Grade Rank Meep 100% 1 Batman 90% 4 Superman 95% 2 Willy Wonka 95% 2 Veruca Salt 43% 5 If you put the whole INDEX formula in cell A2 on sheet 2, the IF/ROW call will return this array (internally): 1 false false false false Since "false" evalutes to zero, the "1" (which means the first row of data) is the largest value in this array. The COUNTIF will count only one occurance of rank = 1. The LARGE function will find the largest value in the array - which is 1 - and pass this to the INDEX function, which will pick off the name from the first row of data. When you copy this formula to cell B2 on sheet 2, it will do the same work and pick off the corresponding score. So far, so good? When you copy the INDEX formula to cell A3 on sheet 2, you are now looking for people and scores for rank = 2. The IF/ROW functions return the following array: false false 3 4 false and COUNTIF reports there are 2 occurrances of rank =2. LARGE will pick off the 2nd largest (from COUNTIF) and return the 3 to the INDEX function, picking off the 3rd row of data. When you copy to cell A4 on sheet 2, you still want rank = 2 (since there is a tie for 2nd place). IF/ROW returns: false false 3 4 false BUT - the range for COUNTIF changes and ignores the rows above the current row, so COUNTIF returns 1. LARGE picks off the 1st largest value, and passes 4 to the INDEX function, picking off row 4 of data. A lot of this is done internally due to the way Excel handles array functions, so you won't see most of this at work. When there are ties in rank, this will keep the tied people in the same relative order as they were in the original list. Hope this helps. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you display Greek and Russian languages in excel? | Excel Discussion (Misc queries) | |||
large text amount in cell will not display in the cell | Excel Discussion (Misc queries) | |||
Conditional display of a .jpeg file? | Excel Discussion (Misc queries) | |||
Data Filter - Not all rows in spreadsheet will display in Autofilt | Excel Worksheet Functions | |||
Combine & Display “Fixed” & “Automatically Updated” Date Parts | Excel Worksheet Functions |