A question for the Maths/Excel gurus!
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'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!
__________________________________________________ ___________________________
__________________________________________________ ___________________________
__________________________________________________ ___________________________