Making a Grade Book in Excel 2007

[HenryDCase]

I'm trying to make a grade book in Excel 2007. So far, I have what I need, but I'm trying to make one step a little easier. I need a formula that can find the value in one column, look up a specific number based off that, and tally the totals between three columns while dropping the lowest score.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]In-class Essay 1[/TD]
[TD="align: center"]In-class Essay 2[/TD]
[TD="align: center"]In-class Essay 3[/TD]
[TD="align: center"]Total Extra Credit[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]grade[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]17[/TD]
[/TR]
[TR]
[TD="align: center"]extra credit earned[/TD]
[TD="align: center"](+10)[/TD]
[TD="align: center"](+4)[/TD]
[TD="align: center"](+7)[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

So, if a student gets the following points on these essays (the numbers along the 'grade' line), they get the amount of extra credit listed below (listed in the 'extra credit earned' line). The 'extra credit earned' line does NOT appear on my grade sheet.

The formula needs to read the grade earned, find the extra credit earned, total the three extra credit scores, and then drop the lowest extra credit score if there are three grades listed--otherwise, the total would be between the two grades with nothing dropped. The student above would receive 17 points of extra credit.

I know this is a complicated formula. Is it even possible? Thanks for any help you can give!

 

[shift-del]

Hello Henry

Is this what you wanted?

<colgroup><col style="width: 28ptpx"><col width="94,5pt"><col width="74,25pt"><col width="74,25pt"><col width="74,25pt"><col width="85,5pt"></colgroup><tbody>
[TD="colspan: 6, align: center"]Arbeitsblatt mit dem Namen 'Tabelle1'[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]

[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #FDE9D9, align: right"] [/TD]
[TD="bgcolor: #FDE9D9, align: left"]In-class Essay 1[/TD]
[TD="bgcolor: #FDE9D9, align: left"]In-class Essay 2[/TD]
[TD="bgcolor: #FDE9D9, align: left"]In-class Essay 3[/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #FDE9D9, align: left"]min. grade[/TD]
[TD="bgcolor: #FDE9D9, align: right"]26[/TD]
[TD="bgcolor: #FDE9D9, align: right"]15[/TD]
[TD="bgcolor: #FDE9D9, align: right"]20[/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #FDE9D9, align: left"]extra credit earned[/TD]
[TD="bgcolor: #FDE9D9, align: right"]10[/TD]
[TD="bgcolor: #FDE9D9, align: right"]4[/TD]
[TD="bgcolor: #FDE9D9, align: right"]7[/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]
[TD="align: right"] [/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #EAF1DD, align: right"] [/TD]
[TD="bgcolor: #EAF1DD, align: left"]In-class Essay 1[/TD]
[TD="bgcolor: #EAF1DD, align: left"]In-class Essay 2[/TD]
[TD="bgcolor: #EAF1DD, align: left"]In-class Essay 3[/TD]
[TD="bgcolor: #EAF1DD, align: left"]Total Extra Credit[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #EAF1DD, align: left"]grade[/TD]
[TD="bgcolor: #EAF1DD, align: right"]27[/TD]
[TD="bgcolor: #EAF1DD, align: right"]17[/TD]
[TD="bgcolor: #EAF1DD, align: right"]24[/TD]
[TD="bgcolor: #EAF1DD, align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="bgcolor: #EAF1DD, align: left"]grade[/TD]
[TD="bgcolor: #EAF1DD, align: right"]23[/TD]
[TD="bgcolor: #EAF1DD, align: right"]20[/TD]
[TD="bgcolor: #EAF1DD, align: right"]22[/TD]
[TD="bgcolor: #EAF1DD, align: right"]11[/TD]

</tbody>

Zelle	Formel
E6	=SUMPRODUCT(LARGE((B6:D6>=B$2:D$2)*B$3:D$3,{1;2}))

<colgroup><col style="width: 40ptpx"><col></colgroup><tbody>
</tbody>

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>

 

[HenryDCase]

Erm, not exactly (though I'm not sure I completely understand your formula). The extra credit earned isn't in the grade book. The idea is to have Excel look up the amount of extra credit, total it, and drop the lowest score.

[TABLE="width: 50"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]essay #1[/TD]
[TD="align: center"]essay #2[/TD]
[TD="align: center"]essay #3[/TD]
[TD="align: center"]total extra credit[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]17[/TD]
[/TR]
</tbody>[/TABLE]


I experimented a little and got this far:

=SUM(LOOKUP(A2,{0,16,21,26},{"0","4","7","10"}),LOOKUP(B2,{0,16,21,26},{"0","4","7","10"}),LOOKUP(C2,{0,16,21,26},{"0","4","7","10"}))

Using this formula, the final result is 21 points of extra credit. This is the correct total of the three scores; however, I need it to drop the lowest score of the three cells, as well. Unfortunately, when I place a -MIN(A2,B2,C2) at the end, it only gives 3 points of extra credit. It should give the student 17 points.

The question, then, is where do I place the -MIN formula, if I can even place that within the current formula. Or is there another way to do this I am unaware of?

 

[shift-del]

Hello Henry

{0,16,21,26},{"0","4","7","10"}

You didn't show these values before.

<tbody>
[TD="colspan: 6, align: center"]Arbeitsblatt mit dem Namen 'Tabelle3'[/TD]

[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]

[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #EAF1DD, align: right"][/TD]
[TD="bgcolor: #EAF1DD, align: left"]In-class Essay 1[/TD]
[TD="bgcolor: #EAF1DD, align: left"]In-class Essay 2[/TD]
[TD="bgcolor: #EAF1DD, align: left"]In-class Essay 3[/TD]
[TD="bgcolor: #EAF1DD, align: left"]Total Extra Credit[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #EAF1DD, align: left"]grade[/TD]
[TD="bgcolor: #EAF1DD, align: right"]27[/TD]
[TD="bgcolor: #EAF1DD, align: right"]17[/TD]
[TD="bgcolor: #EAF1DD, align: right"]24[/TD]
[TD="bgcolor: #EAF1DD, align: right"]17[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="bgcolor: #EAF1DD, align: left"]grade[/TD]
[TD="bgcolor: #EAF1DD, align: right"]23[/TD]
[TD="bgcolor: #EAF1DD, align: right"]20[/TD]
[TD="bgcolor: #EAF1DD, align: right"]22[/TD]
[TD="bgcolor: #EAF1DD, align: right"]14[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="bgcolor: #EAF1DD, align: left"]grade[/TD]
[TD="bgcolor: #EAF1DD, align: right"]18[/TD]
[TD="bgcolor: #EAF1DD, align: right"]26[/TD]
[TD="bgcolor: #EAF1DD, align: right"]23[/TD]
[TD="bgcolor: #EAF1DD, align: right"]17[/TD]

</tbody>

Zelle	Formel
E2	=SUM(LOOKUP(LARGE($B2:$D2,{1;2}),{0;16;21;26},{0;4;7;10}))

<tbody>
</tbody>

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>

 

[HenryDCase]

This works wonderfully!

But what if the cells are not in the same row?

 

[shift-del]

Hi

That doesn't make much sense to me.

 

[HenryDCase]

Nevermind! I made a quick adjustment to my gradesheet and now the formula you provided works perfectly! I needed to put zeroes in the cells with no grade until my students actually write those essays, but the formula calculates the extra credit correctly. Thank you so much for your help, shift-del!

​