Average a group of tests for grade, some tests not taken by all.

[Guest]

I have a sheet with (L to R) student name, number right each test, total
right, average percentage, letter grade. After the names the row has
question number at bottom which are added up at the end of the row. Total
right are divided by total questions. The problem arises when a student does
not take a test. The blank cell is, in effect, like a zero on a test, as
that student's total is still divided by total questions, giving the wrong
grade (see Fred, who missed the second test). Sorry for the long length.
John 10 9 12 31 97% A
Fred 10 10 20 63% F
Total10 10 12 32 100%

 

[Domenic]

Assuming that M12 contains student test scores, and M33 contains the
total score for each test, try...

Q1, copied down:

=AVERAGE(IF(M11>0,M11/$M$3:$P$3))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Format cell as
'Percentage'.

Hope this helps!

 

[Guest]

Thanks.
When I tried this formula I got "#value!". Also, I'm not sure what you mean
by "....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. "

 

[Chip Pearson]

by "....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. "

This means that after you type in the formula, you need to press
CTRL SHIFT and ENTER all at the same time, rather than just
ENTER. If you do this properly, Excel will display the formula
enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

Ah-hah! Use the Force, Luke!
Thanks

This means that after you type in the formula, you need to press
CTRL SHIFT and ENTER all at the same time, rather than just
ENTER. If you do this properly, Excel will display the formula
enclosed in curly braces {}.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com