Bonuses and Penalties in Grade Books

**Kahlan** · 10-10-2005, 06:44 PM

Hi all,

I am working on an assignment in my textbookwith a grade book. I've already figured out the test average of 3 tests using the SUM and MIN functions. The test grades are in cells B4:E4. I had to drop the lowest grade and compute the average of the remaining three, which I've already done.
=SUM(B4:E4)-MIN(b4:E4))/3
There is a next part I am stuck on. Students are required to complete a designated number of homework assignments(which is 12 and it is in cell E19). Then receive a bonus or penalty for ever additional or deficient home assignment. The bonus is added to the test average to determine the semester average. The penalty for each assignment missing is 2 and that info is in cell E20 and the bonus for each additional assignment is 1 and that info is in cell E21. Cell H4 is the bonus or penalty and I4 is the semester average.
I would be grateful for any help. Thanks!

Kahlan

**Kahlan** · 10-10-2005, 07:02 PM

Oh, the number of homeworks is in G4 and bonus or the bonus or penalty formula will go in H4.

Thanks,
Kahlan

**BenjieLop** · 10-10-2005, 09:03 PM

Originally Posted by Kahlan

Hi all,

I am working on an assignment in my textbookwith a grade book. I've already figured out the test average of 3 tests using the SUM and MIN functions. The test grades are in cells B4:E4. I had to drop the lowest grade and compute the average of the remaining three, which I've already done.
=SUM(B4:E4)-MIN(b4:E4))/3
There is a next part I am stuck on. Students are required to complete a designated number of homework assignments(which is 12 and it is in cell E19). Then receive a bonus or penalty for ever additional or deficient home assignment. The bonus is added to the test average to determine the semester average. The penalty for each assignment missing is 2 and that info is in cell E20 and the bonus for each additional assignment is 1 and that info is in cell E21. Cell H4 is the bonus or penalty and I4 is the semester average.
I would be grateful for any help. Thanks!

Kahlan

Try this:

In Cell E20: =if(G4<12,(G4-E19)*2,"")

In Cell E21: =if(G4>=12,G4-E19,"")

In Cell H4: =if(E20="",E21,E20)

Therefore, the semester average in Cell I4:

=((SUM(B4:E4)-MIN(B4:E4))/3) + H4

I hope this is what you need.

Regards.

RagDyer · 10-10-2005, 10:05 PM

Try this single formula, which will calculate the semester average by just
using the number of homework assignments completed that were entered in G4.

No need for a separate bonus or penalty calculation cell, unless you might
want it for reference.

Enter this in I4:

=AVERAGE(LARGE(B4:E4,{1,2,3}))+(G4<12)*((G4-12)*2)+(G4>12)*(G4-12)

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Kahlan" <[email protected]> wrote in
message news:[email protected]...
>
> Oh, the number of homeworks is in G4 and bonus or the bonus or penalty
> formula will go in H4.
>
> Thanks,
> Kahlan
>
>
> --
> Kahlan
> ------------------------------------------------------------------------
> Kahlan's Profile:
http://www.excelforum.com/member.php...o&userid=27983
> View this thread: http://www.excelforum.com/showthread...hreadid=474923
>