A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to omit blank cells when calculating weighted averages



 
 
Thread Tools Display Modes
  #1  
Old October 22nd, 2008, 04:15 PM posted to microsoft.public.excel.worksheet.functions
Suzy
external usenet poster
 
Posts: 39
Default How to omit blank cells when calculating weighted averages

Hello, new to Excel and need help with function.
I am calculating grades for my students, and if they were absent, I do not
want them penalized. What function will omit the blank cells when the formula
is calculating the weighted average? I tried the average option but it still
treated the cell as a zero.

Here is the formula:
=((B5*$B$2)+(C5*$C$2)+(D5*$D$2))

When I tried removing the section of the formula for students who were
missing the assignment in B5, the average still remained low. Thanks in
advance!!
Ads
  #2  
Old October 22nd, 2008, 04:25 PM posted to microsoft.public.excel.worksheet.functions
Sheeloo[_3_]
external usenet poster
 
Posts: 1,713
Default How to omit blank cells when calculating weighted averages

I am assuming that B2, C2, and D2 contain the weights and are same for
everyone.
How do you calculate the weights?

If a student was absent and does not have a score in say B then you need to
redistribute the weights... How will you do that? I mean what is the
applicable logic to calculate?


"Suzy" wrote:

Hello, new to Excel and need help with function.
I am calculating grades for my students, and if they were absent, I do not
want them penalized. What function will omit the blank cells when the formula
is calculating the weighted average? I tried the average option but it still
treated the cell as a zero.

Here is the formula:
=((B5*$B$2)+(C5*$C$2)+(D5*$D$2))

When I tried removing the section of the formula for students who were
missing the assignment in B5, the average still remained low. Thanks in
advance!!

  #3  
Old October 22nd, 2008, 04:35 PM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
Posts: 3,722
Default How to omit blank cells when calculating weighted averages

Just treat the missing assigment as a perfect score.


"Suzy" wrote:

Hello, new to Excel and need help with function.
I am calculating grades for my students, and if they were absent, I do not
want them penalized. What function will omit the blank cells when the formula
is calculating the weighted average? I tried the average option but it still
treated the cell as a zero.

Here is the formula:
=((B5*$B$2)+(C5*$C$2)+(D5*$D$2))

When I tried removing the section of the formula for students who were
missing the assignment in B5, the average still remained low. Thanks in
advance!!

  #4  
Old October 22nd, 2008, 04:44 PM posted to microsoft.public.excel.worksheet.functions
Sheeloo[_3_]
external usenet poster
 
Posts: 1,713
Default How to omit blank cells when calculating weighted averages

I think that will reward the absentee... as he will score higher than someone
who gave the test and got 99%.

I would suggest assigning the average of her other two scores as the missing
score, if the weights are same.

"Teethless mama" wrote:

Just treat the missing assigment as a perfect score.


"Suzy" wrote:

Hello, new to Excel and need help with function.
I am calculating grades for my students, and if they were absent, I do not
want them penalized. What function will omit the blank cells when the formula
is calculating the weighted average? I tried the average option but it still
treated the cell as a zero.

Here is the formula:
=((B5*$B$2)+(C5*$C$2)+(D5*$D$2))

When I tried removing the section of the formula for students who were
missing the assignment in B5, the average still remained low. Thanks in
advance!!

  #5  
Old October 22nd, 2008, 04:47 PM posted to microsoft.public.excel.worksheet.functions
TomPl
external usenet poster
 
Posts: 239
Default How to omit blank cells when calculating weighted averages

This formula is complicated and very specific to your description, but it
should accruately calculate the weighted average of the assignments that do
not have a blank in row 5:

=SUMPRODUCT(--(B55),(B22))/(IF(ISBLANK(B5),0,B2)+IF(ISBLANK(C5),0,C2)+IF(ISBL ANK(D5),0,D2))

  #6  
Old October 22nd, 2008, 05:10 PM posted to microsoft.public.excel.worksheet.functions
TomPl
external usenet poster
 
Posts: 239
Default How to omit blank cells when calculating weighted averages

Though it does the same thing, I like this formula a little better:

=SUMPRODUCT(B55,$B$2:$D$2)/SUMPRODUCT(--((B55)0),$B$2:$D$2)

Tom
  #7  
Old October 23rd, 2008, 08:19 PM posted to microsoft.public.excel.worksheet.functions
Suzy
external usenet poster
 
Posts: 39
Default How to omit blank cells when calculating weighted averages

Hi Tom, thanks so much!! That was so helpful!!

Can you tell me how it calculating the weights? How are the weights
redistributed if it isn't 50-50?


"TomPl" wrote:

Though it does the same thing, I like this formula a little better:

=SUMPRODUCT(B55,$B$2:$D$2)/SUMPRODUCT(--((B55)0),$B$2:$D$2)

Tom

  #8  
Old October 23rd, 2008, 08:38 PM posted to microsoft.public.excel.worksheet.functions
TomPl
external usenet poster
 
Posts: 239
Default How to omit blank cells when calculating weighted averages

The weighted average is achieved by multiplying the number of credits by the
grade for each class then adding the products together
"Sumproduct(B55,$B$2:$D$2)". If the value of a grade is zero then the
product is zero and does not affect the total.

The sum is then divided by the total number of credits excluding those
credits that have no grade "Sumproduct(--((B55)0),$B$2:$D$2)". The part
--((B55)0) creates a boolean 1/0 which is multiplied by the number of
credits to get the number of credit with a grade assigned.

It's just that simple.
  #9  
Old October 23rd, 2008, 09:30 PM posted to microsoft.public.excel.worksheet.functions
Suzy
external usenet poster
 
Posts: 39
Default How to omit blank cells when calculating weighted averages

Hi Tom,

How could I explain it if I had to explain it to parents when they see their
child's grade?

Thanks!!

"TomPl" wrote:

The weighted average is achieved by multiplying the number of credits by the
grade for each class then adding the products together
"Sumproduct(B55,$B$2:$D$2)". If the value of a grade is zero then the
product is zero and does not affect the total.

The sum is then divided by the total number of credits excluding those
credits that have no grade "Sumproduct(--((B55)0),$B$2:$D$2)". The part
--((B55)0) creates a boolean 1/0 which is multiplied by the number of
credits to get the number of credit with a grade assigned.

It's just that simple.

  #10  
Old October 23rd, 2008, 09:47 PM posted to microsoft.public.excel.worksheet.functions
TomPl
external usenet poster
 
Posts: 239
Default How to omit blank cells when calculating weighted averages

Suzy,

If a child gets a grade of .80 on a 5 credit assignment that counts for 4.00
points (.80 X 5).

If a child gets a grade of .90 on a 3 credit assignment that counts for 2.70
points (.90 X 3).

Therefore the weighted average grade for that child is total points (4.00 +
2.70) divided by total credits (5 + 3) or 6.7 ÷ 8 = .8375 (you might round
that to .84).

Actually, I may have misunderstood your question. I assumed that row 2 has
the number of credits for the assignment and that row 5 has an individual
student's grade for that assignment. If that is not true, please do describe
what is in rows 2 and 5 of your example.

It is always about communication!

Tom
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.