Bonuses and Penalties in Grade Books

[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

 

[Kahlan]

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

Thanks,
Kahlan

 

[RagDyer]

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!
==============================================

 

[BenjieLop]

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.

 

[Kahlan]

Thanks to you both for all your help! It is very much appreciated!

 

[BenjieLop]

You are welcome ...