2016 Michigan Teams by County

[Ed Law]

This is the most up-to-date 2016 team numbers by county. It also shows where the district events were. The nice thing about this map is it is linked to the official FiM master team spreadsheet. It will be a lot easier to update the map in the future.

[cbale2000]

Originally Posted by Ed Law

Attachment 21009
This is the most up-to-date 2016 team numbers by county. It also shows where the district events were. The nice thing about this map is it is linked to the official FiM master team spreadsheet. It will be a lot easier to update the map in the future.

Out of curiosity, is this sort of data available going back a few years? I'd be interested in seeing if an animated graphic could be made that would show the growth of teams over time by area as this does.

[Christopher149]

Originally Posted by cbale2000

Out of curiosity, is this sort of data available going back a few years? I'd be interested in seeing if an animated graphic could be made that would show the growth of teams over time by area as this does.

Unfortunately the map keeps changing, and it doesn't look like there is a 2015 map (but http://frclinks.frclinks.com/t/MI-USA/2015 has location info). But here are:

• 2011
• 2012
• 2013
• 2014
• 2016

[orangelight]

I made a Michigan team map using the data from the First website that shows which events teams go to without the state championship. Some of the team location data doesn't seem to be correct (They are in a lake).

[jnicho15]

Originally Posted by orangelight

I made a Michigan team map using the data from the First website that shows which events teams go to without the state championship. Some of the team location data doesn't seem to be correct (They are in a lake).
Attachment 21010

The team in Lake Michigan looks to be this one from Beaver Island.

[Richard Wallace]

Great work, Ed!

Looks like Kalamazoo Central HS would be a good location for a new district sometime soon, based solely on geographic distribution of teams. 6002 ZooBOTix started there this past season, but I don't see any CD accounts with that team number. Anyone here know someone on that team?

[Christopher149]

Originally Posted by jnicho15

The team in Lake Michigan looks to be this one from Beaver Island.

One in Lake Huron is from Mackinac Island, and the other does seem to have a slightly incorrect location (they belong a touch further north).

[Ed Law]

Originally Posted by orangelight

I made a Michigan team map using the data from the First website that shows which events teams go to without the state championship. Some of the team location data doesn't seem to be correct (They are in a lake).
Attachment 21010

I am curious exactly where you get the team location data from.

[orangelight]

Originally Posted by Ed Law

I am curious exactly where you get the team location data from.

The firstinspires.org team and event search page returns lots of values about the team including the lat and lon. http://es01.usfirst.org/teams/_search

I'll see if I can find my Michigan team data set
EDIT: Here http://pastebin.com/HVeY9KJh

[Michael Kaurich]

Originally Posted by Ed Law

Attachment 21009
This is the most up-to-date 2016 team numbers by county. It also shows where the district events were. The nice thing about this map is it is linked to the official FiM master team spreadsheet. It will be a lot easier to update the map in the future.

Is there a way you can share sheet for other regions to use? I would love to do the same thing with Maryland

It is something we have in spreadsheet forms but have never really put into a map.

[Ed Law]

Originally Posted by Michael Kaurich

Is there a way you can share sheet for other regions to use? I would love to do the same thing with Maryland

It is something we have in spreadsheet forms but have never really put into a map.

There is no magic spreadsheet that you can program to create that map. It is just hours of hard work. Here is basically what you need to do.
1) You need a list of all zip codes in your area of interest (state/district/region)
2) You need the corresponding city for each of the zip codes. Some have more than one zip code.
3) You need a table to show which county each city belongs to.
4) Then you get all the teams in your area of interest.
5) Use vlookup function in Excel to find the county for each team.
6) Use pivot table to tabulate how many teams in each county.
7) Find a blank map that shows the counties in your area of interest.
8) Find a map with the name of each county.
9) Then manually create a text box for each county, put it inside the county on the blank map and link the value from the pivot data to it. This is the time consuming and tedious part.

The good thing is once you create the link. Every time you update the team lists in the future, you only have to refresh the pivot table and you will have a new map. Michigan used to create this map manually in the past by counting. Good thing we are all just crazy volunteers.

I did one for California too which is my new home.

[Michael Kaurich]

Thanks Ed! I have a spreadsheet that does that. Was wondering if anyone broke the code to have the text boxes auto update. It is the tedious part.

[orangelight]

Originally Posted by Michael Kaurich

Thanks Ed! I have a spreadsheet that does that. Was wondering if anyone broke the code to have the text boxes auto update. It is the tedious part.

I might be able to do something like this. PM me your spreadsheet and details and Ill see what I can do.