Excel Average Question

LoveIsUnity

Hey everybody. I'm currently calculating my student's grades, and I could save a lot of time if I could set up an average function in Excel. Basically, I have a spreadsheet in which the x axis is the various assignments (so, the numerical grades they've received) and the y axis is the student's names. Is there a way to set up one function that will average various portions of the grades together for each student? I have no problems setting up an average for one student, but is it possible to set up one equation and then have that work for all of my students individually? If I have to create a new equation 30 times it will be easier to just calculate the grades by hand.

Mushiwulf

You should be able to set it up once and then copy and paste it onto the entire group.

LoveIsUnity

How exactly would that work? Just set it up for one person, and copy and paste? Wouldn't that simply carry over the values from the first person's grades?

Gork

As long as you don't anchor the values in your formula and you are consistent with where the values are in relation to each other, excel will automatically use the appropriate cells.

Mushiwulf

Ok, so you have the names running down somewhere near the first column and then following each name you have that student's score in each assignment. In an empty cell to the right of the last assignment, start a click and drag and pull it back across all the assignment scores for 1 student. Now right click on that cell and do the formula magic. There should be some kind of shortcut for averaging. Once you have that setup, you should be able to right click the finished cell and select copy. Now click and drag a selection in that same column that takes care of all the students. Right click and paste. That should work.

Like Gork said, everything needs to be consistent.

Gdiguy

Just to be more explicit: if you have cell D1 that has =AVERAGE(A1:C1), and you copy that cell and paste it to, say, D2, it will automatically now be =AVERAGE(A2:C2). Similarly, if you copy it to F2, it will now be =AVERAGE(C2:E2) (it keeps the structure the same, but just moves the entire average area to correspond to where you've pasted)

If you want to keep a value static during the copying, you add a $ before the value... so if you copy D1=AVERAGE($A1:C1) to cell, say, Z1, now it will be Z1=AVERAGE($A1:Y1) (it still tries to move as many values as it can, but the $A forces the A to stay the same)... you can similarly have $A$1 or A$1, where only the part with a $ will stay consistent

LoveIsUnity

Thank you all so much. That was exactly what I wanted to know. I guess it's pretty obvious I teach English and not statistics.

KVW

If you were really brave, you could set up weighted averages, as well, depending on if all assignments hold different weights or not. Like term project worth 50%, homework assignments worth 10% each and an essay worth 30% or something and it would take the weighted average with that.