I've got a tricky Excel problem I'm trying to solve. I?ve got a long list of cars, and for each car, I?m trying to count the number of other cars on the list that is of better value. Below is a sample mock list (see image below the dotpoints). The two parameters for determining value are: price and mileage. I?ve tried to figure out the logic for determining ?better value? and the below is what I could come up with, but let me know if you have a simpler logic.
For simplicity, let?s just assume there are only two cars we?re comparing value, ?Vehicle 1? and ?Vehicle 2?.
Logic:
1. If Vehicle 2?s price and mileage are ?both? lower than Vehicle 1, then Vehicle 2 counts as better value than Vehicle 1. For example, in the below mock data, the Toyota Yaris in row 7 is definitively better value than both the Yaris in row 3 and row 5.
2. If vehicle 1's price is lower than vehicle 2's, then the saving to be made from Vehicle 2 in terms of mileage should yield a Price/Mileage ratio ?lower? than Vehicle 1's, in order for Vehicle 2 to be considered better value. Eg. Let?s assume the Toyota Yaris in row 3 is Vehicle 1, and the Yaris in row 5 is Vehicle 2. Going from Vehicle 1 to 2 would result in a saving of (40,000-30,000) 10,000km, but at an additional cost of ($14,000-12,000) $2000. Vehicle 1's Price/Mileage is ($12,000 / 40,000) 0.3. The Price/Mileage of the savings from vehicle 2 is ($2000 / 10,000) 0.2. Hence Vehicle 2 is considered of better value than vehicle 1.
3. If vehicle 1's price is higher than vehicle 2's, then the saving to be made from vehicle 2 in terms of price should yield a Price/Mileage ratio ?higher? than Vehicle 1's, for the second vehicle to be considered better value. Eg. In the below, let's say the Toyota Yaris in row 3 is Vehicle 1, and the Yaris in row 9 is Vehicle 2. Going from Vehicle 1 to 2 would result in a saving of ($12,000-$9,000) $3,000, but at an additional cost of (80,000-40,000) 40,000km in mileage. Vehicle 1's Price/Mileage is ($12,000 / 40,000) 0.3. The Price/Mileage of the savings from vehicle 2 is only ($3000 / 40,000) 0.075. Hence Vehicle 2 is not considered of better value than vehicle 1.
Sample Image.PNG < This is sample image.
Sample Worksheet.xlsx < This is the sample worksheet.
Does anyone know of a countif formula that can execute the above logic? I?m hoping for a formula that?ll count the number of better value cars of the same model, as well as a second formula that does the same count but for different models (eg. the number of cars that aren?t a Toyota Yaris, that are of better value). Thank you for any help!
Bookmarks