**how to calculate a weighted average position**when analyzing PPC reports with Excel or Tableau. I guess that’s what happens when you let a former art major do math, but weighted average position is just one of those must-have PPC tools.

You’ve probably ran into a post or two over the last couple of years about using pivot tables to analyze your PPC data. Some of those posts may have been kind enough to include instructions for how to calculate a weighted average position. This is important because once you start aggregating your data in a pivot table you **can’t average your Average Position** or you will get a skewed perspective.

## How to Calculate a Weighted Average Position in Excel

Regardless of the report you need to analyze, you will add an “**AvgPos X Impressions**” column. This column will be used to create a ‘Calculated Formula’ in your pivot table.

Once you’ve got your pivot table going, you will need to create calculated metric by navigating here: **PivotTable Tools > Options > Formulas > Calculated Field**.

Now name your new ‘Weighed Avg Pos” metric and use the following formula:

**=’AvgPos*Impressions’ /Impressions**

Now you will have an accurate representation of average position regardless of the level of detail in your pivot table.

## How to Calculate a Weighted Average Position in Tableau

For those of you fortunate enough to have Tableau for analyzing your PPC data, calculating a weighted average position is just as easy as in Excel. Navigate to the ‘Calculated Field’ tool by **right-clicking in the ‘Measures’ area and clicking ‘Create Calculated Field…”**

Again name your new metric ‘Weighted Avg Pos’ and use the following Tableau formula:

**sum([Avg Position]*[Impressions])/sum([Impressions])**

** **

** **

** **

** **

** **

There, I’ve written it down. Now I can quit bothering my co-workers and just reference this post in the future. Those of you with better memories may not need this reference, but for the rest of you feel free to bookmark this page.** **

** **

“I guess that’s what happens when you let a former art major do math…”

I’m sorry lol. Have you worked in display?

Thank you very much, I was trying to figure out how Google uses its formula

to calculate (Average Position) – PS: You’re not the only former Art Major who

landed in PPC! – The advantage is I can get beyond the 1st decimal..which is a bonus.

Searchengineman

man, thanks for this tip.. i’ve been looking around for the formula and found your blog. BIG thanks

Hey Chad,

Thanks for this helpful tutorial. Even though your pivot table says “Sum of WeightedAvgPos” once you have followed these steps it is all good?

Thanks