Maths for a scoring system..

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!

Perhaps in addition to multiplying the three scores, you can divide by the difference between the largest and the smallest score (plus 1). Thus with 4, 4, 4 there is no spread in the scores so you would be dividing by 1 giving a total score of 64, but with 10, 10, 1 you would then be dividing by 10 giving a total score of only 10.

Hope this helps.

Pete

Could you perhaps use something like

=IF(COUNTIF(C4:E4,0)>=1,0,SUM(C4:E4))

Alf

i found the following to work quite well.
You can put in your own weights for the different standard deviations, but they will never go above 6 if you stick with weights of 1-10.

Alf, thanks for the advice but I can't follow it. What does that formula do?

Melvinrobb, likewise I cannot follow the formula in your example, though the numbers are working pretty well.
=SUM(B2:B4)-(SUM(B2:B4)*VLOOKUP(STDEV(B2:B4),$M$2:$N$8,2,TRUE))

Twiggywales..thanks for your advice. I didn't realise it was so easy to find min and max values in Excel. I'll certainly experiment with that!

Actually, this is working out pretty well.
Adding the 3 values, plus 6 times the minimum value, plus the maximum value, resulting in a score out of 100.
SCORE = "B13+C13+D13+(MIN(B13:D13)*6)+(MAX(B13:D13)"

These numbers look pretty good to me:

10 10 10 100
9 9 9 90
1 1 1 10

5 5 5 50
6 5 4 45
7 5 3 40
7 4 4 46

9 9 2 41

10 9 1 36
6 4 3 37


OK, 36 might look a bit generous for 10,9,1, but that sort of large disparity in the three figures would be very rare. I'm really happy with this.

Thanks again for all the help.

---------- Post added at 08:17 PM ---------- Previous post was at 08:13 PM ----------

Melvinrobb, I had just written that reply before you posted there.
Thanks again for taking the time to explain your method.

It's certainly more complex, but I'll play with it a bit because it makes a lot of sense.

Condra,

yours is equivalent to 7*min + 2*max + median

Pete

Yes exactly!

Seems to give pretty fair scores, considering it's a simple formula.

sure thing! thanks everyone