I'm hoping some of STW's more numerate members can help me...
My problem is that I have a long list where each entry has a varying number of scores out of 20 ascribed to it. So, some entries have 5 scores of 18 or 19 (high = good scores), other entries have 20 scores of 4 or 5.
How would I rank the list where the value of individual scores is also taken into account (rather than just a total). I want the entries with fewer but higher scores to be weighted more favourably than those with lots of low scores.
No doubt there is some formula that can do that but it's way beyond this arts grad's brain.
Thing is you really need to qualify just HOW much you want to emphasise fewer higher scores over more lower scores.
Why not just use the average of all set scores and then rank the averages?
[s]thinking out loud, what if you were to take the square root of the average of the squares of each set?[/s]
Excel? Use a couple of columns: average and number of scores. Order first by the average score, then by the number of scores.
the square root of the average of the squares
is this the maths equivalent of getting the apprentice to ask for a pot of elbow grease and a long weight at the shop....
Going to see what happens. I may be some time.
Argh you've edited!
One problem if I average all the scores for an entry is that a low score then lowers the entry's rank more than a no score.
As Stoner says - simplest to calculate an average score. The next step is to look at variation around scores. Whatever you do you need to understand what it is and why - on that basis, I'd go for an average.
This list is in Excel (joy!) so I can try mogrim's suggestion too.
You could use a median for your average, that will reduce the weight of a single high or low score within a cell.
What do you want to do with the results you get, this will determine the best approach to a great extent.
Matt
here's some alterantives.
Results ranked by:
Sum of scores
Average of scores
Sum of squared scores
Average of Squared scores
[img] 
[/img]
Average base score ranking favours those who have consistently good scores.
Average of sum of squares emphasises those who achieve high scores, but reduces the impact of lower scores.
And if you're crap like Jamie, you'll always be bottom of the pile.
Thanks all, that's stupendously helpful and something to occupy the rest of my afternoon.
What I want is a ranked list that tells me which were the most consistently highly scored entries. A sum total doesn't take into account the number of times an entry received a score out of 20. It could have 100 scores of 1 and be higher ranked than an entry with 5 scores of 19. An average also skews it, and I don't think the median number does the job either.
Something called Spearman's Correlation has also been mentioned. Mean anything?
It could have 100 scores of 1 and be higher ranked than an entry with 5 scores of 19
not if you use sumsquares.
you could pick the top X scores from each and thank rank the averages. That would again, emphasise the high scoring over those who score lots of low scores.
Agreed, I think I like what sumsquares does, thanks Stoner.
I'm going to be untouchable at Numberwang after this.