How to Calculate an Average Around a Reference Cell in Excel
Question details
The user needs to calculate the average of a specific number of cells located immediately above and below a reference cell, while excluding the value of the reference cell itself.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating a localized or running average around a specific data point without skewing the result with the data point's own value.
- Observed behavior
- The goal is to sum the surrounding cells (e.g., two above and two below) and divide by the total number of included cells, omitting the central reference cell.
Ensure your dataset contains continuous numerical values above and below the reference cell to avoid calculation errors or skewed averages.
Use the SUM Formula to Subtract the Reference Cell
This is the most straightforward method. It sums the entire surrounding range and subtracts the reference cell before dividing by the number of surrounding cells.
By utilizing basic arithmetic within Excel, you can capture a continuous range that includes the target cell, subtract the target's value out of the total, and then divide by the remaining number of cells.
Click on the empty cell where you want the final average to be displayed.
Assuming C7 is your reference cell and you want the two cells above and two below, type the formula =(SUM(C5:C9)-C7)/4 into the formula bar.
Press Enter. The formula will calculate the total of the 5 cells, remove C7's value, and divide the remainder by 4.

Use the OFFSET Function for a Dynamic Range
This method is useful if you want to dynamically reference cells relative to your target without hardcoding the exact range addresses.
Use WPS Spreadsheet to Calculate Averages Effortlessly
WPS Spreadsheet provides seamless support for all Excel formulas, including SUM, AVERAGE, and OFFSET, allowing you to perform complex data analysis efficiently without changing your workflow.
- 1. Open your spreadsheet: Launch WPS Office and open your .xlsx data file in WPS Spreadsheet.
- 2. Input the formula: Click on your desired result cell and type =(SUM(C5:C9)-C7)/4 directly into the cell or formula bar.
- 3. Calculate and analyze: Press Enter to instantly view your surrounding average. You can easily drag the fill handle to apply this logic to other cells.

Frequently Asked Questions
Can I use the AVERAGE function directly to exclude the reference cell?
Yes, you can use the AVERAGE function by selecting multiple non-adjacent ranges separated by a comma. For example, =AVERAGE(C5:C6, C8:C9) will average the two cells above and below C7 without including C7. This is often simpler than using SUM and dividing manually.
What if there are blank cells or text in the surrounding range?
If you use the =(SUM(range)-cell)/count method, the hardcoded divisor (e.g., 4) will assume all 4 cells have valid numbers, which could skew the average if any are blank. In cases with blank or text cells, it is better to use =AVERAGE(C5:C6, C8:C9) because the AVERAGE function automatically ignores empty cells and text, adjusting the count accordingly.
How do I calculate the average if the reference cell is at the very top or bottom of the dataset?
If the reference cell is at the boundary of your data, you won't have enough cells above or below it to complete the standard formula. You must manually adjust the formula's range or the OFFSET references to account for the missing cells, and be sure to update the divisor to match the actual number of cells being summed.




