0

I need to adjust my data set (student grades) to fit what the school wants. I also don't want any students to fail (there are a couple who are). On the other hand, several students did a bunch of extra credit and have ended up over 100%. The university wants me to only have a handful of students with grades over 90. They also would prefer if I didn't fail anyone. Essentially, I need to tighten the statistical bell curve according to some specific constraints. Here are the requirements:

  • Constrain all values to 60 - 100
  • Only 5 values >90
  • The majority with values between 70-85

I am using Google Sheets. I can use MS Excel. I don't use R (although it's very cool). I need to know what formulas I can use to do this. I'm not a programmer nor a mathematician. I have scoured the web searching for the answer to what seems like a simple problem and not found one. I understand that it is probably because I took statistics 25 years ago and don't remember the lingo, so please be gentle...

I'm referencing a previous question here that was similar, but none of the responses answered my question. Customization of a standard Bell Curve

Thanks to anyone with any ideas on how to do this...

Sofiapw
  • 1
  • 1
    This isn't really a statistical procedure: it's just changing the grades. Your intuition likely will serve as a good guide. For instance, you could line up all current (raw) grades in decreasing order. Change the top 5 values to numbers from 100 down to 91, change the next half of the values (at least) to numbers from 85 to 70, and change the rest to values from 69 down to 60. You can see this has nothing to do with any kind of curve or "lingo:" it's merely complying with what you believe the school wants, while keeping the grades in the same relative order. – whuber Mar 25 '23 at 21:18
  • 1
    PS Let's pray you're not teaching doctors, engineers, architects, or anyone else going into a profession where poor learning can have serious consequences! – whuber Mar 25 '23 at 21:20

1 Answers1

1

Depending on the original distribution of values, this may not be a simple transformation.

My suggestion † would be to break the original values into categories (e.g. < 60, 60–64, 65–69, and so on), and then decide what value you want these to be transformed to.

It would be easy to put this algorithm into Excel.

But you might have to change it every marking period to get the final distribution you want.

† Aside from telling the university that they don't understand what student grades are supposed to mean, so they might as well just give up on the concept entirely.

Sal Mangiafico
  • 11,330
  • 2
  • 15
  • 35