Why does not Calc have some most useful functions

[ppk]

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 ?

[JohnSUN]

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?

[gabix]

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: https://bugs.documentfoundation.org/

[ppk]

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]

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.

[Lupp]

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?

[ppk]

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]

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]

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]

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

[Lupp]

@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]

3. In the first case it is at least possible to implement the most needed functions by user code. For some functions cases I did.
4. There is an ongoing argument about the question if users can responsibly be encouraged to use Calc as a database surrogate. You may vist this thread in the actual forum. My request was commented by an opinion leader without regarding any detail.
   (My intention was completely different.)

[ppk]

Lupp: Thanks for the info. I’ve gone through the forum thread. I see you can write VB code. I can’t. You are more experienced and involved in LibreOffice than me. With all due respect, I must admit that the discussions in that thread are very disappointing and missed an important point … it’s not “data keeping and managing” functionality, rather ‘data cleaninsing’ functionality that users want in Calc. To drive my point, I need bigger space. These comments are limited in characters.

[Lupp]

This askbot here doesn’t support diskussions. Use jag (you know) psilosop()de if interested.
I made my point with respect to the usage of Calc in that forum. In specific the task from the other request couldn’t reasonably be solved with a database. And nothing in the world has only advantages.
Ont the other hand I studied many hundred request oncerning Calc meanwhile, and there acually are many reckless attemps to do database work by Calc. I have to advise against then.

[nudelsoup]

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.

[mikekaganski]

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”