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 |
#21
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|