If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
OFFSET Formula Question
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 |
#2
|
|||
|
|||
OFFSET Formula Question
On Tue, 5 Aug 2008 07:29:01 -0700, Danni2004
wrote: 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 |
#3
|
|||
|
|||
OFFSET Formula Question
Thank you Lars-Ã…ke. It worked great.
Can you explain how ROW works in this formula? "Lars-Ã…ke Aspelin" wrote: On Tue, 5 Aug 2008 07:29:01 -0700, Danni2004 wrote: 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 |
#4
|
|||
|
|||
OFFSET Formula Question
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 On Tue, 5 Aug 2008 08:57:02 -0700, Danni2004 wrote: Thank you Lars-Åke. It worked great. Can you explain how ROW works in this formula? "Lars-Åke Aspelin" wrote: On Tue, 5 Aug 2008 07:29:01 -0700, Danni2004 wrote: 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 |
#5
|
|||
|
|||
OFFSET Formula Question
Hi
As an alternative to Lars Offset formula, you could use the non-volatile Index function =INDEX(B:B,6*ROWS($1:1)-1) -- Regards Roger Govier "Danni2004" wrote in message ... Thank you Lars-Ã…ke. It worked great. Can you explain how ROW works in this formula? "Lars-Ã…ke Aspelin" wrote: On Tue, 5 Aug 2008 07:29:01 -0700, Danni2004 wrote: 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 |
Thread Tools | |
Display Modes | |
|
|