Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Weighted ranking of a series
Hi,
I'll try and explain this to the best of my abilities, but please ask questions if I don't make sense! I run navigational competitions. What I'm attempting to do is create an "Overall Rankings" sheet of all the competitors who have competed in our events before (starting with this year, but eventually back over the last 10 years). We have 5 different styles of event (A - E). Each competitor obviously gets a placing from 1st through to last (whatever that number happens to be (Problem #1 - I can't see percentages working well for what I'm trying to do)). Not every competitor has competed in every event (Problem #2 - throws averages around a bit). My first attempt was using the data below: Code:
E1 E2 E3 E4 E5 E6 Shiny 6 2 3 2 4 3 Kim 2 3 2 4 3 4 Tom 3 4 6 6 2 2 Jack 4 6 4 3 5 5 Barry 5 5 5 6 6 Nic 1 1 1 1 1 1 My problem was when I used a much larger data set. I copied the formulas, and a competitor who only ever competed once (and won!) ended up ranked on top. Not really what I was after, as a competitor who has done 6 events, and got 5 firsts and a second got placed after the single event competitor. So, I figure I need to weight the scores so that people who have competed in more events get weighted slightly more than people who've done less events, but then to confuse it more, I need to weight the events themselves so that Event A is worth less than Event B, which is worth less than Event C etc etc (as they're different difficulties). I've read up on the SumProduct function, but I can't seem to get it to work properly. I'm just not sure on how to deal with blank cells (as in a competitor hasn't competed in that event). Maybe I'm just going about it all wrong, but if someone can give me a nudge in the right direction, I'd be most grateful! I've attached the spreadsheet I'm playing with so if you're inclined you can have a look. Thanks --Macca :) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted ranking of a series
"-Macca-" wrote:
Maybe I'm just going about it all wrong, but if someone can give me a nudge in the right direction, I'd be most grateful! I probably do not have time to help you at the moment. But I do want to offer some "observations" in the spirit of giving you a "nudge in the right direction" -- or at least avoiding wrong directions. "-Macca-" wrote: I've read up on the SumProduct function, but I can't seem to get it to work properly. I'm just not sure on how to deal with blank cells I think you're putting the cart before the horse. This is like trying to decide whether to use a Phillips or a flat-head screwdriver before you've even decided whether to use screws or nails, or even what you're trying to build. First, figure out the math (and other criteria) for the problem you want to solve -- the algorithm. This should be independent of any tools that you might use to implement the solution, even that you would use Excel. "-Macca-" wrote: a competitor who only ever competed once (and won!) ended up ranked on top. Not really what I was after, as a competitor who has done 6 events, and got 5 firsts and a second got placed after the single event competitor. Obviously you did something very wrong. I cannot imagine any reasonable algorithm that would rank a 1-time winner higher than a 5-time winner. Simple arithmetic should rank the latter higher. (Not that I am suggesting "simple arithmetic" as a final solution.) More to the point, for example, who should rank higher: person A who places 2nd in 3 of 5 events, or a person B who places 1st in 1 of the same 5 events? I suspect there are "standard" formulas or algorithms for this kind of thing. Off-hand, I cannot think of how we might search for them, much less what the algorithms might look like. (On second thought, I wonder if you can look at how tennis players are "seeded".) "-Macca-" wrote: So, I figure I need to weight the scores so that people who have competed in more events get weighted slightly more than people who've done less events While I agree that the number of events competed in should be a factor, I don't think it should necessarily weight one person's results higher than another's. Who should rank higher: person C who places 1st in 1 of 10 events, or person D who places 1st in 1 of 5 events, all events being equally weighted? Arguably, person D is better since he/she had fewer chances to win. Gotta go! Good luck with this. It certainly sounds like an interesting and challenging problem, independent of Excel usage. ----- original message ----- "-Macca-" wrote: Hi, I'll try and explain this to the best of my abilities, but please ask questions if I don't make sense! I run navigational competitions. What I'm attempting to do is create an "Overall Rankings" sheet of all the competitors who have competed in our events before (starting with this year, but eventually back over the last 10 years). We have 5 different styles of event (A - E). Each competitor obviously gets a placing from 1st through to last (whatever that number happens to be (Problem #1 - I can't see percentages working well for what I'm trying to do)). Not every competitor has competed in every event (Problem #2 - throws averages around a bit). My first attempt was using the data below: Code: -------------------- E1 E2 E3 E4 E5 E6 Shiny 6 2 3 2 4 3 Kim 2 3 2 4 3 4 Tom 3 4 6 6 2 2 Jack 4 6 4 3 5 5 Barry 5 5 5 6 6 Nic 1 1 1 1 1 1 -------------------- Note Barry didn't compete in E4. So, I added up each competitors placings, then divided by the amount of events they competed in (using the *counta* function). This gave me an average placing over all the events each competitor got. I could then use the rank.avg function to get their rankings. My problem was when I used a much larger data set. I copied the formulas, and a competitor who only ever competed once (and won!) ended up ranked on top. Not really what I was after, as a competitor who has done 6 events, and got 5 firsts and a second got placed after the single event competitor. So, I figure I need to weight the scores so that people who have competed in more events get weighted slightly more than people who've done less events, but then to confuse it more, I need to weight the events themselves so that Event A is worth less than Event B, which is worth less than Event C etc etc (as they're different difficulties). I've read up on the SumProduct function, but I can't seem to get it to work properly. I'm just not sure on how to deal with blank cells (as in a competitor hasn't competed in that event). Maybe I'm just going about it all wrong, but if someone can give me a nudge in the right direction, I'd be most grateful! I've attached the spreadsheet I'm playing with so if you're inclined you can have a look. Thanks --Macca :) +-------------------------------------------------------------------+ |Filename: Ranking System.zip | |Download: http://www.excelbanter.com/attachment.php?attachmentid=908| +-------------------------------------------------------------------+ -- -Macca- |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Weighted ranking of a series
On Mon, 2 Sep 2013 13:25:13 +0100, -Macca- wrote:
So, I figure I need to weight the scores so that people who have competed in more events get weighted slightly more than people who've done less events, but then to confuse it more, I need to weight the events themselves so that Event A is worth less than Event B, which is worth less than Event C etc etc (as they're different difficulties). What kind of events are these? Has someone already invented this wheel? There are various types of competitions, with events of varying difficulty, and where any given player will not compete in all of the events, for which ranking systems have already been divised. I would search the Web for information regarding, for example Bridge Player ratings Tennis Billiards or whatever else is similar to the ypes of competition your are trying to rank. |
#4
|
|||
|
|||
Quote:
Quote:
Quote:
Thanks for the help. :) |
#5
|
|||
|
|||
Quote:
Quote:
Thanks for your help too. :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to assign a ranking number to a series of values. | Excel Worksheet Functions | |||
Ranking weighted scores with partial results | Excel Worksheet Functions | |||
Ranking columns of data in a series of matrices | Excel Programming | |||
series graph -- one series being added to another series | Charts and Charting in Excel | |||
Filling in a Date Series using the Fill | Series menu command | Excel Programming |