Menu
I would like to know how to add a weighted average in my pivot table. In fact, I need to do the following calculation: SUM(col1 * col2)/SUM(col2).
I tried to do it by using the calculated field option but when I enter my formula, I just have the following result as an output: SUM((col1 * col2)/col2) which is equal to SUM(col1).
391
To calculate the weighted average of a data with a Pivot Table, we can add a column to our source data as an intermediate calculation. This is because Excel doesn't provide a function in the Pivot Table that automatically calculates the weighted average.
To find a weighted average, multiply each number by its weight, then add the results. If the weights don't add up to one, find the sum of all the variables multiplied by their weight, then divide by the sum of the weights.
You will need 1 calculated field, and 1 helper column
Helper Column
col3=col1*col2
Calculated field:
CF=Col3/Col1
If you try to do the helper column as a calculated field, it will sum col1 and col2, then multiply them together which results in a meaningless answer
128
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
