Ask Your Question
0

Funtion "case" in Calc?

asked 2018-06-12 17:53:36 +0200

zinzolin gravatar image

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!

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
4

answered 2018-06-12 18:13:44 +0200

m.a.riosv gravatar image

I think SWITCH function can do it, image description

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

edit flag offensive delete link more

Comments

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

Jim K gravatar imageJim K ( 2018-06-13 00:21:54 +0200 )edit
1

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

m.a.riosv gravatar imagem.a.riosv ( 2018-06-13 10:20:08 +0200 )edit
2

answered 2018-06-12 19:21:41 +0200

Lupp gravatar image

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.

edit flag offensive delete link more

Comments

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!

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

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

Jim K gravatar imageJim K ( 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...)

Lupp gravatar imageLupp ( 2018-06-13 18:44:16 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-06-12 17:53:36 +0200

Seen: 41 times

Last updated: Jun 12