Home |
Search |
Today's Posts |
#1
|
|||
|
|||
3 month rolling chart
I've looked at some examples but found myself lost. I have a sheet that
shows overtime by department (those being the rows) by month (columns), and wanted to make a chart that will show a rolling three months for each department, so it would show August, July, and June, and next month Sept, Aug, July. I know I have to use the Name function and Offset in some terms but am somewhat lost. The department names start in A7 and go down, the months start B4 and go across. Any help would be appreciated. |
#3
|
|||
|
|||
Perhaps this example would help, which shows how to keep a chart pointed at the last
12 months of data: http://peltiertech.com/Excel/Charts/DynamicLast12.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Shanin wrote: I've looked at some examples but found myself lost. I have a sheet that shows overtime by department (those being the rows) by month (columns), and wanted to make a chart that will show a rolling three months for each department, so it would show August, July, and June, and next month Sept, Aug, July. I know I have to use the Name function and Offset in some terms but am somewhat lost. The department names start in A7 and go down, the months start B4 and go across. Any help would be appreciated. |
#4
|
|||
|
|||
Maybe I need to reformat my data. I understand how those examples work on
the way the data is set up in those examples but my data is kind of flip flopped. I have a chart now for each month showing the overtime. It has all our departments across the bottom (23 of them), and the hours up the side. This is a bar graph. They want a bar graph in the same format, but for each department have it show the past three months. All the examples I see always have the months going up and down in the data section, not across, which is how ours is set up. Our column headings are the months Jan-Dec, and it has a total column at the end, our row headings run up and down. The data itself is not going to grow itself, it will always be Jan-Dec of that year, but it will only have data in it up through the current month. I think I need a vacation, I'm over thinking this and just can't seem to fit any logic in right now. "Shanin" wrote: I've looked at some examples but found myself lost. I have a sheet that shows overtime by department (those being the rows) by month (columns), and wanted to make a chart that will show a rolling three months for each department, so it would show August, July, and June, and next month Sept, Aug, July. I know I have to use the Name function and Offset in some terms but am somewhat lost. The department names start in A7 and go down, the months start B4 and go across. Any help would be appreciated. |
#5
|
|||
|
|||
You need to change how you define the names and your current data
layout will work. I started with A2:D6 containing: J F M D1 6 9 12 D2 8 12 16 D3 10 15 20 D4 12 18 24 The cells E2:M2 had the other month headings with N being a totals column. Then, the required names become: Mth1 =OFFSET(Sheet1!$B$3,0,COUNT(Sheet1!$3:$3)-3-1,COUNTA(Sheet1!$B: $B)-1,1) Mth2 =OFFSET(Mth1,0,1) Mth3 =OFFSET(Mth1,0,2) Plot these 3 named formulas as the 3 series in a clusterd column chart. -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Maybe I need to reformat my data. I understand how those examples work on the way the data is set up in those examples but my data is kind of flip flopped. I have a chart now for each month showing the overtime. It has all our departments across the bottom (23 of them), and the hours up the side. This is a bar graph. They want a bar graph in the same format, but for each department have it show the past three months. All the examples I see always have the months going up and down in the data section, not across, which is how ours is set up. Our column headings are the months Jan-Dec, and it has a total column at the end, our row headings run up and down. The data itself is not going to grow itself, it will always be Jan-Dec of that year, but it will only have data in it up through the current month. I think I need a vacation, I'm over thinking this and just can't seem to fit any logic in right now. "Shanin" wrote: I've looked at some examples but found myself lost. I have a sheet that shows overtime by department (those being the rows) by month (columns), and wanted to make a chart that will show a rolling three months for each department, so it would show August, July, and June, and next month Sept, Aug, July. I know I have to use the Name function and Offset in some terms but am somewhat lost. The department names start in A7 and go down, the months start B4 and go across. Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Activating a Chart object | Charts and Charting in Excel | |||
Urgent Chart Assistance | Charts and Charting in Excel | |||
Urgent Chart Questions | Excel Discussion (Misc queries) | |||
Urgent Chart Assistance Requested | Excel Discussion (Misc queries) | |||
Z Chart i.e. top rolling annual bottom rolling monthly middle cum. | Excel Worksheet Functions |