It’s a safe bet that at some point in most of our lives, we’ll find ourselves shopping for a used car.

I just completed this process, in fact, and I admittedly may have made it a wee bit more arduously complicated than necessary, but with the sole intention of purchasing a quality vehicle at a fair price. So, I figured I would share the self-inflicted complications with you, in the hopes that it may be informative, inspiring, or if nothing else, something for you to shake your head at in amazement, disbelief, and pity.

Brief context:

My car-buying experience is limited. My car ownership history is as follows:

1st Car: 1979 Chevy Malibu Classic, inherited from my Grandpa.

2nd Car: 1995 Oldsmobile Aurora that I bought from a friend’s dad’s dealership, fully trusting that I was getting a good deal.

3rd Car: 1997 Toyota Camry that Mom bought mostly with money she got from selling my Aurora for me.

4th Car: 2001 Toyota Camry that I bought from a dealer that I was referred to by a friend of my boss who managed the service department.

To The Present…

I still have my ’01 Camry, actually. She’s hanging in there. The car we were looking to replace was my wife’s ’06 Saturn Ion 2 Coupe, complete with suicide doors and a tiny backseat that didn’t adequately facilitate the insertion and removal of a 10-month-old child from a car seat. It also didn’t boast much cargo room for a growing family that makes multiple road trips to see family each year (and we’ve been known to bear gifts). So with only two real buying-from-a-dealer experiences under my belt – neither of which I could really evaluate the success of – I began the journey of finding our first family car…

Part 1 // Research

Figuring out what car(s) I actually wanted…

For those of you who know me well, it’s no secret that any large purchase in my life involves research, and usually, spreadsheets. I attribute this to a combination of a love of statistics, and a fear of the “purchase regret” (the late-arriving cousin to “buyer’s remorse”) that might come after realizing what I bought was really a piece of junk, or that I simply missed out on something better.

We had a shortlist of criteria to narrow the field considerably:

• Budget (since we all have unique budgets, and that’s not really what this is about, I won’t list ours, but I’m sure if you read all this carefully you can probably get a good idea)
• Fuel Efficiency (Target: A Highway/City average in the 25-30 MPG range*)
• Mileage (Target: Under 50,000 miles)
• High Ratings (since my research is an aggregation of other people’s actual hands on, in-depth research)
• Passenger Capacity (Target: Anything over 5 would be a huge plus)
• Cargo Capacity (Target: A rear cargo cap. of 30+ cu. ft., and a seats-down, max. cap. of 60+ cu. ft.)
• Safety/Reliability/Quality (all the important other stuff that tends to separate the wheat from the chaffe)

Then it was just a matter of compiling a list of vehicles that approached some or all of those priorities, and we knew going in that they would all be SUVs or “crossovers”, so the search didn’t branch out beyond that body style.

*[A quick aside: I really struggled with the concept of buying an SUV to begin with, as they aren’t really synonymous with fuel efficiency/low emissions – a factor that is extremely important to me. In fact, the more I looked around, the more I noticed the fairly consistent correlation between increased fuel efficiency and increased cost. While I kept a handful of hybrid SUV models in my research field, I quickly realized that they were all either too expensive, or in too high demand for me to even find them. I also noticed that MPG ticked up across the board around 2010, with newer versions of the same models showing increased fuel efficiency. So, I resigned myself to aiming for the best MPG we could afford, with the hope that even more fuel efficient SUVs will be available and affordable in the near future.]

I looked on a few different sites, but zeroed in on a handful that were good sources of comprehensive data: U.S. News, The Car Connection, Edmunds, MSN Autos, and J.D. Power. A few of the sites let you search with filters for some of the above criteria, so that can make the list creation pretty easy. As I perused, I kept dropping Year/Make/Model options into a spreadsheet, and once I felt like I had plenty of options to research, I started the exacting process of entering the data.

I created the above spreadsheet, making columns for:

• Make
• Model
• Year
• Whether the car was a Hybrid
• What Type of SUV it was (mid-size, full-size, compact, etc., although this column was fairly irrelevant and thus was left incomplete)
• Average Price (averaged values listed across multiple sites, though these figures weren’t always accurate to the St. Louis area)
• Average MPG between City/Hwy
• Ratings from the various sites I mentioned above, including both expert and user ratings in some cases (I converted all ratings to 0-10 scale, so for example, 4/5 stars became an 8)

Following those are columns averaging the values from:

• All the ratings columns
• The expert ratings columns
• The user ratings columns

Following those are columns for Passenger Capacity and Volume, a column calculating Volume Per Passenger, Cargo Volume, and Max. Cargo Volume (with all foldable seats down).

I grayed out cells where data was unavailable, and color-coded the values in the other columns, typically breaking the data up into three ranges: Best (Green), Average (Yellow) Worst (Red) [NOTE: the color-coding of the “Year” column wasn’t so much to designate older models as the “worst” and newer models as the “best”, but rather to make them visibly easier to distinguish.]

The next sheet I created (in the same document) was for the ratings the various sites gave each vehicle in the specific categories of Safety, Reliability, Quality/Dependability (for the sake of integration, I made these terms interchangeable – not very scientific, I know), and Performance. Then I created columns to average the values from each of the sites within each of the specific categories. As you can see, there are a lot of gray cells indicating unavailable data. Some sites just didn’t have ratings on a lot of vehicles for certain attributes.

Now, if you’re not very familiar with using formulas in a spreadsheet, this next bit may make your eyes glaze over, but it’s all part of the process…

The next sheet I created (again, in the same document) contained the same first three columns as the last two sheets (Make, Model, Year), but then had a column for each of the averaged categories in the two previous sheets. Using a combination of the “RANK” formula and “Sheet#!” label (for example, “=RANK(Sheet1!F2,Sheet1!$F$2:$F$70,1)”), I converted all of the averaged values into a rank for that vehicle in each particular category. Then – and stick with me, here – I averaged the rankings (example: “=IFERROR(AVERAGE(D2:M2))” – the “IFERROR” command came in handy for the empty cells), and then ranked the averages (example: “=IFERROR(RANK(N2,$N$2:$N$70,1))”).

In case your mind is mush after reading that, here’s what that told me: The 2011 GMC Terrain, for instance, had an average ranking of 29.2, meaning that it averaged a ranking of 29th across all the categories, compared to the other SUVs. To put it into even clearer perspective, that average ranking of 29.2 was the 26th highest average ranking among all the SUVs.

But that doesn’t really take into account which attributes (or categories) are most important to us. To do that, I weighted each category in order of importance by multiplying each category rank on a descending scale of 1.1-1.9 (with the most important categories being multiplied by 1.1, and the least important by 1.9). The formula looked like this: “=D2+(E2*1.1)+(F2*1.2)+(G2*1.3)+(H2*1.4)+(I2*1.5)+(J2*1.6)+(K2*1.7)+(L2*1.8)+(M2*1.9)”. This created what I termed a “Weighted Rank Score”, with the lowest score being the best. You can’t see the column for that because I hid it, since the column to the right is a much more helpful ranking of those scores.

And now, for the results (otherwise known as the part my wife spent over a week patiently waiting for):

I pulled out the Top 15 of both the straight average rankings, and the weighted average rankings.

[NOTE: You’ll notice that the 2011 Hyundai Tucson is also listed even though it held rankings of 35th and 40th. That’s because one of our options was to potentially purchase my wife’s parent’s Tucson from them, so I wanted to see how it measured up to the competition.]

From these lists it became pretty easy to whittle down the field to the real favorites. The Toyota Highlander listed here is the hybrid model, which in even the older models is still out of our price range. The other hybrid options – the Mazda Tribue, Mercury Mariner, and Saturn Vue hybrids – are impossible to find. That left us to choose between the various years of RAV4, CR-V, Forester, and the 2010 Chevy Equinox. With the RAV4s and CR-Vs ranking higher, my wife liking the the body styles better, and the price being fairly realistic, we decided to zero in on those 2009-2010 models.

So that concludes Part 1: Research. I know that was probably really boring for some people (my wife can empathize), and it maybe borders on obsessive tendencies, but hopefully others of you can appreciate the thoroughness of the research, and most importantly (at least to me), really letting the numbers give you a completely objective overall perspective beyond just the general (and sometimes biased) impressions you might get perusing a few ratings sites looking for info on the car you already think you want.

Up next, Part 2: Shopping. But until then, what do you think of this process? Is it completely ridiculous? A waste of time? Complete brilliance? Let me know how crazy I am (and whether or not you can relate) in the comments. (“OMG U R CRAY JUS PICK OUT A CAR LOLOL!!!!”)

Thanks for reading.

Cheers,

Kozel