Weighted ranking of a series

[-Macca-]

Hi,

I'll try and explain this to the best of my abilities, but please as
questions if I don't make sense! I run navigational competitions.
What I'm attempting to do is create an "Overall Rankings" sheet of al
the competitors who have competed in our events before (starting wit
this year, but eventually back over the last 10 years).

We have 5 different styles of event (A -> E). Each competitor obviousl
gets a placing from 1st through to last (whatever that number happens t
be (Problem #1 - I can't see percentages working well for what I'
trying to do)). Not every competitor has competed in every even
(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 competitor
placings, then divided by the amount of events they competed in (usin
the *counta* function). This gave me an average placing over all th
events each competitor got. I could then use the rank.avg function t
get their rankings.

My problem was when I used a much larger data set. I copied th
formulas, and a competitor who only ever competed once (and won!) ende
up ranked on top. Not really what I was after, as a competitor who ha
done 6 events, and got 5 firsts and a second got placed after the singl
event competitor.

So, I figure I need to weight the scores so that people who hav
competed in more events get weighted slightly more than people who'v
done less events, but then to confuse it more, I need to weight th
events themselves so that Event A is worth less than Event B, which i
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 t
work properly. I'm just not sure on how to deal with blank cells (a
in a competitor hasn't competed in that event).

Maybe I'm just going about it all wrong, but if someone can give me
nudge in the right direction, I'd be most grateful! I've attached th
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
+-------------------------------------------------------------------

 

[joeu2004]

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.

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.

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".)

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 -----

 

[Ron Rosenfeld]

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.

 

[-Macca-]

;1613718']

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 tryin
to decide whether to use a Phillips or a flat-head screwdriver befor
you've even decided whether to use screws or nails, or even what you'r
trying to build.

First, figure out the math (and other criteria) for the problem you wan
to solve -- the algorithm. This should be independent of any tools tha
you might use to implement the solution, even that you would use Excel.

Gotchya. I blanked it all out last night and started from a differen
angle. I think I'm doing it wrong by using placings for scoring, when
should be using "points" for scoring.

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 reasonabl

algorithm that would rank a 1-time winner higher than a 5-time winner.
Simple arithmetic should rank the latter higher. (Not that I a
suggesting
"simple arithmetic" as a final solution.)

That's what I though too, and I think using points rather than placing
will help.

;1613718']
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 equall
weighted?

IMO, the person who's done more events (10 wins out of 10) should ran
higher than the other (5 wins out of 5), because we're trying t
encourage people to enter!

Thanks for the help. :

+-------------------------------------------------------------------
+-------------------------------------------------------------------