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
I can help you with that problem.
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
I am adding sample data. Please give me few minutes.
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
Perfect, this was super helpful
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.
