Apologies if this is fairly straight forward. I'm looking to obtain a weighted average for categorical scores in a table based on two variables: 1) the number of users, 2) the number of transactions.
So as an example:
| category | score | number_of_users | number_of_transactions |
|---|---|---|---|
| A | 4 | 25000 | 478 |
| B | 7 | 768 | 878 |
| C | 9 | 100 | 90 |
| D | 6 | 19000 | 208 |
If I wanted to obtain the weighted average based on number of users, it would be:
(4 x 25000) + (7 x 768) + (9 x 100) + (6 x 19000) = 220,276 / 25000 + 768 + 100 + 19000 = 44,868
220,276 / 44,868 = 4.90
However, how would I go about finding a weighted average that incorporates number of users and number of transactions? Ideally I'd like the transactions to have a greater bearing- E.G. in the table above, Category B's score would be more important than Category A's score due to the higher number of transactions.