# Excel - AVERAGE Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

So i'm taking the average of a range of 30 cells from another sheet. For example, My formula in Sheet 2 is =average(Sheet1!B5:B35) and when I drag that formula down one cell I get =average(Sheet1!B6:B36) but I want it to do =average(Sheet1!B36:B65).
Solved by A. A. in 16 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 17/09/2018 - 08:01
Hi
Excelchat Expert 17/09/2018 - 08:01
Welcome to Excel Chat.
User 17/09/2018 - 08:01
Hi
Excelchat Expert 17/09/2018 - 08:02
You want to get the average range continuous after the previous range?
User 17/09/2018 - 08:02
Yes, so the next range begins where the previous cell's range ended
Excelchat Expert 17/09/2018 - 08:02
Excelchat Expert 17/09/2018 - 08:03
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert 17/09/2018 - 08:03
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert 17/09/2018 - 08:03
I would like to take an example in the preview.
Excelchat Expert 17/09/2018 - 08:03
Hope, you are able to see it.
User 17/09/2018 - 08:03
I can see the blank document preview
Excelchat Expert 17/09/2018 - 08:04
User 17/09/2018 - 08:04
ok
Excelchat Expert 17/09/2018 - 08:05
I have added the sample text in the preview. Hope, you are able to see it.
User 17/09/2018 - 08:05
I can see a column of 1s and 2s
Excelchat Expert 17/09/2018 - 08:06
Ok. Great. So, for the first range, you will get 1 and for the second range, you will get 2.
User 17/09/2018 - 08:07
Can you see the two formulas I entere?
Excelchat Expert 17/09/2018 - 08:08
Yes. I am checking.
User 17/09/2018 - 08:08
So I want the range in my second cell to take the average of 8:10
Excelchat Expert 17/09/2018 - 08:10
I have created the formula in the preview. Please give me few minutes, while working on.
Excelchat Expert 17/09/2018 - 08:10
Please check in E1 and E2 cells.
Excelchat Expert 17/09/2018 - 08:11
=average(offset(B\$1,5+(row(B1)-1)*30,0,30,1))
Excelchat Expert 17/09/2018 - 08:11
I am going explain the formula in detail.
Excelchat Expert 17/09/2018 - 08:11
There are 30 elements in the range always.
Excelchat Expert 17/09/2018 - 08:12
=offset(reference, rows offset, columns offset, height, width)
Excelchat Expert 17/09/2018 - 08:12
width = 1 (One column)
Excelchat Expert 17/09/2018 - 08:12
height = 30 (30 rows)
Excelchat Expert 17/09/2018 - 08:13
Cell Reference:B\$1 (Always gets the range from B1, So, fixed row 1)
Excelchat Expert 17/09/2018 - 08:13
In order to get the range start point, i have used row function.
Excelchat Expert 17/09/2018 - 08:13
Row(B1) = 1
Excelchat Expert 17/09/2018 - 08:13
Row (B2)=2
Excelchat Expert 17/09/2018 - 08:14
So, i am multiplying with 30 after deducting 1 from row.
Excelchat Expert 17/09/2018 - 08:14
So, it will be 5 + (1-1)*30 = 35
Excelchat Expert 17/09/2018 - 08:15
5+(1-1)*30=5
Excelchat Expert 17/09/2018 - 08:15
5+(2-1)*30=35
User 17/09/2018 - 08:15
If you wanted to reference another sheet, would you reference it after offset?
Excelchat Expert 17/09/2018 - 08:15
So, i can change the starting row using above logic.
Excelchat Expert 17/09/2018 - 08:16
Yes. that is right.
Excelchat Expert 17/09/2018 - 08:16
Offset(Sheet2!B\$1,.....
User 17/09/2018 - 08:16
Excelchat Expert 17/09/2018 - 08:17
Great to hear.
Excelchat Expert 17/09/2018 - 08:17
Thanks for coming to Excelchat. Feel free to leave any comments or feedback. Have a nice day.
User 17/09/2018 - 08:17
Thank you, you too

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.