Calculation Issues

How to Calculate an Average Around a Reference Cell in Excel

Emma BrownEmma Brown Oct 28, 2026 870 views

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.

How to Calculate an Average Around a Reference Cell in Excel
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.
Before you start

Ensure your dataset contains continuous numerical values above and below the reference cell to avoid calculation errors or skewed averages.

Solution 1Recommended

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.

1
Select the output cell

Click on the empty cell where you want the final average to be displayed.

2
Enter the formula

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.

3
Execute the calculation

Press Enter. The formula will calculate the total of the 5 cells, remove C7's value, and divide the remainder by 4.

Use the SUM Formula to Subtract the Reference Cell
Adjust the Divisor: Make sure to change the number '4' to match the exact number of surrounding cells you are averaging. For example, if you want 3 cells above and 3 below, use 6 as the divisor.
Easy Excel Formulas with WPS Office

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. 1. Open your spreadsheet: Launch WPS Office and open your .xlsx data file in WPS Spreadsheet.
  2. 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. 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.
Fully compatible with Microsoft Excel formulas and .xlsx file formats.Lightweight and fast, even when handling large datasets with complex dynamic ranges.Built-in function autocomplete makes typing formulas like OFFSET error-free.
microsoft office alternative - wps office

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.