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