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.

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.

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

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

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

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.

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

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

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

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