# 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?

I think SWITCH function can do it,

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/Formula

`SWITCH` falls under the “Logical” category in LO 6. It is not in the current online documentation, perhaps because it’s a new feature.

It was added with 5.2, for compatibility with excel, two years ago.

Using the SWITCH() function will spoil backward/sideward compatibility with

• older versions of LibreOffice
• any version of Apache OpenOffice
• many versions of MS-Excel™.

Forgive 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.

Well. 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.

First 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.

`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!

But perhaps if AOO gets to version 5.2 then they will implement it as well.

Since 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…)