Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Scafidel
 
Posts: n/a
Default 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%

  #2   Report Post  
Domenic
 
Posts: n/a
Default

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%

  #3   Report Post  
Scafidel
 
Posts: n/a
Default

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%


  #4   Report Post  
Chip Pearson
 
Posts: n/a
Default

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%




  #5   Report Post  
Scafidel
 
Posts: n/a
Default

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%




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
What is this kind of average called? havocdragon Excel Worksheet Functions 3 June 24th 05 05:10 PM
Calculate cell row and column Barb R. Excel Discussion (Misc queries) 7 May 2nd 05 07:02 PM
Need help w/ Weight Formula Tom Excel Discussion (Misc queries) 3 March 4th 05 06:23 PM
Can't group pivot table items by month in Excel scott_ensley Excel Discussion (Misc queries) 1 February 1st 05 09:41 PM
Subtotal of Subtotal displays Grand Total in wrong row Thomas Born Excel Worksheet Functions 5 January 6th 05 02:46 PM


All times are GMT +1. The time now is 01:25 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"