We will be migrating from Ask to Discourse on the first week of August, read the details here

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

edit retag close merge delete

Sort by » oldest newest most voted

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.

more

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!

( 2018-06-13 14:57:42 +0200 )edit

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

( 2018-06-13 17:30:33 +0200 )edit

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

( 2018-06-13 18:44:16 +0200 )edit

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

more

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

( 2018-06-13 00:21:54 +0200 )edit
1

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

( 2018-06-13 10:20:08 +0200 )edit

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

( 2018-07-01 12:23:43 +0200 )edit

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.

( 2018-07-02 00:13:19 +0200 )edit

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.

( 2018-07-02 09:51:58 +0200 )edit