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:

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

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

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.

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


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

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

Thanks for the help. :)

Navigational events. Competitors get given a question sheet with different waypoints (lat/lon, UTM, distances & bearings, etc etc), and have to attempt to get to all the points we provide to answer questions.

Both of you answered with "Tennis" there - I think that's a good place to start searching.

Thanks for your help too. :)