Time Trend Chart

[jsboss]

I'm trying to create a chart that will show the average number of customers at a business. Here are the characteristics I'd like in the chart:

* Average number of men, women, and children in a specified hour during the day
* Trend lines that show the average number of men, women, and children
* X-Axis shows hour of the day, 0-23
* Y-Axis shows number of men, women and children in the store
* Chart automatically incorporates new data as it's entered
* Different charts are generated for different locations
* Chart can be copied and pasted into PowerPoint for briefings and presentations

A few complicating factors:

* Measurement won't be consistent (some days there will be 10 records, some days there won't be any)
* Times won't be consistent (some days all the measurements will occur in the morning and some days they'll all occur at night)

I've attached some sample data, so hopefully it will make a bit more sense when you look at that. I've only got one entry for each hour, and one location, for the example.

Thanks in advance!

[teylyn]

Hello,

the answer to that question could fill a book, or at least a few chapters. I suggest you break it down into smaller chunks.

A good start is to make the source data into an Excel Table by clicking Insert > Table. That will take care of incorporating new data.

Next, you can use insert Pivot table and create a pivot table that can summarize your data by location and hour.
Drag the time into the rows and Men, Women and Children columns into the values.
Right-click a time in the pivot table and select Group. Group by hours only and unselect any other setting.

From this you can create a chart and add trendlines.

You can add slicers for the location and select the location in the slicer.

see attached.

cheers, teylyn