|
Average a group of tests for grade, some tests not taken by all.
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% |
Assuming that M1:P2 contains student test scores, and M3:P3 contains the
total score for each test, try... Q1, copied down: =AVERAGE(IF(M1:P10,M1:P1/$M$3:$P$3)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Format cell as 'Percentage'. Hope this helps! In article , "Scafidel" wrote: 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% |
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. " "Domenic" wrote: Assuming that M1:P2 contains student test scores, and M3:P3 contains the total score for each test, try... Q1, copied down: =AVERAGE(IF(M1:P10,M1:P1/$M$3:$P$3)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Format cell as 'Percentage'. Hope this helps! In article , "Scafidel" wrote: 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% |
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 "Scafidel" wrote in message ... 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. " "Domenic" wrote: Assuming that M1:P2 contains student test scores, and M3:P3 contains the total score for each test, try... Q1, copied down: =AVERAGE(IF(M1:P10,M1:P1/$M$3:$P$3)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Format cell as 'Percentage'. Hope this helps! In article , "Scafidel" wrote: 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% |
Ah-hah! Use the Force, Luke!
Thanks "Chip Pearson" wrote: 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 "Scafidel" wrote in message ... 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. " "Domenic" wrote: Assuming that M1:P2 contains student test scores, and M3:P3 contains the total score for each test, try... Q1, copied down: =AVERAGE(IF(M1:P10,M1:P1/$M$3:$P$3)) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Format cell as 'Percentage'. Hope this helps! In article , "Scafidel" wrote: 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% |
| All times are GMT +1. The time now is 12:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com