Quote:
Originally Posted by saw7988
I want to generate 7 different line graphs, each line corresponding to a day of the week. So for example a Monday (or any other day) graph would extract every 7th number from the column and plot it. So for the data:
1, 2, 3, 4, 5, 6, 7, 1.1, 2.1, 3.1, 4.1, 5.1, 6.1, 7.1
I want my Monday graph to show 1 then 1.1.
I'm on Office 2010. This might not be available for 2007, but you can try. Pretty sure it's available in Office 2013.
If your data is just a list in Col A, then I'd add a title "Value" for A1, then add column B called "Date", and C called "Weekday". You can generate B easily by adding one date and dragging it up/down, and then C starts at C2 =TEXT(B2,"DDDD") and runs on all rows.
Select columns A, B, C go to Insert > Pivot Table dropdown > Pivot Chart, click OK (you can play around with this popup once you know what it's doing).
On the right you'll have the list of columns at the top and quadrants on the bottom. Add Weekday to Report Filter. Date to Axis Fields. Value to Values. Values will be added as "Count of Values" which is not what you want, so click the down arrow on it > Value Field Settings > change to Sum.
Now you'll get a chart with each value separated by date, which you can filter by week in the top left corner of the chart.
You can cut and paste the chart into another worksheet and change the cosmetics. At the bottom left you can filter date ranges, etc. Play around with it to your liking. Pivots rule.