Jump to content



Photo

Help with charting something in Excel


  • Please log in to reply
12 replies to this topic

#1 Prosidius

Prosidius

    Geek.

  • Joined: 09-August 04
  • Location: Milwaukee Wisconsin
  • OS: Windows 8 Pro
  • Phone: Nexus 4

Posted 14 November 2013 - 04:23

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?

 

Capture.JPG

 

Thanks!




#2 +BudMan

BudMan

    Neowinian Senior

  • Tech Issues Solved: 106
  • Joined: 04-July 02
  • Location: Schaumburg, IL
  • OS: Win7, Vista, 2k3, 2k8, XP, Linux, FreeBSD, OSX, etc. etc.

Posted 14 November 2013 - 15:59

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.

#3 +Nik L

Nik L

    Where's my pants?

  • Tech Issues Solved: 2
  • Joined: 14-January 03

Posted 14 November 2013 - 16:11

Pivots are your friend.



#4 OP Prosidius

Prosidius

    Geek.

  • Joined: 09-August 04
  • Location: Milwaukee Wisconsin
  • OS: Windows 8 Pro
  • Phone: Nexus 4

Posted 14 November 2013 - 18:26

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....ZHc&usp=sharing

 

Hopefully that works.  :)



#5 +BudMan

BudMan

    Neowinian Senior

  • Tech Issues Solved: 106
  • Joined: 04-July 02
  • Location: Schaumburg, IL
  • OS: Win7, Vista, 2k3, 2k8, XP, Linux, FreeBSD, OSX, etc. etc.

Posted 14 November 2013 - 19:57

access denied to that link.

Just attach a .csv of your data.

#6 OP Prosidius

Prosidius

    Geek.

  • Joined: 09-August 04
  • Location: Milwaukee Wisconsin
  • OS: Windows 8 Pro
  • Phone: Nexus 4

Posted 14 November 2013 - 20:08

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



#7 +BudMan

BudMan

    Neowinian Senior

  • Tech Issues Solved: 106
  • Joined: 04-July 02
  • Location: Schaumburg, IL
  • OS: Win7, Vista, 2k3, 2k8, XP, Linux, FreeBSD, OSX, etc. etc.

Posted 15 November 2013 - 20:52

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.

#8 nowen

nowen

    Neowinian

  • Joined: 14-December 01

Posted 15 November 2013 - 22:25

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)



#9 OP Prosidius

Prosidius

    Geek.

  • Joined: 09-August 04
  • Location: Milwaukee Wisconsin
  • OS: Windows 8 Pro
  • Phone: Nexus 4

Posted 16 November 2013 - 20:39

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

attachicon.gifpivot1.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..

attachicon.gifpivot2.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.

attachicon.gifdatasort.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.



#10 OP Prosidius

Prosidius

    Geek.

  • Joined: 09-August 04
  • Location: Milwaukee Wisconsin
  • OS: Windows 8 Pro
  • Phone: Nexus 4

Posted 16 November 2013 - 21:54

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.  



#11 +BudMan

BudMan

    Neowinian Senior

  • Tech Issues Solved: 106
  • Joined: 04-July 02
  • Location: Schaumburg, IL
  • OS: Win7, Vista, 2k3, 2k8, XP, Linux, FreeBSD, OSX, etc. etc.

Posted 19 November 2013 - 13:19

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.

#12 OP Prosidius

Prosidius

    Geek.

  • Joined: 09-August 04
  • Location: Milwaukee Wisconsin
  • OS: Windows 8 Pro
  • Phone: Nexus 4

Posted 20 November 2013 - 18:03

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.



#13 +BudMan

BudMan

    Neowinian Senior

  • Tech Issues Solved: 106
  • Joined: 04-July 02
  • Location: Schaumburg, IL
  • OS: Win7, Vista, 2k3, 2k8, XP, Linux, FreeBSD, OSX, etc. etc.

Posted 20 November 2013 - 19:19

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

examplelinechart.png