Help: I need a way to combine these 3 criteria into one averaging formula.

I need help finding a way to combine these 3 criteria into one averaging formula for a gradebook simulation for a class that I can't figure out:

1) Drop the lowest test grade.

2) Add bonus if completed. 6 pts if attendance bonus AND HW bonus are completed. 2 pts if attendance bonus OR HW bonus is completed (completion of bonus is indicated by "y" or "n")

3) Weight the value of tests (5 Regular Tests = %75, 1 Final = %25)

I'm trying to find a final average based on these 3 criteria. I know how to do each of these separately, but I don't know how to combine them. Perhaps there is some magic function that does all 3 at once?

I've marked the cell I need the final average in with red.

Hi Taco Mc Nacho,

It sounds like you need the AverageIFS function (maybe)

http://www.excelfunctions.net/Excel-...-Function.html

After seeing that you give points for Homework and Attendance you may need to simply add a bunch of numbers and divide them by the total possible points.