+ Reply to Thread
Results 1 to 4 of 4

Formula for counting cars of better value

  1. #1
    Registered User
    Join Date
    04-13-2022
    Location
    QLD, Australia
    MS-Off Ver
    Office 2016
    Posts
    2

    Formula for counting cars of better value

    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!

  2. #2
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Formula for counting cars of better value

    Try this, E2=SUMPRODUCT(($B$2:$B$9=B2)*(($C$2:$C$9*$D$2:$D$9)<C2*D2)), copy down.

  3. #3
    Registered User
    Join Date
    04-13-2022
    Location
    QLD, Australia
    MS-Off Ver
    Office 2016
    Posts
    2

    Re: Formula for counting cars of better value

    You're a genius Joseph! It worked like a charm, but can you explain how the formula works? So I can sort of see what it's doing, it gets the multiple of price and mileage, and ranks each car based off this? But I'm not sure how the formula does this, eg. what does the trailing "<C2*D2" do?

  4. #4
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,952

    Re: Formula for counting cars of better value

    I am not good at explaining formulas. You may try Formulas > Evaluate formula.

    The formula basically compares price x mileage for each model and count the number of same models with values greater than the value of current car.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Formula to calculate how many cars are parked at a point in time
    By NOVE9 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-09-2021, 10:49 AM
  2. Planning drivers on cars
    By Bramw2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-21-2019, 08:00 AM
  3. [SOLVED] List cars on the road
    By makinmomb in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-16-2018, 07:10 AM
  4. Using Excel to log hired cars
    By rossf1uk in forum Excel General
    Replies: 3
    Last Post: 05-06-2016, 07:40 PM
  5. Get existing cars from a list
    By Immortal2014 in forum Excel General
    Replies: 6
    Last Post: 04-29-2016, 04:27 PM
  6. Maintenance service for cars, problem of formula : missing conditions
    By Jeoffrey3 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-11-2014, 04:53 AM
  7. [SOLVED] customerfollowup for cars sales
    By joanne panzo in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 06-13-2006, 08:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1