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?
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:
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.