Help with charting something in Excel

[Prosidius]

I have this data I would like to chart and I'm not sure what the best way to do it.  What I'm trying to achieve is figure out what shows  (Show title) are most attended (attendance), what season/time of year people most attend the shows (date &/or Season), and what type of shows people most attend (ID).  Im not an expert in excel so Im not sure how I can do this.  Can anyone help?

 

 

Thanks!

[+BudMan MVC]

difficult to grab your data and show you with it just being a pic but simple sort on the attendance column will show you which shows got most attendance.

as to the other things you want to see, you can just create another cell to add up all the winter, fall, etc. attendance. Same goes for types of shows.

If you just post your data in a csv can manipulate it for you and show you exactly how to do it.

[Dick Montage]

Pivots are your friend.

[Prosidius]

difficult to grab your data and show you with it just being a pic but simple sort on the attendance column will show you which shows got most attendance.

as to the other things you want to see, you can just create another cell to add up all the winter, fall, etc. attendance. Same goes for types of shows.

If you just post your data in a csv can manipulate it for you and show you exactly how to do it.

Sure.

 

https://docs.google.com/spreadsheet/ccc?key=0AgPXde4mKtB_dGRWZkxCWkp0LV82OExId2FZUmY4ZHc&usp=sharing

 

Hopefully that works.  :)

[+BudMan MVC]

access denied to that link.

Just attach a .csv of your data.

[Prosidius]

It wont let me upload a .csv here but I uploaded it here: http://hidemyass.com/files/e68rA/

[+BudMan MVC]

Ok - as suggested pivot tables make it easy. So you highlight your range and then insert pivot table or chart

You can then manipulate the table with what you want to see info on, change attributes of say the attendance to sum vs count, etc. etc..

You can click into any pivot table you created to edit, etc.

For you most viewed show just picking the data and then doing a custom sort on the field you want, say attendance to show you which show was most viewed, etc.

Just do a google for pivot tables and should get you started, or just in excel help search pivot tables.

See how I created the one for type of show (ID) - you could do that on show title if you had duplicate titles - but seems all show titles are unique so it would just be a same data.. As you can see the pegasus flying horse had the most - and you can then see the date on the left once you sorted, etc.

Hope that helps.

[nowen]

Thought I would post this as a side note to data analysis. Excel is great and very handy, but something I have been delving into recently is R (http://www.r-project.org/) which is a very powerful statistical language for computing and graphics. (its free, and multiplatform)

 

Believe me when I say I have very little experience with it, there are many wonderful people out there that can make a 100 line script of mine work with 2 lines (or less!) but I wanted to share it with you as its brilliant for doing the type of data analysis you seem to want to carry out.

 

If you are using Windows (looks like it) theres a free IDE called RStudio that makes life easier (http://www.rstudio.com/).

 

For example looking at your data, you can subset it and look at plots with something like:

# Read the data file
data <- read.csv("D:/Downloads/AstroBreak .csv")

#This is just to trim it as it seems the csv file had more than the data alone
data<-data[,c(1:5)]
data[,4]<-as.numeric(data[,4])
data<-data[1:28,]

#getting individual figures, but not really needed
#val_spring<-sum(data$Attendance[data$Season=="Spring"])
#val_summer<-sum(data$Attendance[data$Season=="Summer"])
#val_fall<-sum(data$Attendance[data$Season=="Fall"])
#val_winter<-sum(data$Attendance[data$Season=="Winter"])

#could make a list of the seasons and use that to group the data
seasons<-c("Spring", "Summer", "Fall", "Winter")

#initalise the list for attendance by seasons
df_seasons<-list()
#Enter the sums in the list
for (i in 1:4) {
df_seasons[i]<-sum(data$Attendance[data$Season==seasons[i]])
}
#bind the list to the seasons in a data.frame (table)
seasonal<-data.frame(cbind(Season=seasons,Attendance=df_seasons))
seasonal[,2]<-as.numeric(seasonal[,2])

#plot the Attendance per season,can make fancier graphics than just a plot

barplot(seasonal$Attendance, names.arg=seasonal$Season)

#listing the seasonal data
> seasonal
Season Attendance
1 Spring 30
2 Summer 64
3 Fall 45
4 Winter 93

These arent perfect ways to do things, but maybe you will find R and RStudio helpful tools for data analysis.

 

Also, I can highly recommend the Coursera.org course "Computing for Data Analysis" which focuses on the use of R.

 

Enjoy

 

 

 

(I am not associated with anything or one connected with R/Coursera etc, just wanted to share recent enthusiasm I have found for it)

[Prosidius]

Thanks.  Hopefully the data will help me show why attendance is down this year.  :)  

Ok - as suggested pivot tables make it easy. So you highlight your range and then insert pivot table or chart

pivot1.png

You can then manipulate the table with what you want to see info on, change attributes of say the attendance to sum vs count, etc. etc..

pivot2.png

You can click into any pivot table you created to edit, etc.

For you most viewed show just picking the data and then doing a custom sort on the field you want, say attendance to show you which show was most viewed, etc.

datasort.png

Just do a google for pivot tables and should get you started, or just in excel help search pivot tables.

See how I created the one for type of show (ID) - you could do that on show title if you had duplicate titles - but seems all show titles are unique so it would just be a same data.. As you can see the pegasus flying horse had the most - and you can then see the date on the left once you sorted, etc.

Hope that helps.

[Prosidius]

Err so question.  I want to compare the attendance numbers for each season.  Example: I want to see the attendance differential between Fall 2012 and Fall 2013.  How do I do that?  Selection those fields only gives an error.  

[+BudMan MVC]

Your data had no dates like that. You would have to filter on years, or create different tables for each year, you could graph 4 seasons per year, and have all yours years as one graph, etc.

Lots to ways to do it.

[Prosidius]

Ok, one last question that I think is similar to the last one, how do I make a multi line graph with Attendance on Y axis, and  the multiple lines being the years?  I think I have to have each year be a row then but Im not quite sure.

 

Edit: ok I created a column for each year and added the dates off to the side and filled the dates of that year that a show occured with the attendance data.  Now I have a very long X axis though. 

 

Your data had no dates like that. You would have to filter on years, or create different tables for each year, you could graph 4 seasons per year, and have all yours years as one graph, etc.

Lots to ways to do it.

[+BudMan MVC]

A line graph would work sure. So your X access is jan 1 through dec 31, Y is attendance and your data set would be by year.

Might be easier to break out your data into different sections in your spreadsheet and then enter your data sets into your graph vs using pivot tables for something like this.. Does not matter if your shows are same date or not, etc.

So you end up with something like this