Complex weighted average calculation

[jtklepp]

I have a spreadsheet with the following data (sample):

What I am trying to do is the following: I want to calculate the average Conversion rate for each Unit per Month for the ABC Core Product

Customers will only have one Core Product, either ABC or something else. If the customer does not have ABC, the data should not be included in counting the average.

For the relevant data, the average conversion rate needs to be weighted based on the number of Users per Unit. So as an example, in the data shown ACME in October has 612 Users for Unit LAX, while Company has 96, so ACME's conversion for October counts more than 6 times as much to figure out the overall average for October for LAX.

I have tried with multiple pivot tables to eventually get to what I need, but it is time consuming and there are thousands of lines of data, with 100s of Customers and 100s of Unit id's, so this is not very practical. Any help in the right direction would be much appreciated.

 

[Asala42]

In line with the pivot table approach, I would be to add a new field (ConversionWT) that multiplies the user count * the conversion rate. Then I'd add a calculated pivot field WgtAvg = ConversionWT/Users that you can toss into the values section of the pivot table.

 

[jtklepp]

Indeed, that does work well, and is more elegant than the solution I came up with. Thank you very much!