P
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
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