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.

In article <[email protected]>,
[email protected] says...
> 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.
>
For some examples see
Dynamic Charts
http://www.tushar-mehta.com/excel/ne...rts/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

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.

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.

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 <[email protected]>,
[email protected] 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.
>