Individual Student Grade Sheet--Help!

**dramirez** · 03-22-2012, 08:35 PM

Hello,

I have created a grade sheet for each individual student. Under the "CHEM1103" tab (see attachment) in cell D20 I have 1) formula to calculate current grade as I input earned scores in column E; and 2) drop the lowest homework score. Now without riding of these tasks (current grade calculation and dropping the lowest homework score), how can I "replace" the lowest, non-zero exam score (worth 150 pts.) with the score earned on the final if the final (worth 200 pts.) is higher? (Note: the "Points" column (D) is the total points possible, and the "Score" column (E) is the score earned by the student.)

If there is a better formula(s) that can be used that will accomplish the same three tasks, that would be fantastic.

Any help is greatly appreciated! Thank you much!

**rylo** · 03-22-2012, 10:13 PM

Hi

1) Couldn't your formula in D20 be changed to (or something similar)

Please Login or Register  to view this content.

as you would know what the minimum homework and exam scores are going to be and can specifically nominate that cells?

2) E20:

Please Login or Register  to view this content.

This formula is array entered. Hold down the ctrl and shift keys, then press enter.

rylo

**dramirez** · 03-23-2012, 12:02 AM

Rylo,

Thank you so much for your response! This is looking good. I'm wondering, though. Do the codes you gave me satisfy the fact that if, for instance, the score on the final was 180/200 pts (90%), then the lowest exam's score (exam 2), 105/150 pts (70&), will change from 70% (105) to 90% (135) as earned on the final? The reason I ask is because the total points at the end of the semester is 900, and right now (after inserting your codes and pseudo scores to "end" the semester) the total points is 750.

Thanks again!

**rylo** · 03-23-2012, 12:06 AM

Hi

Your example file only has one percentage and that is based on the overall result. Where are you putting your individual results? Update example file to cover the scenario.

rylo

**dramirez** · 03-23-2012, 12:28 AM

Rylo,

Ok. I have entered pseudo scores in column E. I still want D20 to be 900 IF the score on the final is higher (as is in the attachment).

Thank you!

**rylo** · 03-23-2012, 12:44 AM

Hi

I guess I'm not reading this right. My understanding is that when all the results are in for a student, then the points total would be the sum of everything in column D, less one instance of the homework (as they are all the same) and one instance of the 150 for exam as the final exam is higher than the values you have for the individual exam. That gives a max total to aim for of 750.

What exactly is the maximum total for a student that has got results for everything? And how is it calculated?

rylo

**dramirez** · 03-23-2012, 01:08 AM

Rylo,

It's ok. I may not be conveying what I'm trying to say adequately. The total points for the entire semester each student can earn is 900, which already includes -10 for lowest homework. There are 4 150-pt exams and 1 200-pt final. If the student earns a higher score on the final, then the lowest of the 4 150-pt exams is not fully dropped but only replaced with the percentage earned on the final. For instance, in the above attachment, the student scored 126/150 [84% (ex. 1)], 105/150 [70% (ex. 2)], 135/150 [90% (ex. 3)], and 135/150 [90% (ex. 4], and on the final, 180/150 (90%). Since the student got a 90% on the final, I want to replace the lowest exam score (ex. 2, 70%) with 90% of 150 points, which is 135/150. In the end, however, the only item dropped is 1 10-pt homework (11 homework assignments were initially assigned). So, without any drops, the total is 910. 910-10 (lowest hw) = 900. No exam is dropped, only replaced.

I'm sorry if this is convoluted. I saw this a few days ago and it seems like a good approach, but the only thing is how can I apply this (http://www.excelforum.com/excel-work...mula-help.html) to MY particular grade sheet? I tried different codes but I kept getting either # signs or other jargon.

I really appreciate your patience and assistance.

**rylo** · 03-25-2012, 06:17 PM

Hi

About the only ways I can see to do this is either have "helper" columns, or to create your own function. Are either / both of these options available?

Given the example file in #5, is the Expected score 803.7? If not, then can you advise what the result should be and how it is achieved.

rylo