0

I apologize in advance for posting a question that may be very basic math. Please bare with me .

I have an online questionnaire with hundreds of multiple choice questions where my students get 1 hour to solve as many as they can (it is technically impossible to solve all).

I collect in a spreadsheet the number of correct answers each student was capable of answering during the 1 hour time session:

Student   | Correct Answers | Score
----------+-----------------+-------
Student A |       130       |  100
Student B |        70       |
Student C |        30       |
Student D |        50       |
Student E |        60       |
Student F |        20       |
Student G |        30       |
Student H |        40       |
Student I |        30       |
Student J |        30       |
Student K |        20       |    0

What I would like to do is to grade each student relative to the highest and lowest scores in their specific group so that the student who correctly answered 130 questions will get 100 and the student who answered only 20 will get zero.

I am trying to come up with the right excel formula to do the job but i'm lacking the mathematical understanding of how to calculate this as a function.

I guess my question is if someone can explain in laymen terms the mathematical logic to achieve this kind of task.

Thank you,

Elad Ratson
  • 103
  • I have solved a very similar problem here (read my answer): https://math.stackexchange.com/questions/3355027/a-function-or-a-factor-to-scale-a-list-of-real-numbers-from-one-range-to-another/3355071#3355071 – Matti P. Nov 26 '20 at 11:22
  • Thank you very much Matti, so... if I understand your solution correctly, in my case i should set f(min)=0 and f(max)=100? – Elad Ratson Nov 26 '20 at 11:27
  • Yes, you have understood it correctly :) – Matti P. Nov 26 '20 at 11:30

1 Answers1

1

So essentially you have a function which maps the min value $(20)$ to $0$ and max value $(130)$ to $100$. This function needs to be strictly increasing in your original score

There is no one answer to this question, and it depends on which gives you results that you are happy with. Some easy choices are

  1. Linear - Just use the function

$$\text{Adjusted Score} = \frac{\text{Actual Score} - 20}{130-20} \times 100$$

This is an easy conversion, and is linear - it is equally sensitive at each score to change

  1. Logarithmic - Using the function

$$\text{Adjusted Score} = 100\log_{10}\left(1 + 9\times\frac{\text{Actual Score} - 20}{130-20}\right) $$

This is more involved, but doesn't differentiate scores at the higher end very much - even if they have 5 points different in their actual score, they might have very small difference in their adjusted scores

  1. Exponential - You could fit an exponential curve to not differentiate lower scores but instead increase differentiation at higher scores

It's really up to you, and what you see fit. Play around with different functions and see which scheme best highlights the point you want to get across to your students with their grades

Dhanvi Sreenivasan
  • 5,637
  • Thank you Dhanvi, for this really easy to understand explantation. Not only I was able to successfully represent both the linear and logarithmic methods in Google sheets, I also understand the mathematical logic which is both cool and helpful. – Elad Ratson Nov 26 '20 at 13:10
  • Only thing I did not understand is, in the logarithmique method, what does the 1+9 values represent? – Elad Ratson Nov 26 '20 at 13:11
  • So essentially I am trying to map from $\log 1 = 0$ to $\log 10 = 1$ linearly, so am using $\ln(1+x)$ but a scaled version.. it's probably a roundabout way of doing it, but it was the first thing that popped into my head – Dhanvi Sreenivasan Nov 27 '20 at 06:23