HFBoards

Go Back   HFBoards > General Hockey Discussion > By The Numbers
Mobile Hockey's Future Become a Sponsor Site Rules Support Forum vBookie Page 2
By The Numbers Hockey Analytics... the Final Frontier. Explore strange new worlds, to seek out new algorithms, to boldly go where no one has gone before.

Could an Excel Expert Help?

Reply
 
Thread Tools
Old
06-16-2015, 06:55 PM
  #1
Larry Hoover
Registered User
 
Larry Hoover's Avatar
 
Join Date: Sep 2012
Posts: 972
vCash: 500
Could an Excel Expert Help?

I have a data file that looks like this: http://imgur.com/prw4DQF
And it has data for over 200+ players overall.
I'm trying to simplify this data into a table the following (pick = overall, btw): http://imgur.com/X2BywRZ

Is there a fast way to do this? I know how to manually do everything, but I was hoping to automate it somewhat at least.

Larry Hoover is offline   Reply With Quote
Old
06-16-2015, 08:02 PM
  #2
Kismet
Registered User
 
Join Date: Apr 2010
Location: Winnipeg
Country: Canada
Posts: 183
vCash: 50
Quote:
Originally Posted by Larry Hoover View Post
I have a data file that looks like this: http://imgur.com/prw4DQF
And it has data for over 200+ players overall.
I'm trying to simplify this data into a table the following (pick = overall, btw): http://imgur.com/X2BywRZ

Is there a fast way to do this? I know how to manually do everything, but I was hoping to automate it somewhat at least.
If I understand what you are looking for, it seems like a pivot table would do the job. Are you at all familiar with them?

Kismet is offline   Reply With Quote
Old
06-16-2015, 08:29 PM
  #3
Larry Hoover
Registered User
 
Larry Hoover's Avatar
 
Join Date: Sep 2012
Posts: 972
vCash: 500
Quote:
Originally Posted by Kismet View Post
If I understand what you are looking for, it seems like a pivot table would do the job. Are you at all familiar with them?
No, I am not unfortunately. But I'm going to look into them.

Larry Hoover is offline   Reply With Quote
Old
06-16-2015, 09:36 PM
  #4
Kismet
Registered User
 
Join Date: Apr 2010
Location: Winnipeg
Country: Canada
Posts: 183
vCash: 50
Quote:
Originally Posted by Larry Hoover View Post
No, I am not unfortunately. But I'm going to look into them.
There's a lot of good information online that should be able to help you out, but let me know if you get stuck.

From what I can tell, you'll likely want to use Overall for your row label (this will group all 1s together, all 2s together and so forth). You may want a second row label for Pos if you want to separate F and D.

Your values may also include both of those fields, as counts (ie Count of Overall will show you a count for how many 1s there are, etc.). You'll then have values for Average of GP, Average of PPG and Sum of NHL Player - you can then get a percentage for the last (Sum of NHL Player/Count of Overall).

Kismet is offline   Reply With Quote
Old
06-17-2015, 09:30 AM
  #5
Larry Hoover
Registered User
 
Larry Hoover's Avatar
 
Join Date: Sep 2012
Posts: 972
vCash: 500
Quote:
Originally Posted by Kismet View Post
There's a lot of good information online that should be able to help you out, but let me know if you get stuck.

From what I can tell, you'll likely want to use Overall for your row label (this will group all 1s together, all 2s together and so forth). You may want a second row label for Pos if you want to separate F and D.

Your values may also include both of those fields, as counts (ie Count of Overall will show you a count for how many 1s there are, etc.). You'll then have values for Average of GP, Average of PPG and Sum of NHL Player - you can then get a percentage for the last (Sum of NHL Player/Count of Overall).
Wow, this would have saved me so much time. I ended up using an AverageIf's formula. I'm going to play around with this and it may end up saving me.

Larry Hoover is offline   Reply With Quote
Old
06-17-2015, 10:54 AM
  #6
Mathletic
Registered User
 
Mathletic's Avatar
 
Join Date: Feb 2002
Location: St-Augustin, Québec
Country: Canada
Posts: 13,638
vCash: 500
You could use the VLOOKUP function and insert an IF function in it. Would do the trick easily. You can look on youtube for tutorials. Plenty of examples.

Mathletic is online now   Reply With Quote
Old
06-17-2015, 09:15 PM
  #7
AD1066
HFBoards Sponsor
 
AD1066's Avatar
 
Join Date: Sep 2011
Posts: 3,790
vCash: 500
If you're looking for more player data by any chance, I once pulled everything from 1979-2013. Player, team, goals, assists, points, last season, etc. If you're interested, the file is attached.

As for the question I would also suggest a pivot table. There's a ton you can do with them and it's well worth the 10 minutes to watch a tutorial.
Attached Files
File Type: xlsx NHL Draft Data 1979-2013.xlsx‎ (1,005.9 KB, 7 views)

AD1066 is offline   Reply With Quote
Old
06-18-2015, 07:47 AM
  #8
Larry Hoover
Registered User
 
Larry Hoover's Avatar
 
Join Date: Sep 2012
Posts: 972
vCash: 500
Quote:
Originally Posted by AD1066 View Post
If you're looking for more player data by any chance, I once pulled everything from 1979-2013. Player, team, goals, assists, points, last season, etc. If you're interested, the file is attached.

As for the question I would also suggest a pivot table. There's a ton you can do with them and it's well worth the 10 minutes to watch a tutorial.

Wow, that's awesome...But how do you ensure that the data updates itself?

Larry Hoover is offline   Reply With Quote
Old
06-18-2015, 08:38 AM
  #9
Doctor No
HFB Partner
 
Doctor No's Avatar
 
Join Date: Oct 2005
Posts: 4,131
vCash: 50
Good question - my biggest problems with pivot tables in work products are (1) the size of the table can change around on you if you aren't paying attention, and (2) the data doesn't always refresh. We typically use them for diagnostics, but replace them with INDEX/MATCH, V/HLOOKUPS, or the dreaded OFFSETs.

With that said, you can right click within the pivot table and you'll get an option to "Refresh".

__________________
The Goaltender Home Page (http://hockeygoalies.org) - Preserving Goaltender History since 1994
Doctor No is offline   Reply With Quote
Old
06-18-2015, 02:12 PM
  #10
AD1066
HFBoards Sponsor
 
AD1066's Avatar
 
Join Date: Sep 2011
Posts: 3,790
vCash: 500
Quote:
Originally Posted by Larry Hoover View Post
Wow, that's awesome...But how do you ensure that the data updates itself?
Unfortunately that's just a snapshot of a point in time; everything was copied into Excel.

I think for automatic updating it would need to be linked to a database or web crawler. Sorry I can't help more.

AD1066 is offline   Reply With Quote
Old
06-18-2015, 02:16 PM
  #11
Molon labe
 
Molon labe's Avatar
 
Join Date: Apr 2015
Posts: 384
vCash: 500
Quote:
Originally Posted by Larry Hoover View Post
Wow, that's awesome...But how do you ensure that the data updates itself?
You would have to have a database that will feed into a server that constantly grabs updated stats. You would have to find a site that would allow you to connect to their database and that usually will not happen unless you go through a bunch of legal jargon. May want to see if there are any RSS feeds that you can pipe into a MySQL DB that will compile the stats dynamically.

Molon labe is offline   Reply With Quote
Old
06-18-2015, 02:22 PM
  #12
Doctor No
HFB Partner
 
Doctor No's Avatar
 
Join Date: Oct 2005
Posts: 4,131
vCash: 50
Quote:
Originally Posted by Benner View Post
You would have to have a database that will feed into a server that constantly grabs updated stats. You would have to find a site that would allow you to connect to their database and that usually will not happen unless you go through a bunch of legal jargon. May want to see if there are any RSS feeds that you can pipe into a MySQL DB that will compile the stats dynamically.
This thread:
http://hfboards.hockeysfuture.com/sh....php?t=1596119

May help with that.

Doctor No is offline   Reply With Quote
Reply

Forum Jump


Bookmarks

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off



All times are GMT -5. The time now is 02:24 PM.

monitoring_string = "e4251c93e2ba248d29da988d93bf5144"
Contact Us - HFBoards - Archive - Privacy Statement - Terms of Use - Advertise - Top - AdChoices

vBulletin Copyright ©2000 - 2015, Jelsoft Enterprises Ltd.
HFBoards.com is a property of CraveOnline Media, LLC, an Evolve Media, LLC company. ©2015 All Rights Reserved.