Calculating grades

**sabk2001** · 04-08-2007, 09:05 PM

I finished a complete Excel AP course in high school and here I am 3 years later STUCK in Excel! Would be great if you guys can help me out in this!

So I'm working on a spreadsheet that replicates a grade sheet for a student in a class. This grade sheet includes spreadsheets for quizzes, assignments, tests, etc. In the assignments section, two of the lowest scored assignments get dropped hence not included in the final score. There is a simple Yes or No field next to each assignment and if it "Yes" then the assignment is counted in the final score, and if "No" then the assignment is ignored. This has to be done for both, total points possible for the assignments as well as points earned by the student for the assignments. I have tried If then statements and no luck. Any ideas would be great.

Thanks.

**mudraker** · 04-08-2007, 09:19 PM

column A = yes
column B = score

=SUMIF(A1:A11,"yes",B1:B11)

**Paul** · 04-08-2007, 09:29 PM

Can you post a copy of your workbook (minus any private data)? You must zip it first (right-click on file, choose Send To -> Compressed Folder) then upload that zip file.

Offhand, without seeing your data, it's tough to come up with a solution.

Making some assumptions:

Column A = Student, Column B = Possible Points, Column C = Earned Points...

I can possibly see using a helper column (column D) to calculate the actual grade on the assignment (=column C/column B) and in column E use the formula:

=IF(OR(D2=SMALL($D$2:$D$20,1),D2=SMALL($D$2:$D$20,2)),"NO","YES")

A true resolution will depend on the layout of your data though.

NOTE: Based on our replies, there may be some confusion.. are you looking for how to automatically fill in the YES/NO's? I thought that was your aim. If not, then Mud's response may be what you're after? Let us know.

**sabk2001** · 04-08-2007, 09:39 PM

mudraker, thanks for the reply but I'm still a little confused because the Yes or No field is for each assignment, not for the all the assignments.

To better clear any confusion I have attached the Excel file. The Yes or No field is in the Chapters sheet under "Include? Yes or No".

Thanks.

**sabk2001** · 04-08-2007, 10:33 PM

Never mind my previous post. I got it to work, the SUM IF function worked like a charm, thanks.

**Paul** · 04-08-2007, 10:49 PM

sabk,

Glad to see you got it figured out..

I went ahead and updated some of your formulas to remove errors if they result in errors, and I also added a formula to automatically add "YES" and "NO" to the Chapters sheet if it should be counted.

The formula I used assumes that if the student only does 13 or less (of the 15) chapter assignments, then all 13 of those that were done will count. (Meaning, the two zeros they get for not doing the other two chapters will count as the two that get knocked off.) If they did 14 of 15 assignments, the one zero and their lowest score from the remaining will be knocked off. If they did all 15, their two lowest scores would not count.

Anything in blue is a formula I updated, and the red data on the Chapters sheet is sample data I was using to test the formulas. Does that help at all?

**sabk2001** · 04-08-2007, 11:29 PM

pjoaquin,

Definitely helped. Looks much better as "0" rather than "#DVI/0!". Lol. Although I probably would have gotten to that after I was done with all the fun formulas

. Quick question, I reviewed the formulas that you used for the "Yes and No" field of the sheet. Is it possible you can explain how this worked?

=IF(D10>SMALL($D$4:$D$18,2),"YES","NO")

Thanks.

**Paul** · 04-08-2007, 11:59 PM

Is it possible you can explain how this worked?

=IF(D10>SMALL($D$4:$D$18,2),"YES","NO")

This formula checks the value in cell D10 to see if it is greater than the 2nd smallest value in the entire range of percentages (D4:D18 on that sheet). If it is greater than the two smallest percentages, YES is returned, otherwise NO is returned.

Although, I now see that it doesn't work 100% of the time. For example, if you have 3 scores at 70%, and 70% happens to be the lowest or the second lowest, it will disregard all three scores.

There should be a way to just disregard the low 2. If I have some time I'll try to look into getting it right (or if someone else has time, by all means..) At least for now it can easily be done manually.