To compute average position for Adwords or Bing Ads reports manually, you take (impressions) X (Avg. Position) and sum that, then divide by the sum of impressions.
- In a cell in Excel, type in =SUMPRODUCT(
- The SUMPRODUCT formula is asking for 2 inputs from you. In our situation, we want to highlight two 'arrays'. Don't let the word 'array' scare you... it just means highlight two sets of ranges. So we will want to highlight the impressions range and the average position range.
- Close the formula. At this point, you should have something that looks like this: =SUMPRODUCT(E8:E10,K8:K10)
- Now we need to DIVIDE by the same range we used for impressions. So put a division sign, then type SUM(E8:E10) (for example).
- Now you have the formula needed to compute average position in Excel. The end result should look something like this: =SUMPRODUCT(E8:E10,K8:K10)/SUM(E8:E10)
This formula weights the average position number by the number of impressions for a given row. We know that if your ad shows, you incur an impression. An impression means the ad will show up somewhere on the page in some position. So we know when an impression occurs, so does the instance of an ad position. That's why we want to weight the average position by the amount of impressions.