Difficult (?) average calculation

[pat m.]

I have a several rows of numbers like

Peter: 4,4,4.5,--,5 (=points in a test. The 4th test was not taken)
Mary: 5,5.5,4,4,4.5 (=the same tests, Mary took all 5 tests)

and above it, another row of numbers like

1,1,2,2,2 [the "weight" of those test points, ie. how much they 'weigh'
towards the average].

I now need to calculate the weighted average of the test points, but for
each person only the points of the tests they actually took.
First, I need to multiply each test point with the "weight" above it, then
divide by the sum of the weights for all participated tests.

So Peter would have (1*4)+(1*4)+(2*4.5)+(2*--)+(2*5) / (1+1+2+2) [as he did
not take the 4th test, we do not sum up the 4th "weight")
Mary would have (1*5)+(1*5.5)+(2*4)+(2*4)+(2*4.5) / (1+1+2+2+2) [as she took
all tests, we sum up and divide by all weights)

How can I write a formula that calculates the average for each person with
only the correct number of tests actually taken?

Thanks much for any help.
Patrick

 

[Bob Phillips]

Patrick,

Assuming weights in A1:E1, and Peter's scores in A2:E2, then try

=SUMPRODUCT($A$1:$E$1,A2:E2)/COUNTA(A2:E2)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Patrick,

Update. If you really use -- to signify no entry, then try


=SUMPRODUCT($A$1:$E$1,A2:E2)/COUNTIF(A2:E2,"<>--")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)