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!