Calculating Grades in Numbers

Question

Level 1

4 points

Calculating Grades in Numbers

Freshman year of college I started using Numbers to keep track of my grades and assignments for my classes. Now that I'm a junior and things are getting a bit more complicated, I wanted to make it so that my spreadsheet is also more sophisticated. I want it to be able to calculate the final exam grade necessary to get a certain letter grade. The only problem is that Numbers doesn't seem to have an equation solver like its Microsoft or even freeware counterparts. I've seen examples of people getting around this issue, but I'm still pretty confused on how to apply it to my situation. The picture below shows the function I'm using right now to make everything work. Any help is greatly appreciated!

MacBook Pro, macOS Sierra (10.12.6)

Posted on Sep 6, 2017 3:13 PM

Reply

Answer 1

Sep 7, 2017 1:15 PM in response to mjperry96

You do not need a solver if the only outstanding grade is the final exam.

Since the final average is the result multiplying each grade times the weight, then summing into a numerator. Then setting the denominator to the total weight.

if you had Five grades with five weights, then the average is:

A = ( (G1*W1) + (G2*W2) + (G3*W3) + (G4*W4) + (G5*W5) ) / TOTAL_WEIGHT

TOTAL_WEIGHT = W1 + W2 + ... + W5

so if you rearrange (and assuming that G5 is the final grade and W5 is the weight)

A*TOTAL_WEIGHT = (G1*W1) + (G2*W2) + (G3*W3) + (G4*W4) + (G5*W5)

A*TOTAL_WEIGHT - ( (G1*W1) + (G2*W2) + (G3*W3) + (G4*W4) ) = G5*W5

So...

G5 = ( A*TOTAL_WEIGHT - ( (G1*W1) + (G2*W2) + (G3*W3) + (G4*W4) ) ) /W5

I suggest using something like this:

Make sure the table is named "Grading Distribution"

the first row is a header row

the last row is a footer row

enter your actual grade in column B (which should, generally, be no more than the points assigned to the category)

enter the possible points for each category in column C,

D2=100×B2÷C2

this is shorthand for... select cell D2, then type (or copy and paste from here) the formula:

=100×B2÷C2

To fill down, select cell D2, copy

select cells D2 thru D7, paste

enter the weight in column E

For column F

F2=B2×E2÷C2

G2=IF(B2="", 0, 1)×E2

H2=F2

To fill down, select cell F2 thru H2, copy

select cells F2 thru H7, paste

Now add a second table as shown and make sure it is named "Current Calculated Average":

B2=100×SUM(Grading Distribution::F2:F6)÷SUM(Grading Distribution::G2:G6)

B3=100×SUM(Grading Distribution::F2:F7)÷SUM(Grading Distribution::G2:G7)

enter your desired final average (between 0 and 100). Hopefully you always want to enter a 100 here 😉

In the table "Grading Distribution" change the formula in cell H8:

H8=Current Calculated Average::B4

Also change the formula in cell H7

H7=MIN(H$8−SUM(H2:H6), E7)

now go back to the table "Current Calculated Average"

B5=Grading Distribution::C7×Grading Distribution::H7÷Grading Distribution::E7&" out of "&Grading Distribution::C7&" points"

Reply