Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 30 Apr 2014 14:04:04 +0200Complicated Calc spreadsheet, how to classify a list by the "best"?https://ask.libreoffice.org/en/question/33227/complicated-calc-spreadsheet-how-to-classify-a-list-by-the-best/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.Sun, 27 Apr 2014 20:07:19 +0200https://ask.libreoffice.org/en/question/33227/complicated-calc-spreadsheet-how-to-classify-a-list-by-the-best/Answer by m.a.riosv for <p>I'd rather explain what I need to do with an example instead of using words. Talking about LibreOffice Calc here.</p>
<p> 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: <a href="http://en.libreofficeforum.org/sites/libreofficeforum.org/files/uploads/WAZAAAAA_files/StupidExample.ods">http://en.libreofficeforum.org/sites/...</a> ) </p>
<p>Ferrari - MAX SPEED:200km/h - ACCELERATION SPEED:8sec - MASS:1100kg</p>
<p>McLaren - MAX SPEED:110km/h - ACCELERATION SPEED:10sec - MASS:1090kg</p>
<p>Honda - MAX SPEED:120km/h - ACCELERATION SPEED:10sec - MASS:1091kg</p>
<p>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?</p>
<p>Thanks in advance.</p>
https://ask.libreoffice.org/en/question/33227/complicated-calc-spreadsheet-how-to-classify-a-list-by-the-best/?answer=33238#post-id-33238Please 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](/upfiles/13986422128844295.ods)Mon, 28 Apr 2014 01:43:51 +0200https://ask.libreoffice.org/en/question/33227/complicated-calc-spreadsheet-how-to-classify-a-list-by-the-best/?answer=33238#post-id-33238Comment by WAZAAAAA for <p>Please take a look if the attached sample is an approximation on what you want.</p>
<p>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 <a href="/upfiles/13986422128844295.ods">Sample.ods</a></p>
https://ask.libreoffice.org/en/question/33227/complicated-calc-spreadsheet-how-to-classify-a-list-by-the-best/?comment=33312#post-id-33312Please 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.Wed, 30 Apr 2014 14:04:04 +0200https://ask.libreoffice.org/en/question/33227/complicated-calc-spreadsheet-how-to-classify-a-list-by-the-best/?comment=33312#post-id-33312Answer by lactea for <p>I'd rather explain what I need to do with an example instead of using words. Talking about LibreOffice Calc here.</p>
<p> 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: <a href="http://en.libreofficeforum.org/sites/libreofficeforum.org/files/uploads/WAZAAAAA_files/StupidExample.ods">http://en.libreofficeforum.org/sites/...</a> ) </p>
<p>Ferrari - MAX SPEED:200km/h - ACCELERATION SPEED:8sec - MASS:1100kg</p>
<p>McLaren - MAX SPEED:110km/h - ACCELERATION SPEED:10sec - MASS:1090kg</p>
<p>Honda - MAX SPEED:120km/h - ACCELERATION SPEED:10sec - MASS:1091kg</p>
<p>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?</p>
<p>Thanks in advance.</p>
https://ask.libreoffice.org/en/question/33227/complicated-calc-spreadsheet-how-to-classify-a-list-by-the-best/?answer=33237#post-id-33237To 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
Mon, 28 Apr 2014 00:03:07 +0200https://ask.libreoffice.org/en/question/33227/complicated-calc-spreadsheet-how-to-classify-a-list-by-the-best/?answer=33237#post-id-33237