setup offset in time periods in Excel
Q86. Our financial Year is divided into 13 periods of 4 weeks each. I enter weekly YTD data. In Excel, how can I set up Worksheet to get Period data automatically?
For example, how can I set up Col F to autopopulate based on values in Col C?
To do it in Excel, here is the answer:
a) Consider cell C1 as reference. Every 4th value from reference has to be picked up from the Weekly YTD Sales column. This is a classic case of picking every nth row value in a column.
Enter the formula =OFFSET($C$1,(ROW(F2)-1)*4,0) as shown below for Period 1. Drag the formula all the way down for the remaining periods.
The second argument provides 4 row increments. The "-1" is introduced to account for the "YTD Sales" Title row.