Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting a Soccer PointsTable
Hello All,
In am in need of some help please. I am trying to sort a Soccer table. I have used the Functions of Rank and Large to sort the ranking according to Goal difference and Points for the teams. The problem is, I need to display the Teams names, instead of a number that I get from the Rank and Large functions at the end, as well as if the teams are equal with Goal difference (GD) and Points (Pts) ranking them alphabetically. If you have an easier and better idea to do the sort, please let me know. I am trying to keep the sort process as short as possible. I have found a very lengthly solution, but using Rank and Large I am able to get close to the ideal result, so I thought about asking the Experts for help, to get the final result. I have not attached the table but I will send it to who ever is willing to help. Thank you and best regards Max |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting a Soccer PointsTable
Here are two different approaches. The first one uses several formulas,
whereas the second uses only one. However, the first approach is more efficient than the second. Assumptions: A2:E6 contains the data Column A contains the Team Column D contains the Goal Difference Column E contains the Points [Option 1] F2, copied down: =D2+E2/10^2+COUNTIF($A$2:$A$6,""&A2)/10^4 G2, copied down: =IF(N(F2),RANK(F2,$F$2:$F$6),"") H1: =MAX(G2:G6) I2, copied down and across: =IF(ROWS(I$2:I2)<=$H$1,INDEX(A$2:A$6,MATCH(ROWS(I$ 2:I2),$G$2:$G$6,0)),"") [Option 2] I2, copied down and across: =INDEX(A$2:A$6,MATCH(LARGE($D$2:$D$6+$E$2:$E$6/10^2+COUNTIF($A$2:$A$6,"" &$A$2:$A$6)/10^4,ROWS(I$2:I2)),$D$2:$D$6+$E$2:$E$6/10^2+COUNTIF($A$2:$A$6 ,""&$A$2:$A$6)/10^4,0)) Note that this formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Max wrote: Hello All, In am in need of some help please. I am trying to sort a Soccer table. I have used the Functions of Rank and Large to sort the ranking according to Goal difference and Points for the teams. The problem is, I need to display the Teams names, instead of a number that I get from the Rank and Large functions at the end, as well as if the teams are equal with Goal difference (GD) and Points (Pts) ranking them alphabetically. If you have an easier and better idea to do the sort, please let me know. I am trying to keep the sort process as short as possible. I have found a very lengthly solution, but using Rank and Large I am able to get close to the ideal result, so I thought about asking the Experts for help, to get the final result. I have not attached the table but I will send it to who ever is willing to help. Thank you and best regards Max |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting a Soccer PointsTable
Hello Domenic,
How do I attach an Excel spreadsheet, I will send it to you. May be this will help. For some or other reason I am missing it with the formulas you sent me. I still get numbers, I need to be able to display the team names according to the three ranking criteria. Thank you for your help. Max "Domenic" wrote: Here are two different approaches. The first one uses several formulas, whereas the second uses only one. However, the first approach is more efficient than the second. Assumptions: A2:E6 contains the data Column A contains the Team Column D contains the Goal Difference Column E contains the Points [Option 1] F2, copied down: =D2+E2/10^2+COUNTIF($A$2:$A$6,""&A2)/10^4 G2, copied down: =IF(N(F2),RANK(F2,$F$2:$F$6),"") H1: =MAX(G2:G6) I2, copied down and across: =IF(ROWS(I$2:I2)<=$H$1,INDEX(A$2:A$6,MATCH(ROWS(I$ 2:I2),$G$2:$G$6,0)),"") [Option 2] I2, copied down and across: =INDEX(A$2:A$6,MATCH(LARGE($D$2:$D$6+$E$2:$E$6/10^2+COUNTIF($A$2:$A$6,"" &$A$2:$A$6)/10^4,ROWS(I$2:I2)),$D$2:$D$6+$E$2:$E$6/10^2+COUNTIF($A$2:$A$6 ,""&$A$2:$A$6)/10^4,0)) Note that this formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Max wrote: Hello All, In am in need of some help please. I am trying to sort a Soccer table. I have used the Functions of Rank and Large to sort the ranking according to Goal difference and Points for the teams. The problem is, I need to display the Teams names, instead of a number that I get from the Rank and Large functions at the end, as well as if the teams are equal with Goal difference (GD) and Points (Pts) ranking them alphabetically. If you have an easier and better idea to do the sort, please let me know. I am trying to keep the sort process as short as possible. I have found a very lengthly solution, but using Rank and Large I am able to get close to the ideal result, so I thought about asking the Experts for help, to get the final result. I have not attached the table but I will send it to who ever is willing to help. Thank you and best regards Max |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting a Soccer PointsTable
Did you adjust the references accordingly?
Did you confirm the formula for the second approach with CONTROL+SHIFT+ENTER, not just ENTER? In article , Max wrote: Hello Domenic, How do I attach an Excel spreadsheet, I will send it to you. May be this will help. For some or other reason I am missing it with the formulas you sent me. I still get numbers, I need to be able to display the team names according to the three ranking criteria. Thank you for your help. Max |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting a Soccer PointsTable
Hello Domenic,
You Champion! Thank you, what I did was build a table to use the same cell references that you had in your formula's and the formula's work perfectly. Now I will adapt the formula's to my tables accordingly. Well done and than you again. Best regards Max "Domenic" wrote: Did you adjust the references accordingly? Did you confirm the formula for the second approach with CONTROL+SHIFT+ENTER, not just ENTER? In article , Max wrote: Hello Domenic, How do I attach an Excel spreadsheet, I will send it to you. May be this will help. For some or other reason I am missing it with the formulas you sent me. I still get numbers, I need to be able to display the team names according to the three ranking criteria. Thank you for your help. Max |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting a Soccer PointsTable
Hello Domenic,
The formulas worked well with one exception. As I don't fully understand the formulas you used I could not get the one particular table to read correctly. Here is the incident that the formulas don't work with. The 2nd option for column "I" is the best as you said, but it sorts this case incorrectly for the 1st two places only. Column Letter === D E I Group D GD Pts Olympiacos 0 1 Roma Valencia 3 9 Valencia Roma 4 6 Olympiacos Shakhtar Donetsk 0 1 Shakhtar Donetsk According to the points Valencia should be on top then Roma and the last two are correct. Could you see if you would be able to sort it out. Thnak you and best regards Max "Max" wrote: Hello Domenic, You Champion! Thank you, what I did was build a table to use the same cell references that you had in your formula's and the formula's work perfectly. Now I will adapt the formula's to my tables accordingly. Well done and than you again. Best regards Max "Domenic" wrote: Did you adjust the references accordingly? Did you confirm the formula for the second approach with CONTROL+SHIFT+ENTER, not just ENTER? In article , Max wrote: Hello Domenic, How do I attach an Excel spreadsheet, I will send it to you. May be this will help. For some or other reason I am missing it with the formulas you sent me. I still get numbers, I need to be able to display the team names according to the three ranking criteria. Thank you for your help. Max |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting a Soccer PointsTable
That's because the solution I offered ranks first by Goal Difference and
then by Points. Based on your original post, I understood this to be the case. Although, looking at it now, it doesn't quite make sense. Anyway, since I can't quite make out which columns contain the data, here are the amendments based on my original solution... For the first approach, change the formula for F2 from... =D2+E2/10^2+COUNTIF($A$2:$A$6,""&A2)/10^4 to =E2+D2/10^2+COUNTIF($A$2:$A$6,""&A2)/10^4 For the second approach, the formula should be... =INDEX(A$2:A$6,MATCH(LARGE($E$2:$E$6+$D$2:$D$6/10^2+COUNTIF($A$2:$A$6,"" &$A$2:$A$6)/10^4,ROWS(I$2:I2)),$E$2:$E$6+$D$2:$D$6/10^2+COUNTIF($A$2:$A$6 ,""&$A$2:$A$6)/10^4,0)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Max wrote: Hello Domenic, The formulas worked well with one exception. As I don't fully understand the formulas you used I could not get the one particular table to read correctly. Here is the incident that the formulas don't work with. The 2nd option for column "I" is the best as you said, but it sorts this case incorrectly for the 1st two places only. Column Letter === D E I Group D GD Pts Olympiacos 0 1 Roma Valencia 3 9 Valencia Roma 4 6 Olympiacos Shakhtar Donetsk 0 1 Shakhtar Donetsk According to the points Valencia should be on top then Roma and the last two are correct. Could you see if you would be able to sort it out. Thnak you and best regards Max |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting a Soccer PointsTable
Hello Domenic,
I apologise for my error, I assumed the everyone understands Soccer table ranking. I am sorry, my mistake. Once again very much thank you for your help. Max "Domenic" wrote: That's because the solution I offered ranks first by Goal Difference and then by Points. Based on your original post, I understood this to be the case. Although, looking at it now, it doesn't quite make sense. Anyway, since I can't quite make out which columns contain the data, here are the amendments based on my original solution... For the first approach, change the formula for F2 from... =D2+E2/10^2+COUNTIF($A$2:$A$6,""&A2)/10^4 to =E2+D2/10^2+COUNTIF($A$2:$A$6,""&A2)/10^4 For the second approach, the formula should be... =INDEX(A$2:A$6,MATCH(LARGE($E$2:$E$6+$D$2:$D$6/10^2+COUNTIF($A$2:$A$6,"" &$A$2:$A$6)/10^4,ROWS(I$2:I2)),$E$2:$E$6+$D$2:$D$6/10^2+COUNTIF($A$2:$A$6 ,""&$A$2:$A$6)/10^4,0)) ....confirmed with CONTROL+SHIFT+ENTER. Hope this helps! In article , Max wrote: Hello Domenic, The formulas worked well with one exception. As I don't fully understand the formulas you used I could not get the one particular table to read correctly. Here is the incident that the formulas don't work with. The 2nd option for column "I" is the best as you said, but it sorts this case incorrectly for the 1st two places only. Column Letter === D E I Group D GD Pts Olympiacos 0 1 Roma Valencia 3 9 Valencia Roma 4 6 Olympiacos Shakhtar Donetsk 0 1 Shakhtar Donetsk According to the points Valencia should be on top then Roma and the last two are correct. Could you see if you would be able to sort it out. Thnak you and best regards Max |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting a Soccer PointsTable
In article ,
Max wrote: Hello Domenic, I apologise for my error, I assumed the everyone understands Soccer table ranking. I am sorry, my mistake. No problem, no need to apologize. I should have asked for clarification.. :) Once again very much thank you for your help. You're very welcome! Glad I could help! Cheers! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting a Soccer PointsTable
Can this method adapted for a third criterior " Goals for"
In soccer it is sorted by POINTS -- Goal Different --- Goal for. I used the formula below however when the teams have the sames point and goal different BUT one has a greater Goals For. The teams are tied. I used the formula below =D2+E2/10^2+COUNTIF($A$2:$A$6,""&A2)/10^4 then from the answer in column F Column C is the " Goals for" =F2+C2/10^2+COUNTIF($A$2:$A$6,""&A2)/10^4 BUT this doubles the decimal part in column in some cases. Help? On Mon, 30 Oct 2006 07:58:37 -0500, Domenic wrote: Here are two different approaches. The first one uses several formulas, whereas the second uses only one. However, the first approach is more efficient than the second. Assumptions: A2:E6 contains the data Column A contains the Team Column D contains the Goal Difference Column E contains the Points [Option 1] F2, copied down: =D2+E2/10^2+COUNTIF($A$2:$A$6,""&A2)/10^4 G2, copied down: =IF(N(F2),RANK(F2,$F$2:$F$6),"") H1: =MAX(G2:G6) I2, copied down and across: =IF(ROWS(I$2:I2)<=$H$1,INDEX(A$2:A$6,MATCH(ROWS(I $2:I2),$G$2:$G$6,0)),"") [Option 2] I2, copied down and across: =INDEX(A$2:A$6,MATCH(LARGE($D$2:$D$6+$E$2:$E$6/10^2+COUNTIF($A$2:$A$6,"" &$A$2:$A$6)/10^4,ROWS(I$2:I2)),$D$2:$D$6+$E$2:$E$6/10^2+COUNTIF($A$2:$A$6 ,""&$A$2:$A$6)/10^4,0)) Note that this formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , Max wrote: Hello All, In am in need of some help please. I am trying to sort a Soccer table. I have used the Functions of Rank and Large to sort the ranking according to Goal difference and Points for the teams. The problem is, I need to display the Teams names, instead of a number that I get from the Rank and Large functions at the end, as well as if the teams are equal with Goal difference (GD) and Points (Pts) ranking them alphabetically. If you have an easier and better idea to do the sort, please let me know. I am trying to keep the sort process as short as possible. I have found a very lengthly solution, but using Rank and Large I am able to get close to the ideal result, so I thought about asking the Experts for help, to get the final result. I have not attached the table but I will send it to who ever is willing to help. Thank you and best regards Max |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Sorting Sorting | Excel Discussion (Misc queries) | |||
sorting non contiguous ranges | Excel Discussion (Misc queries) | |||
Advice sought: Multiple column sorting | Excel Worksheet Functions | |||
SORTING question | New Users to Excel | |||
Adding a KeyID column for sorting | New Users to Excel |