Ranking the "best" deal for the money

I have been working on a spreadsheet where I can input data for multiple options and it spit out the best value for the list. As an example, cars. You are car shopping, you have 10 different cars you are looking at, each with different mileage, different price, different years, which one is the best value? I am having trouble with the formula.
?*
Right now I am looking at which Machine Learning model is the best based on the amount of processes it runs for the lowest amount of RAM it uses and i can crack it, see below:
?*

Model	Macs	Memory	Score
1	542.13	4299	18.63%
2	7.58	801	1.40%
3	7.47	801	1.38%
4	119.65	1015	0.99%
5	63.65	935	-2.59%

Which model is best for the most amount of macs for the least memory? Column E2 formula is =(C2/MAX(C:C)+1/(D2/MIN(D:D)))-1. Can anyone confirm if this is the best way to do this, because #2 and #3 cant be the right macs for memory. any advice is helpful.
?*
--
OR
--
with cars, which one below is the best deal when you have three sets of data to look at and two have a positive correlation (miles and price, you want the lowest miles for the lowest price) and the other factor is has a negative correlation (you want the highest year for the lowest price or lowest miles) considering absolutely no other details about them.

Car	Miles	Year	Price
1	73055	2013	14,900
2	34000	2013	17,900
3	92000	2014	15,000
4	50000	2014	16,000
5	40000	2013	13,000

Thanks

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Is your forum profile up-to-date and showing ONLY the oldest Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The four most recent Excel products are Excel 2019, Excel 2021, Excel 2024 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

Your problem is not so much an Excel problem but an optimization problem. If you knew how to do the optimization then the Excel part would be straightforward.

The problem with determining which ML model is best or which car is the best value is that you have not told us how important each of the criteria are.

The formula you showed us is not meaningful if we don't know what data is in what columns. As Ali says, you should provide a sample Excel file.

When ranking options, you should assign either an absolute score or relative ranking for each criterion. Otherwise how would I know if you would rather have a 2013 car with low mileage or a 2020 with high mileage? There are two questions that could be answered, and I'm not sure which one you want to ask:

1) Which car is the best value for me personally, based on my own criteria? Your preferences to value a car might be different than how the market values cars. For example, the market values older model years less because they have outmoded features, but maybe you don't care about that.

2) Which car is the best value based on the market? That is, you would take statistics for year, mileage, and sales price of actual car sales (this would require data that may not be readily available ). Then determine the coefficients for the two variables, year and mileage. Then you can model the equation to determine value (linear? quadratic? logarithmic? exponential?) for any car, and determine which model best fits the data.

Also, as Ali points out, your profile shows an Excel version of "16.10" which means nothing to me. See the last sentence of Ali's post.