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.

## Download Practice Workbook

You can download our practice workbook from here for free!

## 3 Examples of Calculating Average with OFFSET Function in Excel

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 the 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 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**. - Subsequently, insert the following formula.

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

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

- As a result, you will get the average sales for the employee, Adam.
- Afterward, place your cursor in the
**bottom right**position of the cell. - Subsequently, drag the
**fill handle below**upon its arrival.

- As a result, you will get all employees’ average sales.

- Now, for calculating the monthly average sales, click on
**cell C11**and insert the formula below.

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

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

- Afterward, 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 would look like this.

**Read More: ****How to Calculate Average of Multiple Columns in Excel (6 Methods)**

### 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 elbow to accomplish this.

**📌 Steps:**

- At the very beginning, fix your N value as 2 in
**cell C13**. - Following, 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.

**Read More: ****How to Average Every Nth Row in Excel (3 Ways)**

**Similar Readings**

**How to Determine Triple Exponential Moving Average in Excel****Calculate Percentage above Average in Excel (3 Easy Ways)****How to Calculate Exponential Moving Average in Excel****Generate Moving Average in Excel Chart (4 Methods)****How to Calculate VLOOKUP AVERAGE in Excel (6 Quick Ways)**

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

Now, 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. Follow the steps below to do this.

**📌 Steps:**

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

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

- Subsequently, 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.

- But, 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.

#### 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 elbow 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.

- But, being in absolute reference, the formula won’t work properly if you drag the
**fill handle****below**for other employees. - So, 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.

**Read More: How to Average a Column in Excel (7 Easy Methods)**

## 💬 Things to Remember

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

## 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.

And, visit **ExcelDemy** to learn about many more Excel problem solutions, tips, and tricks. Thank you!

## Related Articles

**How to Calculate Daily Average from Hourly Data in Excel****Calculate Average Growth Rate in Excel (3 Easy Methods)****How to Calculate Average of Averages in Excel (with Easy Steps)****Calculate Average Only for Cells with Values in Excel****How to Calculate Average of Text in Excel (2 Ways)****Average Attendance Formula in Excel (5 Ways)****How to Calculate Centered Moving Average in Excel (2 Examples)**