# 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}. Commented Jan 27, 2015 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? Commented Feb 4, 2015 at 8:35
• @MLucas My pleasure. I've expanded my answer—does it make more sense now? Commented Feb 4, 2015 at 21:49