BusinessPlanner
New Member
- Joined
- Jun 24, 2015
- Messages
- 6
Hello,
In Excel 2010 I have columns of data representing 13 period in a year (P1 - P13). These columns repeat every year and I have several years of data - so a lot of columns! Each period a number of faults is recorded in the corresponding cell.
I have a chart that shows the data from the columns in a rolling 13 period graph. In P3 for example, the graph shows values from P3 last year to P3 of the current year (see table below). Each period I have to go in to the chart and alter the series and axis to look at the next period along. So in period 4 I will need to go in and change it from P3 - P3 to P4 - P4.
I have several dozen of these graphs and so I would like to set them up to automatically move along and look at the correct periods when new period data is added. I suspect there may be a way to do this using the OFFSET function but I cant find any examples where rolling data is displayed.
Any assitance or advice would be appreciated!
Thanks!
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Year[/TD]
[TD] Y1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period[/TD]
[TD]P1[/TD]
[TD]P2[/TD]
[TD]P3[/TD]
[TD]P4[/TD]
[TD]P5[/TD]
[TD]P6[/TD]
[TD]P7[/TD]
[TD]P8[/TD]
[TD]P9[/TD]
[TD]P10[/TD]
[TD]P11[/TD]
[TD]P12[/TD]
[TD]P13[/TD]
[TD]P1[/TD]
[TD]P2[/TD]
[TD]P3[/TD]
[TD]P4[/TD]
[TD]P5[/TD]
[TD]P6[/TD]
[TD]P7[/TD]
[TD]P8[/TD]
[TD]P9[/TD]
[TD]P10[/TD]
[TD]P11[/TD]
[TD]P12[/TD]
[TD]P13[/TD]
[/TR]
[TR]
[TD]Faults[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
In Excel 2010 I have columns of data representing 13 period in a year (P1 - P13). These columns repeat every year and I have several years of data - so a lot of columns! Each period a number of faults is recorded in the corresponding cell.
I have a chart that shows the data from the columns in a rolling 13 period graph. In P3 for example, the graph shows values from P3 last year to P3 of the current year (see table below). Each period I have to go in to the chart and alter the series and axis to look at the next period along. So in period 4 I will need to go in and change it from P3 - P3 to P4 - P4.
I have several dozen of these graphs and so I would like to set them up to automatically move along and look at the correct periods when new period data is added. I suspect there may be a way to do this using the OFFSET function but I cant find any examples where rolling data is displayed.
Any assitance or advice would be appreciated!
Thanks!
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Year[/TD]
[TD] Y1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Period[/TD]
[TD]P1[/TD]
[TD]P2[/TD]
[TD]P3[/TD]
[TD]P4[/TD]
[TD]P5[/TD]
[TD]P6[/TD]
[TD]P7[/TD]
[TD]P8[/TD]
[TD]P9[/TD]
[TD]P10[/TD]
[TD]P11[/TD]
[TD]P12[/TD]
[TD]P13[/TD]
[TD]P1[/TD]
[TD]P2[/TD]
[TD]P3[/TD]
[TD]P4[/TD]
[TD]P5[/TD]
[TD]P6[/TD]
[TD]P7[/TD]
[TD]P8[/TD]
[TD]P9[/TD]
[TD]P10[/TD]
[TD]P11[/TD]
[TD]P12[/TD]
[TD]P13[/TD]
[/TR]
[TR]
[TD]Faults[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]