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
  #21  
Old March 18th, 2010, 11:00 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default how to calculate lowest value omiting blank cells

Assuming the numbers are all positive:

=SMALL(A1:A10,COUNTIF(A1:A10,0)+1)

--
Biff
Microsoft Excel MVP


stefan struk wrote in message ...
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:

On Wednesday, October 22, 2008 11:15 AM
Suz wrote:

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!!

On Wednesday, October 22, 2008 11:25 AM
="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote:

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?


"Suzy" wrote:

On Wednesday, October 22, 2008 11:35 AM
Teethlessmam wrote:

How to omit blank cells when calculating weighted averages
Just treat the missing assigment as a perfect score.


"Suzy" wrote:

On Wednesday, October 22, 2008 11:44 AM
="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote:

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.

"Teethless mama" wrote:

On Wednesday, October 22, 2008 11:47 AM
TomP wrote:

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(--(B55),(B22))/(IF(ISBLANK(B5),0,B2)+IF(ISBLANK(C5),0,C2)+IF(ISBL ANK(D5),0,D2))

On Wednesday, October 22, 2008 12:10 PM
TomP wrote:

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(B55,$B$2:$D$2)/SUMPRODUCT(--((B55)0),$B$2:$D$2)

Tom

On Thursday, October 23, 2008 3:19 PM
Suz wrote:

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 weights
redistributed if it is not 50-50?


"TomPl" wrote:

On Thursday, October 23, 2008 3:38 PM
TomP wrote:

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(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.

On Thursday, October 23, 2008 4:30 PM
Suz wrote:

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!!

"TomPl" wrote:

On Thursday, October 23, 2008 4:47 PM
TomP wrote:

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

On Thursday, October 23, 2008 5:28 PM
Suz wrote:

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

"TomPl" wrote:

On Thursday, October 23, 2008 5:35 PM
TomP wrote:

How to omit blank cells when calculating weighted averages
"Suzy" wrote:

On Thursday, October 23, 2008 5:36 PM
TomP wrote:

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

On Friday, October 24, 2008 2:40 PM
Suz wrote:

How to omit blank cells when calculating weighted averages
thank you so much for your help!!

suzy

"TomPl" wrote:

On Wednesday, October 29, 2008 10:26 AM
Suz wrote:

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(D424)/21

Thanks!!

Suzy


"TomPl" wrote:

On Wednesday, October 29, 2008 3:55 PM
David Biddulph wrote:

How to omit blank cells when calculating weighted averages
Have you tried =SUM(D424)/COUNT(D424) or =AVERAGE(D424) ?
--
David Biddulph

On Thursday, October 30, 2008 12:18 PM
Suz wrote:

How to omit blank cells when calculating weighted averages
Hi David,

AVERAGE omits the blank cells, right?

Thanks,
Suzy

"David Biddulph" wrote:

On Thursday, October 30, 2008 1:46 PM
David Biddulph wrote:

How to omit blank cells when calculating weighted averages
Yes. That's why I included =AVERAGE(D424) 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

"Suzy" wrote in message
...

On Thursday, October 30, 2008 4:19 PM
Suz wrote:

How to omit blank cells when calculating weighted averages
Thanks so much!!

Suzy

"David Biddulph" wrote:


Submitted via EggHeadCafe - Software Developer Portal of Choice
How to Annotate Images from a database in a web page
http://www.eggheadcafe.com/tutorials...images-fr.aspx



Ads
 




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 02:00 AM.


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