How to Find Average with OFFSET Function in Excel (3 Examples)

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.

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

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

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

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.

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

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

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

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