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