Curving grades in Excel using Average and Standard Deviation

Updated spreadsheet that includes a 2nd method: bit.ly/2YxIpTn , or www.dropbox.com/s/d90grspdsw4... Here's my attempt to curve test scores using averages and standard deviations.
You can download this spreadsheet from bit.ly/2Ru5X7i-Curve , or www.dropbox.com/s/6t7edo7txpt... and give it a try.
I couldn't find another video explaining how to do this, so this is how I solved the problem. I do not go into an explanation of how a Vlookup Table works in this video; it's apparent enough if you look at the spreadsheet. Open questions are about assigning point values to failing grades as well as if I have the intervals correct.
If you want your students' grades to be higher, you could raise the average's grade to, say, 80% in the Vlookup table. What's your solution? How do you go about assigning grades when a straight percentage won't work?
Music: Big Blues Bed by Audionautix is licensed under a Creative Commons Attribution license (creativecommons.org/licenses/...)
Artist: audionautix.com/

[@VelezMpls]

Thanks for providing a link to your spreadsheet. I did have a couple of students whose grade was severely NEGATIVELY impacted, so I decided to let them keep their raw score. All the others benefited from your method. Frickin' research methods . . . LOL

[@jgoody5381]

+1 for F research methods!

[@MrLeiduowen]

Thanks a lot! You helped me solve all my grade-curving needs once and for all.

[@jgoody5381]

Thank you very much for the explanation and making the sheet available!

[@AkJunaidiPgHjSamat]

It might be too late, but a good idea would be to first search for outliers using the 1.5x the interquartile range. The outliers will be given the best or worst grade respectively, and all the others in the middle can then be calculated using your method.

[@helianthus4306]

Thank you! That was very helpful

[@seanarmstrong8460]

As a fellow teacher in China, I appreciate all of your efforts. In my opinion it really isn't fair to only use raw scores to determine grades, especially when the class content can be so different than what the students may expect. If the whole class didn't understand what was taught, or the whole class misunderstood a section on the test, they should not be penalized. That responsibility falls on the teacher to adjust the scores. So often in the Chinese school where I work, raw score is the basis for all grades. I tried to download your links mentioned in the notes, but the links don't work anymore.

[@randombytes3998]

Oh, I agree about the over-emphasis on raw scores in China. But the issue here is taking a test that's in no way related to As, Bs, and Cs, I think it's a TOEFL practice test, and converting them to As, Bs, and Cs with some degree of fairness. The links worked for me. I'll add the full url links to see if that works better.

[@abbasayubkhan]

F is already considered out! I wonder why there is F+, F & F-?

[@EmperorZelos]

No, you have each grade range being 2/3 of a standard deviation wide.

[@i_am_suedock2879]

Hi there.. thanks for sharing.. is it possible for you to share the excel file as i think you way of calculating the student results statistically in bell curve is interesting. I want to learn more on it. Thanks alot!

[@randombytes3998]

Sure, it's also pasted above. www.dropbox.com/s/6t7edo7txptsikq/Curve%20your%20grades.xlsx?dl=0 I ended up using the linear trend curve more often in my classes, but the bell curve was useful in choosing the values to input. Sometimes using the bell curve no one would get an A.

[@rahulgovindpramani5613]

How did you give the range to the grades?

[@randombytes3998]

I got those from a percentage score to GPA chart if I remember right.

[@idaho_girl]

NO NO NO. Assigning grades by forcing them to be assigned by rank-ordering them and then awarding grades so that they fit a normal distribution is wrong. This is getting the causality arrow backwards. Statistics should only be used to describe data sets, not force the data to conform to an expected result. My opinion is based on my experience as an experimental physicist and professor. There are several reasons why grading on a curve is problematic: 1) It assumes that the average performance of a class is consistent from class to class. I.e., it ignores if a particular class is high-performing or low performing. If the class is uniformly high-performing, then there will be students who have learned and mastered the material who will get a low score because it was below the average. 2) It encourages students to be adversarial towards each other and punishes students who don't cheat. Both of these are a result of how if one student does better than another, then the second student will get a lower grade. Related to this is that students are disincentivized to study together and learn from each other. 3) It does not reward mastery of the material by the students.

[@randombytes3998]

This was in China. I was "in charge" of a large program of 830 students. We were forced to give a mock IELTS exam as the final exam, even though this was an inappropriate test for their level. This was the best solution to fit raw scores of 3 to 18 into a traditional A-F scoring scheme. Perhaps it would have been better if each teacher got to decide their own system? Some then would say, 'They're such good, hardworking students: Everyone gets an A or B for effort. And some would say. "D is passing. C is average. B is very good, and A is rare, for the occasional excellent." The students did not cheat. The exams were heavily proctored. I had to come up with a methodology. The Chinese administrators would have taken the number correct divided by the number of questions, which is not how the IELTS works! 18 would have been 72%. 3 would have been 12%. After curving the grades, students who "failed" this unfair test were given an opportunity (which they took) to raise their score to 60% by completing extra work. The remaining students had their scores raised 60% + 5, 99% + 1 on a linear curve. I do not disagree with you, but I totally disagree with you. Grading on a curve was the MUCH better option for me and these students. And sometimes, completely unaligned with student growth and human potential and people's feelings, you need to measure who actually performed the best, and worst, on a standardized test. Believe me, I would have greatly preferred As and Bs for effort and Cs for the slackers. Considering what the program hoped to accomplish, English for Chinese business students in a 3rd tier university, it would have been the much better metric.