| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Bonuses and Penalties in Grade Books
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 ------------------------------------------------------------------------ Kahlan's Profile: http://www.excelforum.com/member.php...o&userid=27983 View this thread: http://www.excelforum.com/showthread...hreadid=474923 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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)+(G412)*(G4-12) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Kahlan" wrote in message ... 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 |
|
#4
|
|||
|
|||
|
Thanks to you both for all your help! It is very much appreciated! :) -- Kahlan ------------------------------------------------------------------------ Kahlan's Profile: http://www.excelforum.com/member.php...o&userid=27983 View this thread: http://www.excelforum.com/showthread...hreadid=474923 |
|
#5
|
|||
|
|||
|
You are welcome ... -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=474923 |
|
#6
|
|||
|
|||
|
Kahlan Wrote: 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. -- BenjieLop ------------------------------------------------------------------------ BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019 View this thread: http://www.excelforum.com/showthread...hreadid=474923 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
