I have product review data. The reviews are out of 5 stars and I know how many there are (ie quantity of reviews). I'm trying to calculate a weighted average of the reviews because I'm trying to distinguish the reviews by different product categories. If I simply do a pivot table average, it doesn't take into account the number of reviews. This is wrong:
So far, I have read and tried a lot with sumproduct and average.weighted but these seem to be better at calculating weighted averaged when you have the actual original values themselves (vs. the averages + quantity).
How does one calculate weighted average of averages: