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:P1>0,M1:P1/$M$3:$P$3))

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

Hope this helps!

In article <72985CF6-B29E-4A01-8DA1-5D374F80668C@microsoft.com>,
"Scafidel" <Scafidel@discussions.microsoft.com> 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:P1>0,M1:P1/$M$3:$P$3))
>
> ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Format cell as
> 'Percentage'.
>
> Hope this helps!
>
> In article <72985CF6-B29E-4A01-8DA1-5D374F80668C@microsoft.com>,
> "Scafidel" <Scafidel@discussions.microsoft.com> 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" <Scafidel@discussions.microsoft.com> wrote in message
news:9CAC7EFE-33AD-4B2D-B111-11981F137BFE@microsoft.com...
> 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:P1>0,M1:P1/$M$3:$P$3))
>>
>> ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>> Format cell as
>> 'Percentage'.
>>
>> Hope this helps!
>>
>> In article
>> <72985CF6-B29E-4A01-8DA1-5D374F80668C@microsoft.com>,
>> "Scafidel" <Scafidel@discussions.microsoft.com> 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" <Scafidel@discussions.microsoft.com> wrote in message
> news:9CAC7EFE-33AD-4B2D-B111-11981F137BFE@microsoft.com...
> > 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:P1>0,M1:P1/$M$3:$P$3))
> >>
> >> ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
> >> Format cell as
> >> 'Percentage'.
> >>
> >> Hope this helps!
> >>
> >> In article
> >> <72985CF6-B29E-4A01-8DA1-5D374F80668C@microsoft.com>,
> >> "Scafidel" <Scafidel@discussions.microsoft.com> 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%
> >>
>
>
>