Member_2_79173
asked on
How to graph monthly totals of gym visits from data/times of visits
I have data showing the dates and times I have visited various gyms to workout. The data are in the form
Date, Time, Gym name
I want to make a bar graph with months on the x axis and visits per month on the y axis. I also want to make a pie chart showing the number of visits during one hour periods of the day. Example: 5:00 am to 5:59 am, 6:00 am to 6:59 am and so on. I also want to make a pie chart showing the number of times I visited each location. I've mocked up what this might look like by using some fake data. That Excel file is called Example.xlsx. The actual data file is called Gym visits.xlsx. I've changed the gym names to colors for privacy.
I would like someone to actually make the finished product but to also explain what was done so I can learn more about Excel.
Thanks,
donander
Example.xlsx
Gym-Visits.xlsx
Date, Time, Gym name
I want to make a bar graph with months on the x axis and visits per month on the y axis. I also want to make a pie chart showing the number of visits during one hour periods of the day. Example: 5:00 am to 5:59 am, 6:00 am to 6:59 am and so on. I also want to make a pie chart showing the number of times I visited each location. I've mocked up what this might look like by using some fake data. That Excel file is called Example.xlsx. The actual data file is called Gym visits.xlsx. I've changed the gym names to colors for privacy.
I would like someone to actually make the finished product but to also explain what was done so I can learn more about Excel.
Thanks,
donander
Example.xlsx
Gym-Visits.xlsx
ASKER
Sorry, I don't see the attached files. Am I missing something?
Thanks,
donander
Thanks,
donander
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well, this is simply fantastic. You've made exactly what I was looking for and you did it in less than an hour! I will also check out the links you provided to learn how to do this myself.
I do have a couple of questions. If I want to now change the colors back to the gym names in the original data, do I need to make the source dynamic so the chart legends will reflect this? I will want to add more data so I'll have to make the source dynamic for that in any case.
I noticed that in the Count of Gym time chart (the top one) if I display all the years there are some months that do not show up on the x axis. I realize this is because there were 0 (zero) visits in those months. But I would like those months to show up because I want to have the visual impact of those months where I did not visit the gym at all. Is that possible?
Thanks!
Donander
I do have a couple of questions. If I want to now change the colors back to the gym names in the original data, do I need to make the source dynamic so the chart legends will reflect this? I will want to add more data so I'll have to make the source dynamic for that in any case.
I noticed that in the Count of Gym time chart (the top one) if I display all the years there are some months that do not show up on the x axis. I realize this is because there were 0 (zero) visits in those months. But I would like those months to show up because I want to have the visual impact of those months where I did not visit the gym at all. Is that possible?
Thanks!
Donander
LOL, actually, I did it in under ten minutes. That's how powerful pivot tables are!
If you replace the real gym names in the source data and then refresh the pivot table, that should do the trick for the names.
For every month you did not visit the gym, enter one row of data with a date from that month. Leave the other two columns empty. Then make sure to use the count of visits on either the time or the gym name, which will then be zero for that month.
The easiest way to make the data source dynamic is to turn it into an Excel Table: select any cell in the source data and then click Insert > Table. By default, Excel will call the table "Table1". Every new row of data you enter will automatically expand the table, if you enter new data in the next empty row. Then change the pivot sources to be "Table1" and refresh the pivot tables after adding new data.
Cheers, teylyn
If you replace the real gym names in the source data and then refresh the pivot table, that should do the trick for the names.
For every month you did not visit the gym, enter one row of data with a date from that month. Leave the other two columns empty. Then make sure to use the count of visits on either the time or the gym name, which will then be zero for that month.
The easiest way to make the data source dynamic is to turn it into an Excel Table: select any cell in the source data and then click Insert > Table. By default, Excel will call the table "Table1". Every new row of data you enter will automatically expand the table, if you enter new data in the next empty row. Then change the pivot sources to be "Table1" and refresh the pivot tables after adding new data.
Cheers, teylyn
ASKER
teylyn provided a solution to my question on the first try and it was excellent! If I could give more than 500 points I would do so for this answer. teylyn is truly an expert.
Thanks,
donander
Thanks,
donander
the easiest way to analyse this data is to use pivot charts. Attached please find three charts with pivot tables as the underlying data. Since the hours chart had so many slices, I changed it to a horizontal bar chart, which is much easier to read than a pie chart. Also, I've applied a filter to show only years 2011 and 2012 in the top chart. You can click on the filter buttons in the chart to change the filters.
To get started with Pivot Tables, take a look at
http://peltiertech.com/Excel/Pivots/pivotstart.htm
If the data source changes, i.e. if you add more rows of data to the sheet, you may want to make the source for the pivot tables dynamic. How to do that is described here:
http://www.contextures.com/xlPivot01.html
cheers, teylyn