Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenMon, 02 Jul 2018 09:51:58 +0200Funtion "case" in Calc?https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/Hi everybody,
is there an easy way to have a "case" or a "switch" function in LibreOffice Calc?
It could work this way:
CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
I am currently struggling with nested IFs: `IF(condition_1;result_condition1_yes;IF(condition_2;result_condition2_yes;IF(condition_3; ... )`
This kind of nested formula is possible but extremely difficult to read, also because there are no colors or other formatting in the formula bar that could help differentiate the several parts of the expression.
The function `Choose` could help but is only available for numerical values.
Any suggestions?
Thanks in advance!Tue, 12 Jun 2018 17:53:36 +0200https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/Answer by JohnSUN for <p>Hi everybody,</p>
<p>is there an easy way to have a "case" or a "switch" function in LibreOffice Calc?
It could work this way:</p>
<pre><code>CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
</code></pre>
<p>I am currently struggling with nested IFs: <code>IF(condition_1;result_condition1_yes;IF(condition_2;result_condition2_yes;IF(condition_3; ... )</code></p>
<p>This kind of nested formula is possible but extremely difficult to read, also because there are no colors or other formatting in the formula bar that could help differentiate the several parts of the expression.</p>
<p>The function <code>Choose</code> could help but is only available for numerical values.</p>
<p>Any suggestions?</p>
<p>Thanks in advance!</p>
https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?answer=157805#post-id-157805Try use VLOOKUP:
=IFERROR(VLOOKUP(expression;"condition_1";"result_1"|"condition_2";"result_2"|"...";"..."|"condition_n";"result_n"};2;0);"else_result")Tue, 12 Jun 2018 18:14:01 +0200https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?answer=157805#post-id-157805Answer by Lupp for <p>Hi everybody,</p>
<p>is there an easy way to have a "case" or a "switch" function in LibreOffice Calc?
It could work this way:</p>
<pre><code>CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
</code></pre>
<p>I am currently struggling with nested IFs: <code>IF(condition_1;result_condition1_yes;IF(condition_2;result_condition2_yes;IF(condition_3; ... )</code></p>
<p>This kind of nested formula is possible but extremely difficult to read, also because there are no colors or other formatting in the formula bar that could help differentiate the several parts of the expression.</p>
<p>The function <code>Choose</code> could help but is only available for numerical values.</p>
<p>Any suggestions?</p>
<p>Thanks in advance!</p>
https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?answer=157809#post-id-157809First assuming as assured that only one of the conditions can come out 'True' something like
=CHOOSE(1+(Cond1)*1+(Cond2)*2+(Cond3)*3+(Cond4)*4;"Else";Res1;Res2;Res3;Res4)
would do. The parenthesed conditions must be numeric with 0 for False and 1 for True. Ordinary comparisons return 0 and 1 respectively as needed.
Now trying to model the `Case` structure of program execution. The first True condition chooses the path. Subsequent alternatives are skipped. To get this behaviour using the CHOOSE() function we have to annulate conditions coming after an already evaluated True:
=CHOOSE(1+(Cond1)*1+(CURRENT()=1)*(Cond2)*2+(CURRENT()=1)*(Cond3)*3 _
+(CURRENT()=1)*(Cond4)*4;"Else";Res1;Res2;Res3;Res4)
This does no longer look pretty. A solution using a lookup table and applying a kind of `MATCH()` may be preferrable.
Tue, 12 Jun 2018 19:21:41 +0200https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?answer=157809#post-id-157809Comment by Lupp for <p>First assuming as assured that only one of the conditions can come out 'True' something like</p>
<pre><code>=CHOOSE(1+(Cond1)*1+(Cond2)*2+(Cond3)*3+(Cond4)*4;"Else";Res1;Res2;Res3;Res4)
</code></pre>
<p>would do. The parenthesed conditions must be numeric with 0 for False and 1 for True. Ordinary comparisons return 0 and 1 respectively as needed.</p>
<p>Now trying to model the <code>Case</code> structure of program execution. The first True condition chooses the path. Subsequent alternatives are skipped. To get this behaviour using the CHOOSE() function we have to annulate conditions coming after an already evaluated True: </p>
<pre><code>=CHOOSE(1+(Cond1)*1+(CURRENT()=1)*(Cond2)*2+(CURRENT()=1)*(Cond3)*3 _
+(CURRENT()=1)*(Cond4)*4;"Else";Res1;Res2;Res3;Res4)
</code></pre>
<p>This does no longer look pretty. A solution using a lookup table and applying a kind of <code>MATCH()</code> may be preferrable.</p>
https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=157865#post-id-157865`SWITCH()` is not specified in the OASIS paper "Recalculated Formula". It was implemented starting with V5.2 to improve interoperability with some versions of MS-Excel.
As it is specialised on comparisons with single values, you will experience the same annoyances you get with `CHOOSE()` if you want to apply more elaborate conditions. Its only advantage lies in not restricting the `Expression` to one returnng small ivalues `1..n` in the most simple case.
Not compatible with A.OpenOffice!Wed, 13 Jun 2018 14:57:42 +0200https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=157865#post-id-157865Comment by Jim K for <p>First assuming as assured that only one of the conditions can come out 'True' something like</p>
<pre><code>=CHOOSE(1+(Cond1)*1+(Cond2)*2+(Cond3)*3+(Cond4)*4;"Else";Res1;Res2;Res3;Res4)
</code></pre>
<p>would do. The parenthesed conditions must be numeric with 0 for False and 1 for True. Ordinary comparisons return 0 and 1 respectively as needed.</p>
<p>Now trying to model the <code>Case</code> structure of program execution. The first True condition chooses the path. Subsequent alternatives are skipped. To get this behaviour using the CHOOSE() function we have to annulate conditions coming after an already evaluated True: </p>
<pre><code>=CHOOSE(1+(Cond1)*1+(CURRENT()=1)*(Cond2)*2+(CURRENT()=1)*(Cond3)*3 _
+(CURRENT()=1)*(Cond4)*4;"Else";Res1;Res2;Res3;Res4)
</code></pre>
<p>This does no longer look pretty. A solution using a lookup table and applying a kind of <code>MATCH()</code> may be preferrable.</p>
https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=157885#post-id-157885But perhaps if AOO gets to version 5.2 then they will implement it as well.Wed, 13 Jun 2018 17:30:33 +0200https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=157885#post-id-157885Comment by Lupp for <p>First assuming as assured that only one of the conditions can come out 'True' something like</p>
<pre><code>=CHOOSE(1+(Cond1)*1+(Cond2)*2+(Cond3)*3+(Cond4)*4;"Else";Res1;Res2;Res3;Res4)
</code></pre>
<p>would do. The parenthesed conditions must be numeric with 0 for False and 1 for True. Ordinary comparisons return 0 and 1 respectively as needed.</p>
<p>Now trying to model the <code>Case</code> structure of program execution. The first True condition chooses the path. Subsequent alternatives are skipped. To get this behaviour using the CHOOSE() function we have to annulate conditions coming after an already evaluated True: </p>
<pre><code>=CHOOSE(1+(Cond1)*1+(CURRENT()=1)*(Cond2)*2+(CURRENT()=1)*(Cond3)*3 _
+(CURRENT()=1)*(Cond4)*4;"Else";Res1;Res2;Res3;Res4)
</code></pre>
<p>This does no longer look pretty. A solution using a lookup table and applying a kind of <code>MATCH()</code> may be preferrable.</p>
https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=157891#post-id-157891Since LibO branched off AOO did **very** littel enhancement. New versions mainly got some bug fixing, and 4.1.4 wasn't even sufficiently successful with avoiding new bugs for what it was unusually fast replaced with 4.1.5. I wouldn't critisise a conservative feature-policy. However (kust two examples):
Data from ranges passed to a user function still get numeric 0 for empty cells.
Still no CF managment. (Buggy, in LibO though...)Wed, 13 Jun 2018 18:44:16 +0200https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=157891#post-id-157891Answer by m.a.riosv for <p>Hi everybody,</p>
<p>is there an easy way to have a "case" or a "switch" function in LibreOffice Calc?
It could work this way:</p>
<pre><code>CASE [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END
</code></pre>
<p>I am currently struggling with nested IFs: <code>IF(condition_1;result_condition1_yes;IF(condition_2;result_condition2_yes;IF(condition_3; ... )</code></p>
<p>This kind of nested formula is possible but extremely difficult to read, also because there are no colors or other formatting in the formula bar that could help differentiate the several parts of the expression.</p>
<p>The function <code>Choose</code> could help but is only available for numerical values.</p>
<p>Any suggestions?</p>
<p>Thanks in advance!</p>
https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?answer=157804#post-id-157804I think SWITCH function can do it,
![image description](/upfiles/15288198378053504.png)
if not maybe using VLOOKUP or HLOOKUP with an inner array can solves your issue, but formula can't be used inside the inner array, in this case using it with ranges with the formulas.
=VLOOKUP(A8;{0|"#N/A";390|"ultraviolett";420|"violett";480|"blau";560|"grün";580|"gelb";630|"orange";690|"rot";740|"infrarot"};2;1)
=HLOOKUP(A10;{0|390|420|480|560|580|630|690|740;"#N/A"|"ultraviolett"|"violett"|"blau"|"grün"|"gelb"|"orange"|"rot"|"infrarot"};2;1)
Verify if the separators are the same for you in Menu/Tools/Options/LibreOffice calc/FormulaTue, 12 Jun 2018 18:13:44 +0200https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?answer=157804#post-id-157804Comment by m.a.riosv for <p>I think SWITCH function can do it,
<img alt="image description" src="/upfiles/15288198378053504.png"></p>
<p>if not maybe using VLOOKUP or HLOOKUP with an inner array can solves your issue, but formula can't be used inside the inner array, in this case using it with ranges with the formulas. </p>
<pre><code>=VLOOKUP(A8;{0|"#N/A";390|"ultraviolett";420|"violett";480|"blau";560|"grün";580|"gelb";630|"orange";690|"rot";740|"infrarot"};2;1)
=HLOOKUP(A10;{0|390|420|480|560|580|630|690|740;"#N/A"|"ultraviolett"|"violett"|"blau"|"grün"|"gelb"|"orange"|"rot"|"infrarot"};2;1)
</code></pre>
<p>Verify if the separators are the same for you in Menu/Tools/Options/LibreOffice calc/Formula</p>
https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=159499#post-id-159499Forgive me Lupp, this is an endless question, but I think two years is more than enough. It is unfortunate that AOO has not had significant updates for over five years, I guess we can't wait forever for that to happen to take advantage of the new features. In fact I suspect that the only reason it is still apparently maintained is for being a stumbling block for LibreOffice.Mon, 02 Jul 2018 00:13:19 +0200https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=159499#post-id-159499Comment by Jim K for <p>I think SWITCH function can do it,
<img alt="image description" src="/upfiles/15288198378053504.png"></p>
<p>if not maybe using VLOOKUP or HLOOKUP with an inner array can solves your issue, but formula can't be used inside the inner array, in this case using it with ranges with the formulas. </p>
<pre><code>=VLOOKUP(A8;{0|"#N/A";390|"ultraviolett";420|"violett";480|"blau";560|"grün";580|"gelb";630|"orange";690|"rot";740|"infrarot"};2;1)
=HLOOKUP(A10;{0|390|420|480|560|580|630|690|740;"#N/A"|"ultraviolett"|"violett"|"blau"|"grün"|"gelb"|"orange"|"rot"|"infrarot"};2;1)
</code></pre>
<p>Verify if the separators are the same for you in Menu/Tools/Options/LibreOffice calc/Formula</p>
https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=157821#post-id-157821`SWITCH` falls under the "Logical" category in LO 6. It is not in the current online documentation, perhaps because it's a new feature.Wed, 13 Jun 2018 00:21:54 +0200https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=157821#post-id-157821Comment by m.a.riosv for <p>I think SWITCH function can do it,
<img alt="image description" src="/upfiles/15288198378053504.png"></p>
<p>if not maybe using VLOOKUP or HLOOKUP with an inner array can solves your issue, but formula can't be used inside the inner array, in this case using it with ranges with the formulas. </p>
<pre><code>=VLOOKUP(A8;{0|"#N/A";390|"ultraviolett";420|"violett";480|"blau";560|"grün";580|"gelb";630|"orange";690|"rot";740|"infrarot"};2;1)
=HLOOKUP(A10;{0|390|420|480|560|580|630|690|740;"#N/A"|"ultraviolett"|"violett"|"blau"|"grün"|"gelb"|"orange"|"rot"|"infrarot"};2;1)
</code></pre>
<p>Verify if the separators are the same for you in Menu/Tools/Options/LibreOffice calc/Formula</p>
https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=157841#post-id-157841It was added with 5.2, for compatibility with excel, two years ago.Wed, 13 Jun 2018 10:20:08 +0200https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=157841#post-id-157841Comment by Lupp for <p>I think SWITCH function can do it,
<img alt="image description" src="/upfiles/15288198378053504.png"></p>
<p>if not maybe using VLOOKUP or HLOOKUP with an inner array can solves your issue, but formula can't be used inside the inner array, in this case using it with ranges with the formulas. </p>
<pre><code>=VLOOKUP(A8;{0|"#N/A";390|"ultraviolett";420|"violett";480|"blau";560|"grün";580|"gelb";630|"orange";690|"rot";740|"infrarot"};2;1)
=HLOOKUP(A10;{0|390|420|480|560|580|630|690|740;"#N/A"|"ultraviolett"|"violett"|"blau"|"grün"|"gelb"|"orange"|"rot"|"infrarot"};2;1)
</code></pre>
<p>Verify if the separators are the same for you in Menu/Tools/Options/LibreOffice calc/Formula</p>
https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=159453#post-id-159453(Adding a hint from one of my comments on the other answer also here.)
Using the SWITCH() function will spoil backward/sideward compatibility with
- older versions of LibreOffice
- any version of Apache OpenOffice
- **many** versions of MS-Excel(TM).Sun, 01 Jul 2018 12:23:43 +0200https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=159453#post-id-159453Comment by Lupp for <p>I think SWITCH function can do it,
<img alt="image description" src="/upfiles/15288198378053504.png"></p>
<p>if not maybe using VLOOKUP or HLOOKUP with an inner array can solves your issue, but formula can't be used inside the inner array, in this case using it with ranges with the formulas. </p>
<pre><code>=VLOOKUP(A8;{0|"#N/A";390|"ultraviolett";420|"violett";480|"blau";560|"grün";580|"gelb";630|"orange";690|"rot";740|"infrarot"};2;1)
=HLOOKUP(A10;{0|390|420|480|560|580|630|690|740;"#N/A"|"ultraviolett"|"violett"|"blau"|"grün"|"gelb"|"orange"|"rot"|"infrarot"};2;1)
</code></pre>
<p>Verify if the separators are the same for you in Menu/Tools/Options/LibreOffice calc/Formula</p>
https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=159524#post-id-159524Well. I cannot reasonably object to leaving AOO behind. It's their decision in a sense.
In this specific case I just wanted to
-1- point to facts that might otherwise be unnoticed.
-2- indirectly hint that I don't judge SWITCH() to be very useful. It can help to run sheets originating in the "well beloved" Excel 2016, but **might better not be used in fresh design**.Mon, 02 Jul 2018 09:51:58 +0200https://ask.libreoffice.org/en/question/157801/funtion-case-in-calc/?comment=159524#post-id-159524