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?

  • $\begingroup$ 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}. $\endgroup$ – 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:

a nice graph

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.

  • $\begingroup$ 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? $\endgroup$ – MLucas Feb 4 '15 at 8:35
  • $\begingroup$ @MLucas My pleasure. I've expanded my answer—does it make more sense now? $\endgroup$ – 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?

  • $\begingroup$ 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. $\endgroup$ – MLucas Jan 28 '15 at 18:07
  • $\begingroup$ @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? $\endgroup$ – Théophile Jan 28 '15 at 18:30
  • $\begingroup$ 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. $\endgroup$ – MLucas Jan 30 '15 at 14:08
  • $\begingroup$ @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)$. $\endgroup$ – Théophile Jan 30 '15 at 21:41
  • $\begingroup$ 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. $\endgroup$ – MLucas Feb 2 '15 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.