Ask Your Question

Why does not Calc have some most useful functions

asked 2016-10-10 14:06:35 +0200

ppk gravatar image

It is very surprising to me that Calc doesn't have some straightforward functions for some very common and basic functionality. For example,

  1. It doesn't have a 'unique()' function to give unique values from an a row/column/range.
  2. It doesn't have something like 'filter()' function to extract values from row/column/range based on some user-given criteria.
  3. Heck, it doesn't even have a 'split()' function to split a cell contents based on a given delimeter (like comma, dash, underscore etc).
  4. It doesn't have a 'sort()' function too. I know most of the users will suggest me to see alternative methods available in Calc. But there are some use cases, where we need 'sort()' as a function rather than as a menu item.

Even Microsoft Excel does not have any such straightforward meaningful functions for the above functionality. As far as I know, only Google Spreadsheets provides these functions. In fact, if I analyse, these functions are what a person dealing with any considerable data analysis job does use more than 60% of the time.

Are there any plans to provide these functions in LibreOffice Calc in near future ?

edit retag flag offensive close merge delete


Are you saying that all these features are already present in the Google-spreadsheets? And they work well? OMG, use the Google-spreadsheets! What is the problem?

JohnSUN gravatar imageJohnSUN ( 2016-10-10 15:06:38 +0200 )edit

For over a decade of using OpenOffice, I have never needed such functions. Thus, I would not deem them “very common and basic”. Anyway, this is a forum, and if you want any improvement, file a request at:

gabix gravatar imagegabix ( 2016-10-10 15:43:36 +0200 )edit

gabix: It's strange. In my case, and in most of our office use case, these functions are a very common requirement. But in your case, you did not find them as 'common and basic'. So, may be it is subjective ... whether these functions are common or not. Even if they are not common, they are very straight forward features, functionality-wise. But there are no straight forward functions available to do the job.

ppk gravatar imageppk ( 2016-10-10 18:13:39 +0200 )edit

JhonSUN: Yes, Google Spreadsheets provide all such functions and they work wonderfully. I've already been using Google Spreadsheets. I've no alternative because LibreOffice does not provide such functions. But I would really, really like to see such straight forward functions in LibreOffice as well.

ppk gravatar imageppk ( 2016-10-10 18:16:14 +0200 )edit

OK, behind us many years when users of Excel came grumbling about a function they found in Exxcel and not in Calc. Now this seems to turn to Google spredsheets to be named as the measure of all spreadsheeting.
Afaik Excel does not provide all the functions you were talking of, and introduced some new functions for text manipulation just recently in something I saw called "Excel-2016".
All the functions listed above are aiming at database like usage, I think. Why?

Lupp gravatar imageLupp ( 2016-10-10 18:27:36 +0200 )edit

Lupp! "Now this seems to turn to Google spredsheets to be named as the measure of all spreadsheeting.". No, there was no such intention in my post. I had to compare to Google Spreadsheets because at this moment it is the only one providing such straight forward functions.

ppk gravatar imageppk ( 2016-10-11 07:55:27 +0200 )edit

Lupp! As you told, yes, most of the times it is for small database like usage that we mostly use Excel/Calc for. For simple sorting, splitting texts in cells, doing something on the unique values or filtering some required data based on some criteria, we don't need to turn to database apps like Access/Base, right ?

ppk gravatar imageppk ( 2016-10-11 07:57:06 +0200 )edit

For a clarification, my post was not meant for any comparisions. I've been a LibreOffice user ever since it came in to existence (prior to that, I was using OpenOffice). I advocate for usage of free and open source programs in my circle. And believe me, very often I've come across the need for stright forward functions for these functionality. Inclusion of them will be a huge advantage for LibreOffice Calc. Anyway, as pointed out, I will post on link text.

ppk gravatar imageppk ( 2016-10-11 08:04:16 +0200 )edit

First feature request filed at Bug-103105 for unique() feature.

ppk gravatar imageppk ( 2016-10-11 08:45:50 +0200 )edit

@ppk: Maybe you missed my point.
1. I rarely miss a UNIQUE function, but I alo think the functionality offered by interactive tools (filters, sorting, F&R e.g.) should be accessible by means of standard functions in addition, In some cases an enhancement of an existing function (accessible via an additional optional parameter e.g.) would be the preferred way. .
2. There are lots of much more important improvements to Calc.

Lupp gravatar imageLupp ( 2016-10-11 12:50:00 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-06-19 12:22:22 +0200

nudelsoup gravatar image

updated 2020-06-19 12:23:48 +0200

Necro'ing this topic since a solution is still missing.

It appears that the developers have very specific tasks in mind when "helping" others. This question is legit, even if some people can't imagine a proper usecase. The fact that sometimes you have a data sheet that you want to present in a dynamic way through input boxes is not that uncommon. I mean, there's a function SQRTPI() that basically merges SQRT() and PI() - try arguing why that was implemented instead of a sort function. Besides, not everyone is able to (or have the time to) design a script or macro for something as simple as sorting a dynamic list. At this point I've considered building the suggested features myself, except I can't devote the time in the coming months (thesis).

I'm working on something as simple as extracting the minimum of 4 numbers, excluding numbers lower than half the average. In Drive Sheets, this would have taken less than a minute, using =MIN( FILTER( A1:A4, A1:A4 > (AVERAGE(A1:A4)*0.5))). It is mindboggling that something similar is impossible in Calc, that it simply lacks any function support for inline filtering of data. Instead, there are added *IF and *IFS all over the place.

It's 2020, and google are out of the evil-closet. Don't force people to go there if it can be avoided. And be careful about dismissing feature requests simply because your toolchest enables you to hack your way out of it. It's an elitist attitude that yields no benefits whatsoever.

edit flag offensive delete link more



the developers have very specific tasks in mind when "helping" others

"I see someone's comment that I disagree with. That MUST BE A DEVELOPER'S COMMENT!!!1111"

Mike Kaganski gravatar imageMike Kaganski ( 2020-06-19 12:37:17 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-10-10 14:06:35 +0200

Seen: 368 times

Last updated: Jun 19