Ask Your Question

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


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

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.



Verify if the separators are the same for you in Menu/Tools/Options/LibreOffice calc/Formula

edit flag offensive delete link 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.

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

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

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

Lupp gravatar imageLupp ( 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.

m.a.riosv gravatar imagem.a.riosv ( 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.

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

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


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 _

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


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


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

Seen: 67 times

Last updated: Jun 12