1
$\begingroup$

Okay so long story short, I have a list of 184 cars with a bunch of info for each of them in Excel. I want to be able to compare the price of the cars to the rank of them so that I can essentially find which car is the "most worth buying". Rank is simply just how much I like each car, for example a car with a rank of 23 means its my 23rd favorite out of all 184 cars. So when I say most worth buying, what I mean by this is I obviously would way rather have a car ranked 10th for 50,000 than a car ranked 20th for 200,000. There are plenty of not so easy examples though too, such as which car is more worth it, the car ranked 18th and costs 50,000 or the car ranked 15th but costs $90,000?

So the way I went about this originally was to break the cars up into "Price Brackets". I have like a 0-20,000 bracket, a 20,000-40,000, 300,000-400,000, etc. Since the more the cars cost, the less the difference in cost really matters, I have the brackets have larger differences as the price rises. A twenty five thousand dollar car verses a 15 thousand dollar car is a lot larger of a difference than a half a million dollar car vs five hundred and ten thousand dollar car.

After splitting the cars up by price into these brackets, I just sorted the cars by rank within each bracket. So the best ranked car was at the top of each bracket.

This kind of worked, and got me groups of like 16ish cars, with the most "worth it" ones at the top, but still I know this isn't the best way to go about this.

First off, the higher the price of the car you go, the more the rank should matter. If you're trying to decide between a couple of cars to buy, one of them costing like 5 million the other 4 million, you are most likely someone of extreme wealth that doesn't really care too much about that price difference, therefore the "winner" should be the higher ranked car, unless the price difference is really dramatic obviously.

Secondly, if a car is only 1 rank better than another within its same price bracket, but costs $70,000 more, why is it on top?

I'm struggling to figure out exactly how to explain all of this but hopefully you understand what I mean. Basically I'm just trying to figure out the most efficient way to find out how worth it each of these cars are. I tried using a scatterplot, trend lines, etc. and after a bunch of attempts I figured I should seek some help, so please help me out!

|cite|improve this question
$\endgroup$
  • $\begingroup$ Anyone think they can help? $\endgroup$ – Alex Nov 17 '17 at 21:29
  • 1
    $\begingroup$ The only way to solve this is using your subjective judgement about the relationship between the rankings and the price: A very wealthy person might opt for the top-ranked car regardless of the price, whereas someone with a limited budget would have to settle for a lower-ranked car that they can afford. There is no way to make rank and price commensurate except through your own subject evaluation or if you did something like a survey of people to see what value they put on what attributes. For example, is one rank "worth" an extra $20,000? There's no way to judge that except subjectively. $\endgroup$ – Sal Mangiafico Oct 6 '18 at 18:25
  • $\begingroup$ That being said, you may gain some insight from plotting rank vs. price, or as @PeterFlom suggests, rating vs. price. For example, if rating increases with price up to a point and then levels out, you could use something like a linear-plateau model to identify this point on the plot, and the critical x : the price above which there is no further increase in rating as price continues to increase. Another idea: you could use clustering methods to see if there are e.g. high-rating-high-price cars and medium-rating-high-price cars. $\endgroup$ – Sal Mangiafico Oct 6 '18 at 18:33
1
$\begingroup$

First, I don't see how making brackets helps. That turns one problem with no easy solution into a bunch of problems, all just like the first, each with no easy solution.

Second, I think the problem is with "rank". That is an ordinal classification. It won't yield an easy solution unless the car ranked first is also cheapest - and that won't happen.

So, what to do? You need to come up with a way of evaluating cars’ value to you. You can use the data you have to help with this. How much do you value, say, acceleration? Gas mileage? etc. for all the variables you have. Then you can easily make a linear combination that gives each car a rating rather than a ranking. Then figure out which car, among those you can afford, has a price that is best for its rating.

|cite|improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.