How To: Calculate Weighted Average

Greg

Greg

Founder

How To: Calculate Weighted Average

When you’re looking at some campaign performance data in Exel or a Google Spreadsheet and you want to know your the mean number of impressions or clicks delivered across campaigns, it’s very easy to use the built-in AVERAGE() function

However, when looking at metrics such as CTR or CPC, it’s important to remember that AVERAGE() will not give you an accurate result. This is due to some campaigns contributing more or less to the overall results, and using AVERAGE() of all your CTRs will lead to outliers skewing the result. In the example sheet below Campaign 10 is skewing the mean, despite having served a lot fewer impressions than the rest. Therefore, we want to weight our average to take into account differences in campaign delivery

Weighted Average

It works as follows:

  1. First, you need to calculate the SUMPRODUCT() of your impressions and CTR. For other cases, make sure to use the measure of how much each campaign should count towards the average (Impressions) and the metric you want to average (CTR). This is done by multiplying impressions by CTR for each row of data and adding the results of all those calculations together
  2. Secondly, SUM() your impressions
  3. Finally, divide the result from Step 1 by the result of Step 3 and this will give you the average CTR weighted by the number of impressions served by each campaign