Hi,
I have a workbook with a bunch of grades and a bunch of graders.
I have 120 students who each wrote a paper and each paper was graded by 3
editors but not all editors read all papers.
To make the results fair, I want to use the average and stddev of each
grader's grades and apply it to create an "adjusted score" for each grade.
Currently, the data is arranged such that it is:

Student Editor Name1 Score Editor Name2 Score Editor Name3
Score
A John 20 Jane 25
Joe 22
B Betsy 21 John 29
Bob 23

And so on for a total of 122 students.

I know how to do a countifs to find the number of grades each editor entered
but I'm totally struggling to figure out a way to calculate the mean and
standard deviation of all the grades for each editor.

Any ideas?

Hi elf4278

Your problem is not a complicated one per se, the problem is the way
your data is ordered. If the data is in the form you say it is in I
would do the following;

Make a copy of the sheet you are working on. Then Copy Student,
Editor2 and Score(2) and paste this data at the base of the Student
column A, repeat this step for Editor(3) and score(3) pasting at the
base of your student column. Remove the unwanted columns – D through
to G. Sort the data by student, Col A.

Now you are in business. Your data should look like this

Student Editor Name Score
Student A John 20
Student A Jane 25
Student A Joe 22

etc.....

This format gives you flexibility and now you can do some analysis on
it.

Now create another table with a unique list of your Editors.

Use the following formula for Mean – changing your variables.

=SUMPRODUCT((B2:B7="John")*(C2:C7))/COUNTIF(B2:B7,"John")

For the Standard deviation paste this formula, change the variables
and press Ctrl – Shift – Enter This will create an array formula for
your standard deviation.

=STDEV(IF(B2:B7="John",C2:C7))

For both formulas I would reference the Unique Editor cells in the
formula so you are not typing their names at all.

Don’t hesitate to post if you need further assistance.

Take care

Marcus

Marcus,

Just a quick Q, the range that you referred to (rows 2 to 7) was arbitrary,
right? It could include rows with or without the editor ("John" as used
throughout) because of the ifs. Right?

Otherwise, this sounds great. I'll give it a try and let you know!



"marcus" wrote:

Hi elf4278

Your problem is not a complicated one per se, the problem is the way
your data is ordered. If the data is in the form you say it is in I
would do the following;

Make a copy of the sheet you are working on. Then Copy Student,
Editor2 and Score(2) and paste this data at the base of the Student
column A, repeat this step for Editor(3) and score(3) pasting at the
base of your student column. Remove the unwanted columns €“ D through
to G. Sort the data by student, Col A.

Now you are in business. Your data should look like this

Student Editor Name Score
Student A John 20
Student A Jane 25
Student A Joe 22

etc.....

This format gives you flexibility and now you can do some analysis on
it.

Now create another table with a unique list of your Editors.

Use the following formula for Mean €“ changing your variables.

=SUMPRODUCT((B2:B7="John")*(C2:C7))/COUNTIF(B2:B7,"John")

For the Standard deviation paste this formula, change the variables
and press Ctrl €“ Shift €“ Enter This will create an array formula for
your standard deviation.

=STDEV(IF(B2:B7="John",C2:C7))

For both formulas I would reference the Unique Editor cells in the
formula so you are not typing their names at all.

Dont hesitate to post if you need further assistance.

Take care

Marcus

Hi elf4278

Sorry for the slow response I was in the land of nod. Yep that is
right the range is arbitrary. You change the range to whatever is
appropriate.

I would have a second table say it started in E2 with the names

John
Jane
Joe
Betsy

In F2 put =SUMPRODUCT(($B$2:$B$7=E2)*($C$2:$C$7))/COUNTIF($B$2:$B
$7,E2)

This would refer to John, drag this formula down and it will encompas
the other 'Editors' and do the same for the standard deviation
formula. Your range will probably go from $B$2:$B$122

Anyways good luck and if you have any trouble send me the
spreadsheet. Happy to help.

Marcus

Works great. Thanks!!

"marcus" wrote:

Hi elf4278

Sorry for the slow response I was in the land of nod. Yep that is
right the range is arbitrary. You change the range to whatever is
appropriate.

I would have a second table say it started in E2 with the names

John
Jane
Joe
Betsy

In F2 put =SUMPRODUCT(($B$2:$B$7=E2)*($C$2:$C$7))/COUNTIF($B$2:$B
$7,E2)

This would refer to John, drag this formula down and it will encompas
the other 'Editors' and do the same for the standard deviation
formula. Your range will probably go from $B$2:$B$122

Anyways good luck and if you have any trouble send me the
spreadsheet. Happy to help.

Marcus