OFFSET Formula Question

[Danni2004]

Hi
Below is a simple example of my data. The bottom shows a table that pulls
averages from another table above. Could someone help me create an OFFSET
formula that will pull six rows down from the prior period's average?
You can see that Per 01 averages for Load 1, 2 & 3 just equal row 5 of the
table. If I just do an OFFSET for six rows down from D18, it pulls from cell
D25. If I refer to the cell in the primary table where Per 01's average is
(D5), I won't be able to just copy the formula down through subsequent
periods.
Hope this question was clear.

A B C D
1 Period 01
2 Load 1 Load 2 Load 3
3 Reg 1 231 225 234
4 Reg 2 245 236 244
5 Average 238 231 239
6
7 Period 02
8 Load 1 Load 2 Load 3
9 Reg 1 228 230 239
10 Reg 2 243 227 233
11 Average 236 229 236
12
13 Period 03
14 Load 1 Load 2 Load 3
15 Reg 1 246 241 232
16 Reg 2 242 226 240
17 Average 244 234 236
18
19 Averages
20 Load 1 Load 2 Load 3
21 Period 01 238 231 =E5
22 Period 02
23 Period 03

 

[Lars-Åke Aspelin]

Hi
Below is a simple example of my data. The bottom shows a table that pulls
averages from another table above. Could someone help me create an OFFSET
formula that will pull six rows down from the prior period's average?
You can see that Per 01 averages for Load 1, 2 & 3 just equal row 5 of the
table. If I just do an OFFSET for six rows down from D18, it pulls from cell
D25. If I refer to the cell in the primary table where Per 01's average is
(D5), I won't be able to just copy the formula down through subsequent
periods.
Hope this question was clear.

A B C D
1 Period 01
2 Load 1 Load 2 Load 3
3 Reg 1 231 225 234
4 Reg 2 245 236 244
5 Average 238 231 239
6
7 Period 02
8 Load 1 Load 2 Load 3
9 Reg 1 228 230 239
10 Reg 2 243 227 233
11 Average 236 229 236
12
13 Period 03
14 Load 1 Load 2 Load 3
15 Reg 1 246 241 232
16 Reg 2 242 226 240
17 Average 244 234 236
18
19 Averages
20 Load 1 Load 2 Load 3
21 Period 01 238 231 =E5
22 Period 02
23 Period 03

Well, you can change your formula in cell B21 to

=OFFSET(B$5, 6*(ROW(B21)-ROW(B$21)), 0)

and copy it to the right and down as far as needed

But beware of that you may run into trouble if you insert more rows in
your table so that there is no longer 6 rows between the averages of
each period.

Hope this helps / Lars-Åke

 

[Danni2004]

Thank you Lars-Ã…ke. It worked great.
Can you explain how ROW works in this formula?

 

[Lars-Åke Aspelin]

ROW returns the (first) row number for the parameter.
If there is no parameter given, ROW() just returns the row number of
the cell where the formula is.

In this case ROW(B21) returns 21 as the row number for cell B21 is 21
Also ROW(B$21) returns 21 but the difference here is that when you
drag the formula down, the parameter changes in the first place but
not in the other.
If you look at the formula in cell B22 you wil find that you have
ROW(B22), which will return 22, and ROW(B$21), which stillreturns 21.
This means that the parameter to the OFFSET function changes for each
row.
In row 21 you have 6*(21-21) which is 0.
In row 22 you have 6*(22-21) which is 6.
In row 23 you have 6*(23-21) which is 12.
and so on.
The effect of this ist that you get the data from each 6:th row as
required.

Lars-Åke

 

[Roger Govier]

Hi

As an alternative to Lars Offset formula, you could use the non-volatile
Index function
=INDEX(B:B,6*ROWS($1:1)-1)