I am creating a formula in Excel to convert 1-5 Grading Scale to 1-100 Grading System Suppose that I have the following table:
97-100 = 1.00 94 - 96 = 1.25 91-93 = 1.50 88-90 = 1.75 85-87 = 2.00 82-84 = 2.25 79-81 = 2.50 76-78 = 2.75 75 = 3.00
My problem is this: What if the grade is 1.47, what will be the exact numerical equivalent from 91-93? And how do I compute it?
On
It looks like very decrease of 3 points on the 1-100 scale gives an increase of .25 on the 1-5 scale. For example, going from 94-96 to 91-93 (a decrease of 3) makes you go from 1.25 to 1.50 (an increase of .25).
Turning this around, an increase of .25 on the 2nd scale is a decrease of 3 on the 1st scale. Multiply by 4; an increase of 1 on the 2nd scale is a decrease of 12 on the 1st scale. Divide by 100; an increase of .01 on the second scale is a decrease of .12 on the first. Multiply by minus 3; a decrease of .03 on the 2nd scale is an increase of .36 on the 1st.
So, if you know what 1.50 on the 2nd scale corresponds to on the first, then you just add .36 to get what 1.47 on the 2nd scale corresponds to on the 1st.
On
This is an answer based more on intution. Hopefully, it will be easier to understand.
You can multiply the score in the ratio of their respective range.
So, since the ratio will be $\frac{100}{5}=20$.
Let $S_5$ and $S_{100}$ be the respective scores.
Therefore,
$$S_{100}=S_5 \times 20$$
However, I included $0$ here. For no $0$ values, we essentially have to convert a $0$ to $4$ scale to $0$ to $99$.
We can do this by
$$S_{100}=(S_5-1)\times\frac{99}{4}+1$$
Now you can just plug in the values to find whatever you like.
I'm not quite sure I understand. What it seems to me is that you have defined a function in the following way:
$$f: \{75,76,77,\dots,99,100\}\to \{1,1.25,1.5,\dots,2.75,3.0\} $$
$$f(x) = \begin{cases} 1~~~~~~~~~~~97\leq x\leq 100 \\1.25~~~~~~94\leq x\leq 96\\ 1.5~~~~~~~~91\leq x\leq 93\\ \vdots \\ 3~~~~~~~~~~~~x=75\end{cases}$$
As I understand, if you want to convert a score of $92$ from the grading scale out of 100 to the grading scale out of 5, it falls under the third case above, and $92\mapsto 1.5$.
Note a few things however: not only does $92\mapsto 1.5$, but also $93\mapsto 1.5$ as well as $91\mapsto 1.5$, a property we call "not being one-to-one". In particular it means that $f^{-1}$ will not be a function. That is to say, if we wanted to convert the number $1.5$ from the grading scale out of 5 to the grading scale out of 100, what number should it get? Should $1.5\mapsto \begin{cases}91?\\92?\\93?\end{cases}$.
Furthermore, the value of $1.47$ does not appear in the range of $f$, so $f^{-1}(1.47)$ is not even defined.
Assuming you are willing to smooth the inverse function out by extending the domain (making it everywhere defined) and provide rules such as "if result is ambiguous, always go with the higher value" (making it well defined).
An example would be
$$f_*^{-1}(y) = \begin{cases} 100~~~~~~~~~~~1\leq y< 1.25 \\96~~~~~~~~~~~~~1.25\leq y < 1.5\\ 93~~~~~~~~~~~~~1.5\leq y<1.75\\ \vdots \\ 75~~~~~~~~~~~~~~y=3\end{cases}$$
Using this $f_*^{-1}$ (which is now a function) instead of $f^{-1}$ (which was not), we have that $f_*^{-1}(1.47) = 96$
One final note, perhaps an alternative approach would be to change the original $f$ so that $f$ would be one-to-one by making it so that it would map to a continuous line instead of a staircase function.