Help with Excel Formulas

[nope7308]

I'm trying to create a grade spreadsheet, but I'm having difficulty with one small section...

Students will be writing 6 essays and a total of 10 quizzes. I want excel to automatically drop the two lowest quiz scores, having the remaining 8 quizzes count for 25% of the final grade.

I originally created the spreadsheet with only one column for quizzes, which made calculating the average pretty straightforward. When trying to drop the two lowest scores, however, I have no idea how to make it work.

If the 10 quiz scores are in cells J2-R2, what formula would you use to drop the two lowest scores and have the remaining 8 scores weighted at 25% total?

Any help would be greatly appreciated!

Edit:
Here is the formula I used to calculated the average when I only had one column for the quizzes. As you can see, the assignments are not weighted evenly.
=(D2*0.05)+(E2*0.1)+(F2*0.1)+(G2*0.15)+(H2*0.15)+(I2*0.2)+(J2*0.25)

 

[firestarter]

It's a shame you want to drop the two lowest scores. If you only wanted to drop one, the formula would be trivial... you'd start by summing, and then subtract the MIN() value in the list.

As it is, you may well find that writing a macro function to iterate through the list and find the two smallest values is the easiest way.

 

[nope7308]

I found this website which explains how to do it, but I still can't figure it out. Maybe you can help me out?

http://www.aaupatnyit.org/microsoft_excel_calculate_grades.htm

 

[firestarter]

I'd not heard of the 'small' function before... that does what you want.

I'm wary of helping you too much to be honest. A person using a spreadsheet where they don't understand how it works is dangerous IMO... they often don't notice small problems.

Question for you... do you want to eliminate the two lowest scores, or the lowest scores after weighting?

I think the best spreadsheet practice (especially if you're not an expert) is to take lots of space, and work things out in a lot of smaller steps. In excel you can always collapse columns to hide working.

So, have your grid of scores.

On the right of it, have an equal sized grid of single weighted scores. It would be good to take the weightings out of the formulas and put the values on the top line of the grid, referring to them.

Next column, sum the weighted scores.

Next column, find the minimum score in the weighted array, using MIN or SMALL(range, 1).

Next column, find the 2nd smallest score in the weighted array, using SMALL(range, 2)

Final column, subtract the two minimum scores from the sum of weighted scores.


Laying out like the above means you can check your working every step of the way. Just hide the weighted sum and semi-total columns to tidy up when everything is working.

 

[nope7308]

Thank you for the concern, but I would really prefer that someone provide me with the exact formula. I can easily do the calculations manually to verify accuracy, but that would defeat the purpose of using the formula in the first place.

For clarity, let me explain exactly what I need and the precise format of the spreadsheet...

Column A - C will contain identifying information, and columns D - I will be reserved for the 6 assignments (each weighted differently). Columns J - R will be reserved for the 10 quizzes. Of those 10 quizzes, I want to drop the two lowest scores. The remaining 8 quizzes will account for 25% of the final grade. The final grade will be the last column

Now, can someone please write the specific formula I need? I can easily verify the accuracy of the formula by awarding 100% in 8 of the quizzes and 50% in the other two. If the final grade is 25%, then the formula works.

 

[MisterMe]

Thank you for the concern, but I would really prefer that someone provide me with the exact formula. I can easily do the calculations manually to verify accuracy, but that would defeat the purpose of using the formula in the first place.

....

You have amped-up firestarter's concern to full-fledged dread on my part. A spreadsheet is a powerful and dangerous application when you know what you are doing. When you are not sure, you really ought to look for alternatives. There are dedicated gradebook applications that will allow you to drop as many of the lowest grades that you like. You are guaranteed with the dedicated programs that all students' grades are calculated using the same criteria.

 

[CylonGlitch]

Column A - C will contain identifying information, and columns D - I will be reserved for the 6 assignments (each weighted differently). Columns J - R will be reserved for the 10 quizzes. Of those 10 quizzes, I want to drop the two lowest scores. The remaining 8 quizzes will account for 25% of the final grade. The final grade will be the last column

If the 10 quizzes are in the range d1:d10 then the formula you want is this :
=(sum(d1:d10)-small(d1:d10,1)-small(d1:d10,2))/8

What you do with it, is up to you. Make sure things are done right though.

 

[saberahul]

Assuming you have n quizzes, why not just sort and replace the column as n-2.

 

[nope7308]

If the 10 quizzes are in the range d1:d10 then the formula you want is this :
=(sum(d1:d10)-small(d1:d10,1)-small(d1:d10,2))/8

What you do with it, is up to you. Make sure things are done right though.

Thank you SO much! As it turns out, I almost had the exact formula. Not to fear, I will keep an eye on the calculations and be certain that nothing goes awry.

Thanks again!

 

[macmick]

Thank you SO much! As it turns out, I almost had the exact formula. Not to fear, I will keep an eye on the calculations and be certain that nothing goes awry.

Thanks again!

Another way would be- AVERAGE(LARGE(A1:A10,{1,2,3,4,5,6,7,8}))