SERPPatch
Loading search intelligence workspace…
SERPPatch
Loading search intelligence workspace…
Calculate your true organic Google ranking health without unweighted arithmetic distortion. Copy-paste this verified Google Sheets & Excel formula or use our free interactive online calculator.
To calculate impression-weighted average position in Google Sheets, multiply each keyword rank by its impression volume, sum those products together using SUMPRODUCT, and divide by total impressions. This reflects the true search position experienced by your actual market audience:
Where Column B contains Google Search Console Position numbers and Column C contains Impressions.
Configure column letters to match your Google Search Console export. Switch syntax between US and European formats.
=SUMPRODUCT(B2:B, C2:C) / SUM(C2:C)| # | Query (Col A) | Position (B) | Impressions (C) | Weighted Product |
|---|---|---|---|---|
| 2 | content decay audit | 2.3 | 14,200 | 32,660 |
| 3 | how to detect traffic drop seo | 4.8 | 3,800 | 18,240 |
| 4 | seo traffic decay formula template | 12.1 | 450 | 5,445 |
| 5 | free rank tracker no signup | 68 | 25 | 1,700 |
| Flawed Arithmetic Average: 21.80 | True Weighted Average Position: 3.14 | |||
Occurs when the sum of impressions is 0 (or empty rows). Wrap the entire formula in =IFERROR(formula, 0) to safely output 0 instead of an error.
Occurs if ranges have unequal lengths (e.g. B2:B500 vs C2:C520) or if text headers are accidentally included in the data range.
GSC exports sometimes format numbers as strings. Select your column, click Format › Number › Automatic, or multiply the column by 1.
Navigate to Performance → Search results in your Google Search Console property. Filter to your desired page or site-wide date range (e.g. Last 28 Days). Click Export in the top right corner and choose Google Sheets.
Queries with fewer than 5 impressions often represent single-search bot anomalies or accidental misspellings that introduce noise without driving revenue. Apply a sheet filter to remove queries with < 5 impressions for maximum statistical integrity.
Notice how your unweighted average position might say 42.0, while your impression-weighted rank is 3.8. Presenting weighted metrics to clients and stakeholders prevents panic over irrelevant long-tail queries.
SERPPatch connects directly to your Google Search Console via read-only OAuth, calculating impression-weighted rankings and content decay automatically for $49/mo.