How to get proper “weighted” average of one column's data based on number values found in another column? [duplicate]

I'm trying to get the true average "position" in Google (as per Google Search Analytics data) for specific search query filters (e.g., all queries containing "xyz")

Where I'm getting hung up is that I need to take into account the total number of Impressions for each in the Impressions column (column D) to get a proper average.

Here is my current formula:

``````=AVERAGEIF(\$B\$3:\$B,"*"&B1&"*",\$F\$3:\$F)
``````

How can I create a proper average?

• The OP flagged this as a duplicate (not sure why that didn't reflect in the close reason). – jonsca Feb 9 '17 at 0:26

1 Answer

I would create a new column to hold either 0 or the amount of queries depending on if they contained the word see link, multiply that by the the average position for the query, then divide by the new column

I dropped a new sheet in your spreadsheet giving an example.

• Hi thanks for sharing this! Seems to get a result, but I wonder if perhaps there is a way of doing this with the extra columns? – Drewdavid Feb 8 '17 at 23:02