Loveland, CO. This formula helped us come up with Loveland as the answer to that question:
=SUM(PRODUCT((INDIRECT("E"&ROW())-MIN($E$2:$E$435))/(MAX($E$2:$E$435)-MIN($E$2:$E$435)),5),PRODUCT(1-((INDIRECT("F"&ROW())-MIN($F$2:$F$435))/(MAX($F$2:$F$435)-MIN($F$2:$F$435))),10),PRODUCT(INDIRECT("G"&ROW())/100,5),PRODUCT(INDIRECT("H"&ROW())/100,5),PRODUCT(INDIRECT("I"&ROW())/100,10),PRODUCT((INDIRECT("J"&ROW())-MIN($J$2:$J$435))/(MAX($J$2:$J$435)-MIN($J$2:$J$435)),2),PRODUCT((INDIRECT("K"&ROW())-MIN($K$2:$K$435))/(MAX($K$2:$K$435)-MIN($K$2:$K$435)),10),PRODUCT(1-((INDIRECT("L"&ROW()))/10),2),PRODUCT(1-(INDIRECT("M"&ROW())/10),2),PRODUCT(1-(ABS(85-INDIRECT("N"&ROW()))/25),1),PRODUCT(1-(ABS(32-INDIRECT("O"&ROW()))/25),1),PRODUCT(INDIRECT("P"&ROW())/100,10),PRODUCT((INDIRECT("Q"&ROW())-MIN($Q$2:$Q$435))/(MAX($Q$2:$Q$435)-MIN($Q$2:$Q$435)),5),PRODUCT(IF(INDIRECT("R"&ROW()),1-(ABS(17-INDIRECT("R"&ROW()))/5),1),5),PRODUCT(1-((INDIRECT("S"&ROW())-MIN($S$2:$S$435))/(MAX($S$2:$S$435)-MIN($S$2:$S$435))),15),PRODUCT(1-((INDIRECT("T"&ROW())-MIN($T$2:$T$435))/(MAX($T$2:$T$435)-MIN($T$2:$T$435))),10),PRODUCT(IF(INDIRECT("U"&ROW())="D",1,0),2),INDIRECT("Y"&ROW()))
Wait, What?
The formula above is the exact spreadsheet formula we used to discover places that we would like to live. In a nutshell, we were miserable where we previously lived and over the course of several months during my free time, I decided we would analyze as much city data that we could and condense it into a number I called “Rank”.
We researched every list we could come up with in a Google search, “Greenest Cities”, “Best places to live”, “Best Cities in the US”, etc. We added cities that we thought we would like or we thought would be novel places to live. Combined with some “Top 10/20/50/100 lists” and city data provided by http://www.city-data.com/, we came up with the following categories of raw data on which our formula would “judge” each city:
- Population
- Population Density
- Air Quality
- Water Quality
- Superfund
- Doctors per 100,000 people
- Median Income
- Violent Crime Rating
- Property Crime Rating
- Average High Temperature
- Average Low Temperature
- Average Comfort Rating
- Dollars spent per student
- Students per teacher
- Cost of Living
- Percentage of population claiming Christian
- Political orientation (Democrat/Republican)
The following categories came from lists but I don’t remember exactly which lists they referenced. We liked the ideas of each of the lists:
- CNN Money 2008
- CNN Money 2009
- Kiplinger 2009
- cityrating.com
- Green Jobs
- Simple Life
- CH Green
We looked at our categories and prioritized each, giving a weight as to how important the category was in comparison with all other categories. Altogether, 435 cities were setup in the spreadsheet and ranked. Our final choice was actually ranked at #8. For grins, we added our previous city to the spreadsheet and found it was ranked #291.
We immediately noticed 3 of our top 10 cities were in Colorado, and actually within 1 hour drive from each other. The thought of Colorado had never crossed my mind until that moment.
Once we had the rankings, we started from the top and performed a more social and less analytical approach to finding a city. We searched for each town’s website, calendar of activities, local newspapers (and we actually read through them), reviews and what people talked about in the area.
Loveland was chosen after all that research. We hit the road with a rent-a-truck, no place to live, no job lined up, and haven’t turned back since…