HI, I'm looking for a formula that allows me to calculate the lowest grade in a range, but not including the "blank" or "0" values in cells within the range.
-thanks
Suz wrote:
How to omit blank cells when calculating weighted averages
22-Oct-08
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!!
Previous Posts In This Thread:
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!!
I am assuming that B2, C2, and D2 contain the weights and are same for
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
:
RE: How to omit blank cells when calculating weighted averages
Just treat the missing assigment as a perfect score
:
I think that will reward the absentee...
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
:
This formula is complicated and very specific to your description, but it
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(--(B5
5),(B2
2))/(IF(ISBLANK(B5),0,B2)+IF(ISBLANK(C5),0,C2)+IF(ISBLANK(D5),0,D2))
Though it does the same thing, I like this formula a little
Though it does the same thing, I like this formula a little better
=SUMPRODUCT(B5
5,$B$2:$D$2)/SUMPRODUCT(--((B5
5)>0),$B$2:$D$2
Tom
Hi Tom, thanks so much!! That was so helpful!!
Hi Tom, thanks so much!! That was so helpful!
Can you tell me how it calculating the weights? How are the weight
redistributed if it is not 50-50
:
The weighted average is achieved by multiplying the number of credits by the
The weighted average is achieved by multiplying the number of credits by the
grade for each class then adding the products together
"Sumproduct(B5
5,$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(--((B5
5)>0),$B$2:$D$2)". The part
--((B5
5)>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.
Hi Tom,How could I explain it if I had to explain it to parents when they see
Hi Tom,
How could I explain it if I had to explain it to parents when they see their
child's grade?
Thanks!!
:
Suzy,If a child gets a grade of .80 on a 5 credit assignment that counts for 4.
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
Hi Tom,Row 2 has the percentage the assignment was worth out of 100%, so row 2
Hi Tom,
Row 2 has the percentage the assignment was worth out of 100%, so row 2 (for
this specific assignment) has 30%, 25%, and 45%.
Then row 5 has the score the student received for the assignment, in this
case, blank, 85, and 68.
Thank you so much!!
Suzy
:
RE: How to omit blank cells when calculating weighted averages
:
The formula will work equally as well with percentages as with credits so I
The formula will work equally as well with percentages as with credits so I
think it should do what you asked.
Good luck with it.
Tom
RE: How to omit blank cells when calculating weighted averages
thank you so much for your help!!
suzy
:
Hi Tom,Can you help me with another formula?
Hi Tom,
Can you help me with another formula? I am also calculating the averages of
each particular assignment in the class. How do I omit the blank cells from
the class average for the assignment?
The formula is:
=SUM(D4
24)/21
Thanks!!
Suzy
:
Re: How to omit blank cells when calculating weighted averages
Have you tried =SUM(D4
24)/COUNT(D4
24) or =AVERAGE(D4
24) ?
--
David Biddulph
Re: How to omit blank cells when calculating weighted averages
Hi David,
AVERAGE omits the blank cells, right?
Thanks,
Suzy
:
Re: How to omit blank cells when calculating weighted averages
Yes. That's why I included =AVERAGE(D4
24) as one of my 2 suggestions.
And for further information you could try hitting the F1 key and typing the
word Average.
RTFM doesn't have to be the *last* resort.
--
David Biddulph
Re: How to omit blank cells when calculating weighted averages
Thanks so much!!
Suzy
:
Submitted via EggHeadCafe - Software Developer Portal of Choice
How to Annotate Images from a database in a web page
http://www.eggheadcafe.com/tutorial...9-082c24acd999/how-to-annotate-images-fr.aspx