Weighted Average

[Gage Teacher]

How do I weight a cell in an array of cells that I want to average?
Specifically, I want to give more weight to the final exam than to the unit
tests. Sum and Sumproduct don't seem to apply.

 

[Arvi Laanemets]

Hi

The mathematical formula for weighted average is
Xaverage=SUM(Xi*Wi)/SUM(Wi)
where Xi and Wi are i's value and its weight respectively.

How the formual will look in Excel for your particular case, will depend on
how your data are organized ... is there a weight for every exam result in
separate column, or do you have some another column which allows to
calculate the weight for every entry, or is the case more complicated.

 

[Ron Coderre]

See if this example gets you headed in the right direction:

A1: Name
B1: Quiz
C1: Quiz
D1: Exam

F1: QuizAvg
G1: ExamAvg
H1: Wtd Avg
I1: QuizWt
J1: ExamWt

F2: =SUMPRODUCT(--($B$1:$D$1="Quiz")*$B2:$D2)/COUNTIF($B$1:$D$1,"Quiz")
G2: =SUMPRODUCT(--($B$1:$D$1="Exam")*$B2:$D2)/COUNTIF($B$1:$D$1,"Exam")
H2: =F2*I2+G2*J2
I2: 40%
J2: 60%

Now just enter names and grades.

Example:
Bill, 80, 80, 90
QuizAvg: 80
ExamAvg: 90
WtdAvg: 86

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro