Weighted Average

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

Working on a spreadsheet that is averaging school grades using a weighted
average.

I started building formulas (long way most likely) and wondered if there was
a function or combination of functions.

Win XP/Excel 2002

Thank you

Pat Joram
(e-mail address removed)
 
Pat - It might help if you post your current formula(s).
Also, a description or example of the table data.
 
Here we go:
In B1:F1 I have the weighting for 5 pieces of work (homework, lab, essay,
quiz....); the numbers are 10%,15%, 25%, 30%,20% (hope sum=1) - they could
be 0.1, 0.15, 0.25, 0.3 and 0.2.
In A2 I have name of first student; in B2:F2 I have her grades;
In G2 enter =SUMPRODUCT($B$1:$F1,B2:F2) - that works out Mary's mark
Copy down the column (double click the fill handle for speed) and we work
out ever students' mark.
(PS the EDU in your email address was a clue as to what you needed! Hope I
am right)
Best wishes
 
Hi

Modify your table so, that every score has his own weigth. Like this:
Score Wt
68 15.0%
94 20.0%
100 20.0%
98 10.0%
87 7.5%
94 7.5%
95 6.7%
93 6.7%
96 6.6%

The formula will now be like:
=SUMPRODUCT((A2:A10)*(B2:B10))
or
={SUM((A2:A10)*(B2:B10))} (array formula)
(These figures are giving 91.3 as average)
 
Back
Top