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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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!! |
#2
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|