# Creating a weighted score

I have an audit where there are six criteria, each can be scored Excellent (E), Satisfactory (S), Needs improvement (N) or Unsatisfactory (U).

I know that if someone scores Excellent in all six areas I would like their score to be 100. I also know that if they score 6 Satisfactories, I would like them to score 95. Finally, I also know that if they score unsatusfactory in all area, I would like them to score 0.

I have tried assigning arbitray, evenly distributed, values then plotting these points {$({0,0})$, $({2,95})$, $({3, 100})$} and creating a quadratic line of best fit, using the least squares method, but the resultant equation: $f(x)=\frac{455}{6}x - \frac{85}{6}x^2$ reaches its maximum at $x=91/34$, where $f(x)=41405/408$. As the maximum score is 100 this is no good to me. I cannot simply add a constant either, as I have have the same problem at the intercept, where I would get a negative score.

Am I approaching this all wrong? Any suggestions as to how I can create a general equation for this and what values I shoudl assign to each criteria?

• I'm going to guess that the values you're suggesting are just approximate. So 6 S scores could score 94 without the universe collapsing. I think it would help to know what you think two or three other combinations should score: maybe {E,E,S,S,N,N} and {S,S,S,N,N,U}. – Joffan Jan 27 '15 at 23:21

Try the following function: $$f(x) = \frac{5}{18}\big(11x^3-106x^2+339x\big)$$

You can check that $f(0) = 0$, $f(2)=95$, and $f(3)=100$. For "Needs Improvement", the function gives $f(1) = 67.778$. Furthermore, $f$ has a local maximum at $x=3$; you can see that it levels off there nicely:

I found this by setting $f(x)=ax^3+bx^2+cx+d$ and solving for $a,b,c,d$. Your three points at $x=0$, $x=2$, and $x=3$ are not enough to determine these four constants, so based on your description of the problem I added a fourth constraint that $f(x)$ should attain a maximum at $x=3$. (Thus, using calculus, $3ax^2+2bx+c = 0$ when $x=3$.)

Finally, if you plot this against the logistic curve I suggested, you'll see that they are very similar for $x \in [0,3]$.

Edit: further details on the calculation

In looking for a simple polynomial function to fit the data, we can consider there to be four pieces of information:

• $f(0) = 0$;
• $f(2) = 95$;
• $f(3) = 100$;
• There should be a maximum at $f(3)$: in other words, the slope of the tangent line there, $f'(3)$, should equal $0$.

Since we have four pieces of information, it makes sense to look at a polynomial of degree $3$, which has four degrees of freedom. Thus $f(x) = ax^3+bx^2+cx+d$ for some $a,b,c,d$. Evaluating this function at $x=0, 2$, and $3$, we have:

\begin{align} d &= 0\\ 8a+4b+2c+d&=95\\ 27a+9b+3c+d&=100. \end{align}

For the fourth equation, we use the fact that the derivative of $x^n$ is $nx^{n-1}$, which gives $f'(x) = 3ax^2+2bx+c$. Setting this to $0$ at $x=3$, we have:

\begin{align} 27a+6b+c&=0. \end{align}

You now have four equations in four unknowns, and can use Excel, or Wolfram Alpha, etc., to solve the system.

• That is perfect. Thank you very much. My A Level maths seems a long time ago, are you able to explain more fully how you calculated this equation? – MLucas Feb 4 '15 at 8:35
• @MLucas My pleasure. I've expanded my answer—does it make more sense now? – Théophile Feb 4 '15 at 21:49

What if you just assign the following values:

$$E = {100\over6}, \quad S = {95\over6}, \quad N = {n\over6}, \quad U = 0$$

and choose $n$ as appropriate for your needs?

Also, it would help to have more qualitative information, particularly regarding the relative value of $N$ since that is the one criterion that you haven't used in a formula. Is $N$ very bad, but not terrible, in the same way that $S$ is very good, but not excellent? In other words, if someone scores $6 \times$ Needs Improvement, do they get a score of $5$? Is there such a thing as an overall passing or failing score?

• Thanks for your help. In the end I accepted an imperfect model using f(x)=(455/6)x-(85/6)x^2 where 0<=x<=2 and using a straight line with equation f(x)=5x+85 where 2<x<=3. – MLucas Jan 28 '15 at 18:07
• @MLucas Glad to help. But why are you using such a complicated function when the criteria can only take on four values? Using a continuous function the way you have will let you interpolate values (e.g., to find $f(0.5)$)—but why would you want to do that? – Théophile Jan 28 '15 at 18:30
• I work in a dynamic industry and the chances are that at some point in the future, I may have to increase the sophistication of the scoring model. Essentially this was just an exercise in future-proofing my work. – MLucas Jan 30 '15 at 14:08
• @MLucas I see. If you're looking for something cleaner, what about using, for example, a logistic curve? The function $$\frac{200}{1+e^{-1.75x}}-100$$ gives approximately the values you describe. It is somewhat more generous than your model for $f(1)$. – Théophile Jan 30 '15 at 21:41
• That's a brilliant solution, unfortunately I really need f(2) and f(3) to equal 95 and 100 respecitively. Having plugged your equation into Excel's solver, I am unable to find any values of the contants in you equation which satisfy my constraints. – MLucas Feb 2 '15 at 15:18