Hi,
I would like to calculate the weighted average values on a matrix. This is a sample dataset:
| UserId | CritID | CritSelVal | PoolCritID | PoolSelVal |
| 1 | 3 | 0.20 | 4 | 5.00 |
| 1 | 2 | 0.14 | 5 | 7.00 |
| 1 | 2 | 0.20 | 4 | 5.00 |
| 1 | 2 | 0.14 | 3 | 7.00 |
| 1 | 1 | 0.20 | 5 | 5.00 |
| 1 | 4 | 7.00 | 5 | 0.14 |
| 1 | 1 | 0.14 | 4 | 7.00 |
| 1 | 3 | 5.00 | 5 | 0.20 |
| 1 | 1 | 5.00 | 3 | 0.20 |
| 1 | 1 | 5.00 | 2 | 0.20 |
| 1 | 4 | 5.00 | 3 | 0.20 |
| 1 | 5 | 7.00 | 2 | 0.14 |
| 1 | 4 | 5.00 | 2 | 0.20 |
| 1 | 3 | 7.00 | 2 | 0.14 |
| 1 | 5 | 5.00 | 1 | 0.20 |
| 1 | 5 | 0.14 | 4 | 7.00 |
| 1 | 4 | 7.00 | 1 | 0.14 |
| 1 | 5 | 0.20 | 3 | 5.00 |
| 1 | 3 | 0.20 | 1 | 5.00 |
| 1 | 2 | 0.20 | 1 | 5.00 |
This is the matrix view, using CritID and PoolCritID. Values in Matrix is the PoolSelVal
| | 1 | 2 | 3 | 4 | 5 |
| 1 | | 5.000 | 5.000 | 0.143 | 0.200 |
| 2 | 0.200 | | 0.143 | 0.200 | 0.143 |
| 3 | 0.200 | 7.000 | | 0.200 | 5.000 |
| 4 | 7.000 | 5.000 | 5.000 | | 7.000 |
| 5 | 5.000 | 7.000 | 0.200 | 0.143 | |
| Total | 12.400 | 24.000 | 10.343 | 0.686 | 12.343 |
I have a problem calculating the weighted average in the table below as the calculation in the row is not the same calculation in the total column
Normalised based on PoolCritID (Column) Column Total used to calculate the normalised value
Weighted average should be the average for each row excluding the 0
| | 1 | 2 | 3 | 4 | 5 | Weighted Average |
| 1 | 0.000 | 0.208 | 0.483 | 0.208 | 0.016 | 0.23 |
| 2 | 0.016 | 0.000 | 0.014 | 0.292 | 0.012 | 0.08 |
| 3 | 0.016 | 0.292 | 0.000 | 0.292 | 0.405 | 0.25 |
| 4 | 0.565 | 0.208 | 0.483 | 0.000 | 0.567 | 0.46 |
| 5 | 0.403 | 0.292 | 0.019 | 0.208 | 0.000 | 0.23 |
Thanks
