Complicated Calc spreadsheet, how to classify a list by the "best"?

[WAZAAAAA]

I’d rather explain what I need to do with an example instead of using words. Talking about LibreOffice Calc here.

Let’s say that I have the detailed characteristics of 3 racing cars and want to sort/classify them from the best to the worst.
(downloadable example Calc file here: http://en.libreofficeforum.org/sites/libreofficeforum.org/files/uploads/WAZAAAAA_files/StupidExample.ods)

Ferrari - MAX SPEED:200km/h - ACCELERATION SPEED:8sec - MASS:1100kg

McLaren - MAX SPEED:110km/h - ACCELERATION SPEED:10sec - MASS:1090kg

Honda - MAX SPEED:120km/h - ACCELERATION SPEED:10sec - MASS:1091kg

The calculation should take in consideration that higher=better for MAX SPEED, and lower=better for ACCEL SPEED and MASS. It should also consider that the MASS advantage for the Honda over the Ferrari is a very small “victory” (+0,82%) compared to the huge difference in MAX SPEED (-66%).
Naturally, the results from the best to the worst in the example would be Ferrari 1st, Honda 2nd, McLaren 3rd, but how do I process all of this with Calc? And HOW MUCH (maybe expressed in percentage) is each car better than the other?

Thanks in advance.

[mariosv]

Please take a look if the attached sample is an approximation on what you want.

Doing first a percent calculation, for the values in every characteristic, a second one for the weighing between the characteristics to get a final value to evaluate Sample.ods

[WAZAAAAA]

Please have my babies. This is exactly what I needed.

I’d like to add a little something: when I had to write 0 in one of the values (for example, such as MAX SPEED 0 for Honda), it would give me the “#DIV/0!” error. My workaround was to simply delete the 0 and the error would disappear, still keeping the right proportions somehow.

Thank you again.

[lactea]

To me, this appears to be a perfect example for a balanced scorecard.

Define criteria ranges and give points accordingly. Say,

MaxSpeed > 100: 1 point

MaxSpeed > 140: 2 points

MaxSpeed > 180: 3 points

… up to maybe 10 points each criteria. Then simply add up the scores. This can be automatized via the lookup()-function by the way. In case you want to overweight a criteria, you should multiply a factor or make a weighted multiplication.

a * SpeedPoints + b * Accel.Points + c * WeightPoints = Score, with a + b + c = 1