How to Calculate the Average of Every 12 Cells in Excel

Microsoft Excel is a powerful tool for handling vast amounts of data, and one common task is calculating the average of every n cells. In this article, we will explore how to achieve this for every 12 cells using two different methods: OFFSET and INDEX functions. Additionally, we will discuss a trick to ensure that your formulas are easily understood by others.

Method 1: Using OFFSET

Assume your data starts in cell A1 and goes down column A. You want to calculate the average of every 12 cells.

To do this, follow these steps:

Enter the following formula in cell B1 or any other cell where you want to display the first average:

AVERAGE(OFFSET(A1, ROW(A1)-1, 0, 12, 1))

Drag the formula down in column B to calculate the average for every subsequent set of 12 cells.

Explanation:
The OFFSET function creates a reference that is a certain number of rows and columns away from a starting cell. Here, it moves down by multiples of 12 rows to calculate the average of the corresponding range.

Method 2: Using INDEX

Alternatively, you can use the INDEX function along with AVERAGE:

Enter the following formula in cell B1:

AVERAGE(INDEX(A1:A100, ROW(A1)-1, 1):INDEX(A1:A100, ROW(A1)-1 12, 1))

Replace A1:A100 with the actual last cell of your data range.

Drag the formula down as needed.

Explanation:
The INDEX function is used to define the start and end of the range for the average calculation. It returns the value of a cell in a specified row and column of a given range.

Ensuring Understandability

Even with these formulas, there might be a need for them to be more understandable, especially for those who are new to Excel. One trick is to build the formulas as text and then convert them to working formulas.

Create a formula in cell A2 that generates text like the desired formulas:

TEXT("AVERAGE(OFFSET(A1, ROW(A1)-1, 0, 12, 1))", "@")

Copy this formula across the necessary cells.

Verify that the results look exactly like the desired formulas.

Copy the cells with the formulas, and use Paste Special... > Values to convert the text to values.

Go to the Home menu, and use the Find and Select... > Select > Replace functionality. Put a in both the Find what and Replace with fields, then click the Replace All button.

This process converts the static text into functional formulas, making your workbooks more understandable to others.