The Higher Number Becomes The Average

[Cornelius Morris]

In a grade book, if students get a grade on the final exam that is higher than the midterm grade, this becomes the exam portion of their (if it's lower, it's averaged). So a student who gets an 81 on the midterm and a 90 on the final gets a 90 for the exam portion of the final grade. What's the easiest formula to make the final exam grade the average if it's higher than the midterm grade?

Right now the formula I use for the course grade is this:

=SUM(I3*$I$1)+(J3*$J$1)+(K3*$K$1)

where I3/J3/K3 are the midterm/final/quiz averages, and I1/J1/K1 represents the respective % (30/40/30) of the grade assigned to the midterm/final/quizzes.

Many thanks in advance for any suggestions.

 

[barry houdini]

To get the average of I3 and J3 if J3 is lower but J3 if J3 is higher

=MAX(J3,(I3+J3)/2)

 

[Cornelius Morris]

Thanks, Barry.

How do you then average that with the 3rd number (K3 in my original)?

Average of I3 + J3 +K3 is easy. But how to do yours (J3/2 if J3 is higher than I3, but average of the above 3 if J3 is =< I3?

Thanks.

 

[barry houdini]

I'm not really clear on what you want now, can you give a couple of examples with some figures?

 

[Cornelius Morris]

I3 is the midterm exam, J3 is the final exam, K3 is the average of all the quizzes. If it were a straight average, then I would just average those 3 numbers (75%, 90%, 78%). But I give the provision that if a student gets a higher number on the final (J3) than the midterm (I3), instead of simply averaging, I make the final exam grade (J3) the average of the 2 exams--to reward improvement. So if the final is the same, or lower, than the midterm, it's just a straight average of the 3. If higher, that's the exams' average, which is then 2/3 of the final grade, with 1/3 contributed by the quizzes (K3).

Does that help?

 

[barry houdini]

So where does the 30/40/30 come in then?

 

[Cornelius Morris]

30/40/30 was the original breakdown of the percentages (midterm counts 30%, final counts 40, quizzes count 30). I need to keep that in the formula anymore. Simply averaging 3 values would be easier, and if the final were higher than the midterm, that portion becomes 2/3 of the grade).

Thanks for considering it.

 

[indiantrix]

This may be what you need...
=MAX(J3,(I3+J3)/2)*SUM($I$1,$J$1)+K3*$K$1
Larry.

 

[barry houdini]

One way to look at it then is that you just average all 3, but I3 must be at least as high as J3, so that would be

=AVERAGE(J3,K3,MAX(I3,J3))

 

[Cornelius Morris]

Both of those suggestions work well. Thanks very much.