Description: There are some array features in spreadsheets which recently have been added to Excel. These functions make lots of sense and they remind me of statistical software (e.g. Stata) which have very similar functions: FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY https://support.office.com/en-us/article/new-array-functions-003df6c7-1dcb-4388-8e2e-0fe77a0887bc?ui=en-US&rs=en-US&ad=US Steps to Reproduce: . Actual Results: . Expected Results: . Reproducible: Always User Profile Reset: No Additional Info:
Note that those functions aren't fully specified yet, for example https://support.office.com/en-us/article/RANDARRAY-function-21261e55-3bec-4885-86a6-8b0a47fd4d33 says: Note: January 14, 2019: The RANDARRAY function is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers. Also, it needs to be investigated how the spilling situations are actually handled and what happens if due to recalculation the return array size varies. This is not just new spreadsheet functions but a new behaviour that calculating a function can change spreadsheet content. Also restrictions like such formula can be edited only at the top left of its array need to be implemented, though we maybe could handle that differently as long as the edited formula affects the range as if it was edited at the top left. Our current array formula display/edit behaviour already is somewhat similar.
*** Bug 127809 has been marked as a duplicate of this bug. ***
I would like to support Gerry's cause. In my opinion also the XLOOKUP function would be very helpful. It returns an array if needed. XLOOKUP, FILTER, SORT, UNIQUE are my wishes. Juergen
Another vote of support.
These are not just nice to have anymore, some or all of those functions are already used in Google Sheets for a long time and needed for intercompatability, and are not Office Insiders exclusive anymore.
Here is the documentation for the Google Sheet functions: FILTER https://support.google.com/docs/answer/3093197?hl=en SORT https://support.google.com/docs/answer/3093150?hl=en SORTN https://support.google.com/docs/answer/7354624?hl=en UNIQUE https://support.google.com/docs/answer/10522653?hl=en SEQUENCE https://support.google.com/docs/answer/9368244?hl=en RANDARRAY https://support.google.com/docs/answer/9211904?hl=en
Another vore from me.
Interesting applications: https://superuser.com/questions/1711510/when-filtering-data-is-it-possible-to-put-in-a-cell-the-value-of-the-filter https://superuser.com/questions/807286/equivalent-for-filter-formula-in-libre-office
I add my support for these functions. Over the years I've come across the limitations of array handling a number of times. Most often I've found wacky workarounds with compromises on functionality and likely performance. However, there are instances where the limitations are simply too constricting making the ideal handling of arrays impossible. I'll share the example I'm currently facing later, but provide a general use case in favor of the need of these functions. I'll start by saying that dynamic Array manipulation is critical in handling data and has so many use cases. In my opinion the dynamic handling of arrays is the only remaining missing link in both function and form, at least as far as data handling in Calc. One general limitation is that you can't take 2 arrays and interweave the data filtered on criteria, and then sorted to be in a proper order. Libreoffice Calc has the tools to do this manually, but if the document has ongoing changes, that manual effort becomes extraordinarily time consuming and tedious. ----------- The rest of this post will detail real world limitations ----------- So my current example, which could be solved by these functions being implemented, goes as follows: I have a budget/finance system which has separate sheets as ledgers for income, expenses, transfers between accounts. There are then other sheets which extrapolate as much data as possible from those ledgers allowing you to see things like totals from different income types, various expenses broken down, averages per month, detailed account information, stock information, and on and and and on. (This requires some not as wacky array workarounds, but it works.) Now stocks are where some wacky array workarounds start. If I want to pluck the purchase of a stock out of the Expenses Sheet (and all related information), I need a named range/formula expression called, "ExpensesToStock" with: IF(ExpensesType="Stock">0,(ExpensesType="Stock")*(ROW(ExpensesType)),"") Where "ExpensesType" is the column titled Type in the Expenses Sheet. "ExpensesToStock" is now an array of row numbers where that data is in the Expenses Sheet, and blank spaces where the data isn't. Then I remove the blank spaces on a separate configuration sheet by grabbing the "Small" of those row numbers out of "ExpensesToStock" by putting this function in consecutive rows: {=IFERROR(SMALL(ExpensesToStock,ROW($A1)),"")} Each consecutive row then increments as a variable for the small. (ROW($A1),ROW($A2),...) Now that I have a list of rows where the data is, I can run an "Indirect" function to display all of the relevant data on a separate sheet called "Stocks." That's a filtered ledger of only stocks that I can then do additional calculations on to provide even more stock relevant data. ^ TL;DR, Functions automatically copy each Stock line out of the "Expenses" sheet into the "Stocks" sheet and calculate additional important information related to the Stocks. However, here is where the limitations of Calc array handling arise. Stocks aren't just an expense. Stocks can be an expense, income, or a merging between companies. If I wanted to make a dynamic ledger to determine proper data (total stocks, total basis, etc.) I need to evaluate both the purchase, sale, and transfer from the income and expenses sheet for all stock related information. You can imagine that the stock purchase and sale date are pretty important for the order of calculating. Combining both the purchase and sale of stocks plucked from 2 different sheets into a single date sorted ledger would be trivial and automatic with a filter and sort array function, but currently impossible in Calc short of constant manual efforts.
Please don't change the Summary. There is a different bug 127923 for XLOOKUP implementation
(In reply to Roman Kuznetsov from comment #10) > Please don't change the Summary. There is a different bug 127293 for XLOOKUP > implementation
*** Bug 147224 has been marked as a duplicate of this bug. ***
I was about to submit a bug report requesting for these functions, but fortunately I found this first. These functions are super useful and I've seen a handful of questions about whether LO Calc has them. +1 for this enhancement request.
Just a little reminder that these functions are still missing :( and people who want to use them are forced to use either Microsoft 365, Office 2021 or Google Sheets.
(Hoping this is not just noise, but can help somebody) I know it's not a complete solution, but in a hurry, this extension: https://github.com/goosepirate/lox365 can be useful.
*** Bug 156698 has been marked as a duplicate of this bug. ***
I completely agree! Google Sheets is getting too powerful and I need to use it more often because Libre calc is missing functions like FILTER and SORT which are essential for my spreadsheets. I hope libre calc devs code those much needed enhancements...
(In reply to Jacob H from comment #9) > I'll start by saying that dynamic Array manipulation is critical in handling > data and has so many use cases. In my opinion the dynamic handling of arrays > is the only remaining missing link in both function and form, at least as > far as data handling in Calc. Dynamic arrays https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531
*** Bug 159105 has been marked as a duplicate of this bug. ***
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/b0791dd9216224bdb266fd0d8c87df253b6d0583 tdf#126573 Add Excel2021 array function FILTER to Calc It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/e5361d0ef04bc63e7809323750aabe6efafcaef9 Related: tdf#126573 Add Excel2021 array function FILTER to Calc It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/45435a680be065e44eba385bb2523b27b77fb451 tdf#126573 Add Excel2021 array function SORT to Calc It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/bfb4c58ae708c75949559290bdfdd9afcef6aa91 tdf#126573 Add Excel2021 array function SORTBY to Calc It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/be938f2fb1c0a18658b4170396a22c64b77bf646 Related tdf#126573 Add Excel2021 array function SORT to Calc It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/35772a003bb30be61f8ba8abe805455e41db0e1e tdf#126573 Add Excel2021 array function SEQUENCE to Calc It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/d68f2394afc3372d867ea6157123e51b278ba81b tdf#126573 Add Excel2021 array function RANDARRAY to Calc It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Balazs Varga committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/c1565b8b94548ef85508b0302ef9299f18ff09be tdf#126573 Add Excel2021 array function UNIQUE to Calc It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
In release notes: https://wiki.documentfoundation.org/index.php?title=ReleaseNotes%2F24.8&type=revision&diff=750726&oldid=750464
On behalf of everyone who has been waiting for these, thank you so much.
FTR, the functions were added to the Release Notes wiki page (for LO 24.8), and _then_ additional patches were applied, and several bugs were reported (and hopefully fixed). The fact that these functions are already mentioned in the Release Notes does not mean that they were all thoroughly tested by common users. OTOH, they are not considered experimental either. So, please actually test and report. Please note that "supporting dynamic arrays" is not the same as supporting these functions; this ticket is about the latter only.
Link for testers: https://bettersolutions.com/excel/functions/updates-2021-new-functions.htm
I notice that if I enter the array formula =UNIQUE(A1:A999) into a range of cells that is larger than the output from the UNIQUE() (e.g. paste it into a range of 10 cells when there are only 5 unique values in the range A1:A999) then the remaining cells are filled with #N/A. Fair enough I guess but when I try the array formula =IFNA(UNIQUE(A1:A999),"NO VALUE") it doesn't substitute the #N/A for NO VALUE as I had hoped. Is that a bug? Would apply to any array formula pasted into a range larger than it's result although unique() is one where the size of the input and output arrays differ so arises more often.
Another observation: If I put =UNIQUE(A1:A999) into one cell, say B1 then an array formula is automatically created and placed into a range that is just big enough for the result. If I have five unique values in the range A1:A999 then this will be B1:B5. Then if I go and add more unique values to the range A1:A999 then the result of the UNIQUE() (which is now too big for the range B1:B5) is silently truncated. In excel the behaviour is different - the output range dynamically grows to accomodate the larger result of the UNIQUE(). In the event that this larger range runs into something else the result is shown as #SPILL meaning "result is too large for available space". This seems a bit more robust and intuitive. What is the intended behaviour here?
(In reply to Duncan Loveday from comment #33) > Another observation: If I put > > =UNIQUE(A1:A999) into one cell, say B1 then an array formula is > automatically created and placed into a range that is just big enough for > the result. If I have five unique values in the range A1:A999 then this will > be B1:B5. Then if I go and add more unique values to the range A1:A999 then > the result of the UNIQUE() (which is now too big for the range B1:B5) is > silently truncated. Manually selecting the range where the array formula is located, and expanding the range should provide the additional values. ATM, recalculate (hard) will not automatically expand the result. IDK whether it should – there are both positive and negative consequences, and Calc would need to know how to deal with them – or whether this is the expected behavior (with any array formula?). > > In excel the behaviour is different - the output range dynamically grows to > accomodate the larger result of the UNIQUE(). In the event that this larger > range runs into something else the result is shown as #SPILL meaning "result > is too large for available space". This seems a bit more robust and > intuitive. That's about dynamic array formulas, which is not the same as supporting these functions. > > What is the intended behaviour here?
add FILTER function ODF proposal is here: https://issues.oasis-open.org/browse/OFFICE-4156 add SORT function ODF proposal is here: https://issues.oasis-open.org/browse/OFFICE-4157 add SORTBY function ODF proposal is here: https://issues.oasis-open.org/browse/OFFICE-4158
(In reply to ady from comment #34) Yes I can manually expand the range to allow for additional values. My point though is that the behaviour differs from excel and some of the other comments here lead me to believe the motivation for this change is partly to replicate what is there in excel. Silently truncating the result seems like a bad idea to me, just my opinion of course. And yes this applies to any array formula not just these although it becomes an issue for an array formula where the size of the output depends on the input contents which is the case for UNIQUE, not for many others like SORT where the input and output ranges are the same size.
https://ask.libreoffice.org/t/draw-the-mandelbrot-set-in-libreoffice-calc-using-only-formulae/107400/2 Error occurs when entering (Ctrl+Shift+Enter) the following formulae in LibreOffice 24.8 Dev: Err:539: =ArrayFormula(UNIQUE({ MOD(SEQUENCE(1681,1,0,1),41)/20, ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20; -MOD(SEQUENCE(1681,1,0,1),41)/20, ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20; MOD(SEQUENCE(1681,1,0,1),41)/20, -ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20; -MOD(SEQUENCE(1681,1,0,1),41)/20, -ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20 })) Err:539: =UNIQUE({ MOD(SEQUENCE(1681,1,0,1),41)/20, ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20; -MOD(SEQUENCE(1681,1,0,1),41)/20, ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20; MOD(SEQUENCE(1681,1,0,1),41)/20, -ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20; -MOD(SEQUENCE(1681,1,0,1),41)/20, -ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20 }) Err:509: =MOD(SEQUENCE(1681,1,0,1),41)/20, ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20, -MOD(SEQUENCE(1681,1,0,1),41)/20, ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20, MOD(SEQUENCE(1681,1,0,1),41)/20, -ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20, -MOD(SEQUENCE(1681,1,0,1),41)/20, -ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20 Can you please advise how to enable LibreOffice Calc to draw the MandelBrot Set in LibreOffice Calc using only formulae? Thank you
Created attachment 195060 [details] MandelBrot Set test spreadsheet
> Error occurs when entering (Ctrl+Shift+Enter) the following formulae in > LibreOffice 24.8 Dev: > Err:509: > =MOD(SEQUENCE(1681,1,0,1),41)/20, ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20, > -MOD(SEQUENCE(1681,1,0,1),41)/20, ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20, > MOD(SEQUENCE(1681,1,0,1),41)/20, -ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20, > -MOD(SEQUENCE(1681,1,0,1),41)/20, -ROUNDDOWN(SEQUENCE(1681,1,0,1)/41)/20 Thank you for you comment, Óvári. What would be the expected value/array here? Is this long function should work? (For me it looks syntactically wrong.) I mean I copy-pasted it into other apps like (Excel, google sheet) and I got errors there too. I tried the functions separately, like: =MOD(SEQUENCE(1681;1;0;1);41)/20 =ROUNDDOWN(SEQUENCE(1681;1;0;1)/41)/20 =-MOD(SEQUENCE(1681;1;0;1);41)/20 =-ROUNDDOWN(SEQUENCE(1681;1;0;1)/41)/20 and all of these worked fine. :) > Can you please advise how to enable LibreOffice Calc to draw the MandelBrot > Set in LibreOffice Calc using only formulae? Is this problem related to these new functions? If not maybe we should set back to resolved and should open another new one for this?
Created attachment 195063 [details] separate sequence functions
Err:539 means this: In LibreOffice, an inline array can only be build from constant values. LibreOffice is not able to use expressions that need a calculation. The ODF specification is in section 5.13 Inline Array, part 4, ODF 1.3. There you find the Note “Note: Expression authors should be aware that use of Expression other than constant Number or constant String may impair interoperability.” So the problem with these examples seems to be, that the values of the inline array are not constant. An enhancement request exists already, tdf#105683. I suggest, setting the bug back to "resolved fixed".
Why have I not been notified, in the beginning of the year I was in need of the functions again and checked if someone was working on it, and now with the next version, which hopefully still supports Windows 7 for the one PC at work I don't get a replacement for (yet) *fingers crossed* we finally get the much needed array functions :D
(In reply to Philip Sch from comment #42) > the next version, which hopefully still supports Windows 7 Note https://wiki.documentfoundation.org/ReleaseNotes/24.8#Platform_Compatibility
*** Bug 162600 has been marked as a duplicate of this bug. ***