A question for any Excel/Maths heads..

Status
Not open for further replies.

condra

New Member
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. :nightmare:
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!
 
Status
Not open for further replies.
Top