When working with large numerical datasets, it is a frequent need to calculate the average number of data. We can do this in Excel in many ways. The **OFFSET** function gives a tremendous dynamic opportunity to average in Excel. In this article, I will show you 3 practical examples to average by using the** OFFSET **function in Excel.

## How to Find Average with OFFSET Function in Excel: 3 Examples

Say, you are given a dataset of 6 employeesâ€™ sales in the first 4 months of a year. Now, you want to calculate the average sales for individual months and also the average sales of individual employees for a total of 4 months. Follow the article below to accomplish this easily and dynamically by using **OFFSET** and **AVERAGE** functions.

### 1. Calculate Average of Any Number of Rows/Columns Using the OFFSET Function in Excel

You can calculate the average sales for individual months and also the average sales of individual employees by following the steps below.

**📌 Steps:**

- First and foremost, click on cell
**G5**Â and insert the following formula.

`=AVERAGE(OFFSET(C5,0,0,1,4))`

- Hit the
**Enter**key.

- As a result, you will get the average sales for the employee,
*Adam*. - Place your cursor in the bottom right position of the cell.
- Drag the
**Fill Handle**below upon its arrival.

- As a result, you will get all employeesâ€™ average sales.
- Now, to calculate the monthly average sales, click on cell
**C11**and insert the formula below. Then, hit the**Enter**key.

`=AVERAGE(OFFSET(C5,0,0,6,1))`

- Use the
**Fill Handle**feature rightward to copy the same formula for all the other months.

Thus, you will get the average using the **OFFSET** function in Excel and the result will look like this.

**Read More: **How to Calculate VLOOKUP AVERAGE in Excel

### 2. Combine COUNT Function with OFFSET to Calculate Average of Last N Number of Rows/ Columns in Excel

When using the **OFFSET** function, you can calculate the average for n number of rows or columns dynamically. You will have to combine **the COUNT function** here too.

**2.1 Average of Last N Rows**

Say, you want to calculate the average for the last two employees’ sales for 4 months. Go through the following steps to accomplish this.

**📌 Steps:**

- First, put your
*N*value as 2 in cell**C13**. - Afterward, click on cell
**B13**and write the following formula in the formula bar.

`=AVERAGE(OFFSET(C4,COUNT(C4:C10)-C13+1,0,C13,4))`

- Following, press the
**Enter**key.

As a result, you will get the average sales of the last two employees of the dataset for all 4 months. You can change the value of *N* as per your requirement and the **AVERAGE** function will automatically return you the exact result.

**2.2 Average of Last N Columns**

Again, you might need to calculate the average for all employees but only for the last two months. Follow the steps below to accomplish this.

**📌 Steps:**

- At the very beginning, fix your
*N*value as 2 in cell**C13**. - Insert the following formula in cell
**B13**and press the**Enter**key.

`=AVERAGE(OFFSET(B5,0,COUNT(B5:F5)-C13+1,6,C13))`

As a result, you will get the last two months’ average sales for all employees. If you want to calculate the average for any other number of months, change the *N* value accordingly. The formula will automatically return the result.

### 3. Calculate Average for Every N Number of Rows/Columns Using OFFSET and ROW Functions

You might need to calculate average sales in a consecutive manner. That is, you need to calculate average sales for every *N* number of rows or columns. Keep following the article to achieve this.

**3.1 Average of Every N Number of Rows**

Say, you need to calculate average sales for every two employees of the dataset. You can accomplish this by using **the ROW function** with the **OFFSET** function.

**📌 Steps:**

- Initially, click on cell
**C13**and insert the following formula.

`=AVERAGE(OFFSET($C$5,(ROW()-ROW($C$13))*2,,2,))`

- Then, hit the
**Enter**key.

- As a result, you will get the
*January*average sales for*Adam*and*Mathew*. - Afterward, for all the other employees, drag your
**Fill Handle**below to achieve your target in the same manner.

- For the other months, dragging the
**Fill Handle**wonâ€™t work properly due to absolute reference. - So, for the
*February*sales average for every two employees, click on cell**D13**and insert the following formula.

`=AVERAGE(OFFSET($D$5,(ROW()-ROW($D$13))*2,,2,))`

- Subsequently, press the
**Enter**key.

- Repeat the same procedures for all the other cells and change your formula every month.

As a result, you will get all the averages for every two rows every month. And, the outcome should look like this.

**Read More:** How to Average Every Nth Row in Excel

#### 3.2 Average of Every N Number of Columns

Similarly, you might need to calculate the average for every employee every two months. Go through the steps below to achieve this by combining **the COLUMNS functionÂ **with the **OFFSET** function.

**📌 Steps:**

- First and foremost, click on cell
**C13**and insert the following formula.

`=AVERAGE(OFFSET($C$5,,(COLUMNS($C$13:C13)-1)*2,,2))`

- Subsequently, hit the
**Enter**key.

- Afterward, drag your
**Fill Handle**rightward to repeat the same result for the next two months.

- Being an absolute reference, the formula won’t work properly if you drag the
**Fill Handle**below for other employees. - For the next employee, click on cell
**C14**and write the formula below in the formula bar.

`=AVERAGE(OFFSET($C$6,,(COLUMNS($C$14:C14)-1)*2,,2))`

- Following, hit the
**Enter**key.

- Repeat the same dragging approach and change your formula accordingly for every individual employee.

As a result, you will get all employeesâ€™ average sales every two months. And, the output should look like this.

## 💬 Things to Remember

- The
**OFFSET**function will show*#VALUE!*error, if any argument is invalid, like doesnâ€™t include any cell or something like this. - The
**OFFSET**function will show*#REF!*error, if you refer to any cell that is outside of the spreadsheet.

**Download Practice Workbook**

You can download our practice workbook from here for free!

## Conclusion

So, in this article, I have shown you 3 practical examples to average with the **OFFSET** function in Excel. I suggest you read the full article carefully and practice accordingly. I hope you find this article helpful and informative. You are very welcome to comment here if you have any further questions or recommendations.

## Related Articles

- How to Calculate Average in Excel Excluding 0
- How to Average Values Greater Than Zero in Excel
- How to Use VBA Average Function in Excel
- Calculate the Average of an Array with VBA
- How to Add Average Line to Excel Chart

**<< Go Back to Calculate Average in Excel | How to Calculate in ExcelÂ | Learn Excel**