Having nothing but big powerful V8s around the house is all fun and games until you live up next to the mountains and need to drive up and down a lot of hills. Then it gets hella expensive. So I am shopping for a V6 pick-up truck. I do this using Craigslist and Excel.

It will be a month or two before I can scrape the dollars together to buy the truck, so in the meantime I am collecting data from Craigslist. I check Craigslist every day and put all the relevant properties into a spreadsheet:

Image

Note the listings are sorted by “Score.” What is this “Score?” you ask. Well, I needed to be able to use objective criteria to rank different trucks from best to worst. I did this by calculating feature factors and then multiplying them to arrive at a final score. The higher the score the better the deal. The feature factors are in hidden columns, and unhiding columns M through Q reveals the calculations:

Image
  • Year factor simply takes the model year and divides by 2016.
  • Mileage factor averages all the odometer readings of all the listings, and divides that number by the current listing’s odometer reading. If the current listing does not have an odometer reading, the error is captured and the number 1.00 is inserted as a mileage factor.
  • Cab factor. If the listing has a king cab, the cab factor is 1.75.
  • Shell factor. If the listing has a shell, the shell factor is 1.20. Obviously a king cab is worth more to me than a shell (in fact, I probably won’t buy a truck without a king cab).
  • Price factor is calculated similarly to the mileage factor: the prices are averaged, and that number is divided by the current listing’s price.

So for the final score all you have to do is multiply the factors together.

The listings with the highest scores have outrageously low asking prices (generally there is something wrong with them), while the lowest scores have very high prices or very high mileage. But the scores in the middle are for cars I am most likely to want to inspect and maybe buy. As I add more cars to the list, the scoring system will make it easier to sift through the listings.

While working on this last sheet I discovered you can mix data types in Excel! For the cab and shell factors, I needed to be able to put the actual multipliers (1.75 and 1.20 respectively) in cells somewhere so the feature factors could be derived from them. I do it this way instead of building the numbers into the formulas so I can quickly change the multipliers for all the cars (generally, as a best practice with spreadsheets, I figure out all the constants I’m going to need and then put all those in separate cells, often on a separate sheet, so that I can easily change the constants as desired).

But I didn’t want to have to add a new column just for each multiplier, and I didn’t want to set up a new constants worksheet.

So on a hunch I used Excel’s text functions to derive the multiplier from the column header, “Cab Factor (1.75),” as shown:

Image

See what I did there? I did a calculation on the number contained in the last four characters of the string, save one. And fuck me if it didn’t actually work! So all I have to do to change the multiplier is edit the column heading, making sure the number is four characters including the decimal point and there’s a close parenthesis following it.

It’s actually pretty amazing you can mix data types like that, and definitely no accident.

A friend advised me to avoid Ford Rangers. I used to be dogmatically opposed to Fords, based on experiences my family had in the 1970s. But as I get older I have put ideology aside. My RV is a Ford, for example. Two of my best friends drove nothing but Ford Rangers for years, one after the other (this was during my anti-Ford days and I always made a face when I got into one of their cars).

Now I realize if Ford or anyone else that wasn’t supported by the government (like FIAT) made consistently bad cars, they wouldn’t be in business. And to that point Ford has been, over the last decade or two, the most successful car manufacturer in the world. So I guess they don’t really make crap after all.

As for Toyota Tacomas, all the Tacomas I have seen on Craigslist either have outrageous mileage (like 250,000 miles) or outrageous price tags. Perhaps that’s an indication of their essential quality, but I am not buying a car with over 200,000 miles, nor am I paying twice the right price for one. But thanks to my spreadsheet, if a reasonably priced Tacoma with moderate mileage comes on the market, I’ll know to jump on it.