Can someone help me please? I would like these players ranked first on points, then game difference then won. The reason there are all zeros is I would like to put in the formula once and drag and the rank to update itself when I change the values in cells C4 to H11. I would also like the formula to take ties into account and correctly rank positive and negative numbers.
Please update your sample workbook. The data portion columns C:J should have a complete set of sample data, then manually mockup the results in column A based on the sample data.
The sample sbould fully demonstrate all the hurdles you need to overcome regarding scoring and breaking ties.
Once your sample chart is complete and full of data, we have a goal in front of us for automating a formula for column A.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
Also - how do you determine tie breakers?
That's the question we have to ask YOU.
Excel by default assumes ties are just fine. If you rank two people in 3rd places with the same score, Excel simply excludes 4th place so 5th would be next. That's perfectly reasonable and often used.
If you do not allow ties, then you have to determine the criteria for breaking the tie.
1) Easiest solution is positional. The list of players is in some sort of seniority so that people near the top of the list always place above others below in the list in the case of ties.
2) Secondary column of values used to break ties. If the secondary column also ties, then a third?
You have have to determine the logic, we'll help turn that into a formula or three. That's why we need you to mockup a complete set of data demonstrating all of the issues you face and the result you want based on that data when you manually calculate the rankings.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the
Always put your code between code tags. [CODE] your code here [/CODE]
“None of us is as good as all of us” - Ray Kroc
“Actually, I *am* a rocket scientist.” - JB (little ones count!)
This is the new spreadsheet in reply to the above replies.
If you use this formula in A4 copied down it should rank the way you asked
=RANK(J4,J$4:J$11)+SUMPRODUCT((J4=J$4:J$11)*(I4<I$4:I$11))+SUMPRODUCT((J4=J$4:J$11)*(I4=I$4:I$11)*(G 4<G$4:G$11))
If all three are the same - points, game difference and won - then the rank will be the same
Audere est facere
This solves my problem but what can I do to stop ranking negative numbers wrongly e.g. if I update the formula myself two players may be on the same points but game difference when one player has -20 and the other player has -6 then -20 will be ranked higher.
I think that the suggested formula works OK with negative numbers - it will rank -6 higher than -20, isn't that what you expect? If you still think there's a problem can you post a workbook with the incorrect ranking?
Audere est facere
Hi okopo -
Have you ever seen Auto Filters work?
Put your cursor anywhere in your table, like in B5, click on the Data Tab and then click "Filter".
Little dropdown arrows appear on your red row. Click on anyone of these and it will give you choices to sort or filter by that columns data.
If you click on the "Points" arrow and sort Largest to smallest you it will sort your league without needing to rank them. If you sort by Game Differences followed by Points it will effectively be what you want.
Try It... You might like this better than your ranking formula.
Thank you all for the help that I received. I have now solved my problem.
How to I put a solved thread on my posts?
Click on Forum Rules (above) and read Rule #9.
I used this formula =RANK(J4,J$4:J$11)+SUMPRODUCT((J4=J$4:J$11)*(I4<I$4:I$11))+SUMPRODUCT((J4=J$4:J$11)*(I4=I$4:I$11)*(G 4<G$4:G$11)) to rank the players in my second attachment but I would like continuous ranks e.g. 1,2,3,4,5 all help will be appreciated.
Can you show an example where that formula doesn't give you the results you want - and also show the results you do want? Thanks
Audere est facere
Can someone help me please? I would like these players ranked first on points, then game difference then won. The reason there are all zeros is I would like to put in the formula once and drag and the rank to update itself when I change the values in cells C4 to H11. I would also like the formula to take ties into account and correctly rank positive and negative numbers. I would like continuous ranks e.g. 1,2,3,4,5. I used the formula
=RANK(J4,J$4:J$11)+SUMPRODUCT((J4=J$4:J$11)*(I4<I$4:I$11))+SUMPRODUCT((J4=J$4:J$11)*(I4=I$4:I$11)*(G 4<G$4:G$11))
but most of the ranks are the same,
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks