Weighting (?) columns

[Rick]

I want to use my new Excel v.X to calculate student's grades. I
understand there is a formula for "weighting" each column so that,
e.g., Quiz #1 is worth 10% of final grade, midterm is worth 25%, etc.
Excel Help is worthless here, and I am no mathematician. Does this
involve a formula including "=" entered in the top cell of each
column? Is this done as the last step before actually calculating
final grade? In layman's terms, please. THANKS!

 

[J.E. McGimpsey]

I want to use my new Excel v.X to calculate student's grades. I
understand there is a formula for "weighting" each column so that,
e.g., Quiz #1 is worth 10% of final grade, midterm is worth 25%, etc.
Excel Help is worthless here, and I am no mathematician. Does this
involve a formula including "=" entered in the top cell of each
column? Is this done as the last step before actually calculating
final grade? In layman's terms, please. THANKS!

Here's one way:

Say you have students listed from A2:A40, and grades from B22:

A B C D
1 Quiz 1 Midterm Final
2 Anne 75 80 93
3 Bill 85 84 86
....
40 Zeke 40 64 66

Add the weightings in, say, row 42:

42 Weights 10% 25% 65%

(making sure they add to 100%). Then in Column E, enter:

E2: =SUMPRODUCT(B22, $B$42:$D$42)

and copy down to E40.


Note: The positioning of the weights doesn't matter as long as the
number of weights is the same as the number of grades. You could put
it in a column if you wished.

Note 2: The "$"s are important - when you copy, XL will adjust
column and row references that don't have "$" in front (relative
references), but will leave those that do (absolute references) as
is.