In desperate need of grading formula

[ProfO]

Freshman-year calculus 29 years ago was the last serious mathematical thinking I engaged in, so please bear with me.

My school has a new grading formula which requires not only a given grade median, but also a set grade distribution.

If I have a column of grades which range from 4.0 to 8.5, what formula will yield me a column that ranges from 4.5 to 11.0, with all the intervening grades curved proportionately?

Thanks in advance for all your help.

 

[PaddyD]

Hi - welcome to the board!

1) In a spare column, write down the bins you're after - something like:
4
4.5
5
5.5
.
.
11
2) go to tools | data anlaysis & select histogram (if you don't have thate option, first go to tools | addins & select the analysis totol pack)
3) using the wizard, select input range (i.e. your data) & bin range (i.e. the bins you set up in (1))
4) select all the output options to see what you get & hit OK

 

[ProfO]

I think that helps point me in the right direction, but still, no cigar. Perhaps another example would be better.

Let's say that when I grade 5 students, their grades are 5, 10, 25, 45, and 73. What I need is a formula to redistribute the numerical grades such that the lowest score is 0, the highest is 100, and those in between are distributed in proportion to the raw grades.

Suggestions?

Hi - welcome to the board!

1) In a spare column, write down the bins you're after - something like:
4
4.5
5
5.5
.
.
11
2) go to tools | data anlaysis & select histogram (if you don't have thate option, first go to tools | addins & select the analysis totol pack)
3) using the wizard, select input range (i.e. your data) & bin range (i.e. the bins you set up in (1))
4) select all the output options to see what you get & hit OK

 

[PaddyD]

1) review the excel help file for the percentile() function
2) construct an extra column that determines the percentile for each data element
3) use a pivot table / histogram to do the counts of data item by percentile