Ask Your Question
1

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

asked 2014-04-27 20:07:19 +0200

WAZAAAAA gravatar image

updated 2014-04-27 20:08:27 +0200

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/... )

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-21 14:56:58.983748

2 Answers

Sort by » oldest newest most voted
1

answered 2014-04-28 01:43:51 +0200

m.a.riosv gravatar image

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

edit flag offensive delete link more

Comments

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.

WAZAAAAA gravatar imageWAZAAAAA ( 2014-04-30 14:04:04 +0200 )edit
0

answered 2014-04-28 00:03:07 +0200

lactea gravatar image

updated 2014-04-28 00:06:48 +0200

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

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2014-04-27 20:07:19 +0200

Seen: 470 times

Last updated: Apr 28 '14