Graphing 12 month rolling data

[tcalahan]

I have researched and read multiple threads related to graphing rolling averages per month. I've setup my own but I cannot get it to graph correctly. The x axis will be the days of the month ie. 1-31. The y axis is obviously the sales volume. The legend should be a line graph for each individual month sales. I want the graph to reflect the current month and previous 12 full months.

I used the file user rockycj started for another user in a different thread labeled, "Excel Line Chart - 12 month chart moving with dates ". It was very helpful and I'm trying to get this completed before my deadline. If anybody could take a look and let me know, I would greatly appreciate the help. I have attached the file for reference.

[Andy Pope]

You will need to create 12 pairs of named ranges. I have done 2 for you.

[tcalahan]

Ok, that really helped me get things moving. I have developed the spreadsheet further, but ran into another snag. I would like to have one graph with multiple selection criteria that pulls in the exact data.

The problem is I don't have enough space to record the super long IF/THEN statement to put together the named range. What is the best way to achieve the graphing capability that I desire?

I have attached the updated file for your reference.

[arthurbr]

Hi tcalahan,
could you please attach the file again? It seems to have dissapeared somewhere on the www. ?

[tcalahan]

I can't get the file below 100kb, without making the file irrelevant. I'm at 120kb right now.

[arthurbr]

I can't get the file below 100kb, without making the file irrelevant. I'm at 120kb right now.

Part of the file would be OK, and you can zip it if needed

[tcalahan]

Ok, I got the file size down but I had to delete 90% of the data. I left the graph tab so you can get a sense of what I'm trying to accomplish. The data for each division has been deleted, but basically I had a tab for each division listing sales and margin identical to the tabs that are listed.

The goal is to select the graph criteria in cells c1-c3 and the graph will update depending on the selections. I haven't really messed with the user defined range in c3. I'm just trying to get the rolling 13 months to correspond with the selection of "rolling 13 months" and then selecting the divsion and graph type. Cell C3 is used only if C1 is labeled as user defined. Cells C2 and C3 are used regardless of the criteria in C1. Let me know what you think.

[tcalahan]

Attached is the original file with all the supporting data. I don't normally have to zip files. Learn something new everyday. Thanks for the help...

[arthurbr]

Sorry Tcalahan, got to go.
I'll pick up the thread tomorrow if nobody could help.

Cheers

[BDannemiller]

Greeting Andy/arthurbr, my first posting here. Think I have a similar issue at tcalahan.

Am using Microsoft Excel from the Office Professional 2003 suite of applications. Need a formula or narrative on how to arrange the data and formulas (if more than one is required) so as to show progress.

Am trying to track progress for completing specified tasks for 11 months of the fiscal year. The 12th month (Sept) is a non-production month. Current data-set (derived from other worksheets in the workbook) is as shown:

Month: Oct-08 Nov-08 Dec-08 Jan-09 Feb-09 Mar-09 Apr-09 May-09 Jun-09 Jul-09 Aug-09
Planned: 6 8 4 0 2 5 10 10 10 10 4
Actual: 8 16 x x x x x x x x x
(if the data above is garbled, I can send file)

As noted above, in Oct and Nov we exceeded the planned requirement. Dec thru Aug show no data yet as the months are not complete.

I would like to be able show completion as a match to the requirement and then to carry the over-production number into the subsequent month's actual row. For whatever reason, I cannot figure out how to do this (or even if it can be done). If it includes an off-page or out of range computation that will not show, that's fine. I would then be able to do other computations off of the numbers.

Here's hoping you guys can help. WR-