Trying to simplify a scoring system

condra

Hi folks,

I'm working on a spreadsheet at the moment which has a scoring system built in. I'm struggling with simplifying the formulae, so I'm hoping someone here might have some ideas. I'm not much of a maths person unfortunately.

I'll try to explain the system as simply as possible, using a crude fruit analogy.

....................................................

Apples, bananas, peaches etc.
I want to give each fruit an overall score, based on three very different, non related criteria, eg, taste (T), availability (A), and shelf-life (S)...

Some scores are subjectively awarded, and others are empirical, but either way, I award a rating out of 10 for each of the criteria T, A and S.

The catch...
The catch, is that a zero awarded to any of the criteria, needs to nullify the overall score.

What good is a tasty apple with 3 months shelf life, if there is no availability? And what good is a tasty apple with copious supply, if shelf-life is less than an hour?

Adding or averaging...
So if I simply add the points, an overall score of 20 could be awarded for (T=0, A=10, S=10), when it should get no score, or very little.
(T=3, A=3, S=3) would actually be preferable, but only gets a score of 9 using simple adding.

Multiplying
I thought about multiplying, because once there is a zero in the equation, everything is nullified.

It's seems to be closer to what I need, but the ranges of results (1-1000) are dramatic.

The only problem is that I can see is that 10x10x1 would beat 4x4x4 (for example), when the less problematic 4x4x4 is much more ideal.
I guess you could think of it like a house of cards. I want as little weakness as possible. 10,10,10 is the ideal, but I would go with 4,4,4 any day over 10,10,1.....

Pulling my hair out.
I think I might have get used to the fact that a simple formula won't work, and I might need to create some sort of bonus system.

....................................................

Thanks in advane for any help with this!

764dak

condra wrote: »

Hi folks,

I'm working on a spreadsheet at the moment which has a scoring system built in. I'm struggling with simplifying the formulae, so I'm hoping someone here might have some ideas. I'm not much of a maths person unfortunately.

I'll try to explain the system as simply as possible, using a crude fruit analogy.

....................................................

Apples, bananas, peaches etc.
I want to give each fruit an overall score, based on three very different, non related criteria, eg, taste (T), availability (A), and shelf-life (S)...

Some scores are subjectively awarded, and others are empirical, but either way, I award a rating out of 10 for each of the criteria T, A and S.

The catch...
The catch, is that a zero awarded to any of the criteria, needs to nullify the overall score.

What good is a tasty apple with 3 months shelf life, if there is no availability? And what good is a tasty apple with copious supply, if shelf-life is less than an hour?

Adding or averaging...
So if I simply add the points, an overall score of 20 could be awarded for (T=0, A=10, S=10), when it should get no score, or very little.
(T=3, A=3, S=3) would actually be preferable, but only gets a score of 9 using simple adding.

Multiplying
I thought about multiplying, because once there is a zero in the equation, everything is nullified.

It's seems to be closer to what I need, but the ranges of results (1-1000) are dramatic.

The only problem is that I can see is that 10x10x1 would beat 4x4x4 (for example), when the less problematic 4x4x4 is much more ideal.
I guess you could think of it like a house of cards. I want as little weakness as possible. 10,10,10 is the ideal, but I would go with 4,4,4 any day over 10,10,1.....

Pulling my hair out.
I think I might have get used to the fact that a simple formula won't work, and I might need to create some sort of bonus system.

....................................................

Thanks in advane for any help with this!

I too have done various scoring systems for many things in Excel.

I have normally multiplied values since it would give precedence to stuff with less variability. For example 8x8 is bigger than 7x9 even though both 8+8 and 7+9 add up to the same number.

However, since you prefer 4x4x4 over 10x10x1 I would probably add them up and then multiply by the minimum value. So 4x(4+4+4) = 48. For 1x(1+10+10) = 21. However, if the minimum value is 3 then the answer would be 63 even though it has a greater weakness.

I just thought of a way to fix this. You would cube the minimum value and then multiply it by the sum of the values.

So Score = (min(T,S,A)^3)*(sum(T,S,A))

764dak

The numbers are kinda big though 0 to 30000.

You could divide it by 3 and then find the square root so that you would get 0 to 100 but you would get numbers with decimals.

MathsManiac

There's any number of ways you could do it; they are all pretty arbitrary.

How about adding the two biggest numbers and multiplying the answer by the smallest number?

condra

Thanks for all the replies.

Ill experiment with that method MathsManiac, and see how it goes.

I've come up with a system which seems quite fair, at least the fairest I've come up with yet. It gives an overall score out of 100, and gives a good deal of extra weight to the lowest number.

MIN x 7
+
MAX x 2
+
MEDIAN

Yakuza

How about 3 rankings of 0-10 for S, A and T, multiply them and take the cube root (to one decimal place)? You'll end up with figures in the range 0-10 without massive variation - it's pretty much what you said yourself with the additional step of getting the cube root.

condra

Thanks for the reply. Just been playing with those numbers and it's an impressive way of getting results back to reasonable ranges, but the problem of weakness not being penalized remains.

I'm stciking with giving the MIN number extra weight I think.

D-Generate

Perhaps a weighted cosine similarity between the vector (10, 10, 10) and your rating point (X, Y, Z)?


i.e. SQRT(Min(X, Y, Z))*Similarity((10, 10, 10), (X, Y, Z))

ray giraffe

Sounds like a job for the Harmonic Mean!

[latex]Score= \frac{3}{\frac{1}{A}+\frac{1}{B}+\frac{1}{C}}[/latex]

It gives more weight to the lower end of the numbers and also works if you have more than 3 inputs.

It's implemented in Excel as "HARMEAN".

HARMEAN(10,10,1)=2.5

HARMEAN(4,4,4)=4


Note:
If any of the inputs approach 0, then the harmonic mean naturally approaches zero.
Unfortunately you will have to hardcode this with a formula as follows: =IF(MIN(A,B,C)=0,0,HARMEAN(A,B,C))




If the harmonic mean is not exactly what you want you can tweak it using the "Power Mean"

[latex] Score = (\frac{A^m+B^m+C^m}{3})^\frac{1}{m}[/latex]

You choose the power m to be a fixed negative number.

The harmonic mean is m=-1.

If you want to pull your score even more towards the smallest number try m=-2

If you don't want to emphasise the smallest number so much, try m=-0.5

condra

You guys are proper wizards.
For someone like me who sucks at maths, it's absolute witchcraft.

Thanks so much for all the help.