Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSat, 27 Jul 2019 07:45:20 +0200Can XPySort work in LibreOffice?https://ask.libreoffice.org/en/question/202404/can-xpysort-work-in-libreoffice/Hi,
I'm migrating an OO workbook to LO. I make use of the array sort function extension XPySort in OO but, try as I might, I can't make it work in LO. I install it and the installation seems to proceed without error, but when I try to use it, I get VALUE! errors; the same errors I get when I try to invoke the function without installing the extension.
Is there any way to make this function work in LO? Is there a similar array sort function that does?
Thanks for any help.
Edited to add: File uploaded.[C:\fakepath\Test sort LibO.ods](/upfiles/15642369317834741.ods)FrogFanSat, 27 Jul 2019 07:45:20 +0200https://ask.libreoffice.org/en/question/202404/How can I find the latest date in a non-contiguous range (e.g. C5, C10, C15...) that is also earlier than the date in another cell (e.g. A1)?https://ask.libreoffice.org/en/question/180052/how-can-i-find-the-latest-date-in-a-non-contiguous-range-eg-c5-c10-c15-that-is-also-earlier-than-the-date-in-another-cell-eg-a1/ The range is not simple, such as C5:C50, but each date is the first in a set of entries.
I want to find the latest date in each column of entries which is no later than (say) the last date of a particular month.ProgresserSun, 20 Jan 2019 21:20:13 +0100https://ask.libreoffice.org/en/question/180052/How to create a type of Procv but find result with the text and just with initials letters of the text?https://ask.libreoffice.org/en/question/171322/how-to-create-a-type-of-procv-but-find-result-with-the-text-and-just-with-initials-letters-of-the-text/Example: I need to find somes names on a big table, with a lot data on it, but I don't know the full name of the person, just the beginning of, like, a guy who has the name "Jeffrey Campbell River" and I just know the surname "Jef" how can I create a function on LibreOffice Calc for find the full name and the others data's about him?jacktorresrMon, 05 Nov 2018 21:24:09 +0100https://ask.libreoffice.org/en/question/171322/Get range containing all cells in array formula from basic macrohttps://ask.libreoffice.org/en/question/169847/get-range-containing-all-cells-in-array-formula-from-basic-macro/I am trying to write a macro that resizes an array formula or in other words a formula that I can run when the number of items returned by the array formula changes, to avoid the ulgy "#N/A" cells at the end of the array.
I have found [one example](https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=67553) but it doesn't seem to work as it requires a `getCurrentArray` function which doesn't seem to be defined, however the basic premise seems sound (i.e. delete the existing formula and re-insert a new one).
I have tried to just call the `setArrayFormula` method on a single cell in the array, but unsurprisingly that doesn't work (annoyingly, it just silently does nothing) because you need to be working with the whole array, similar to the way you can press CTRL+/ when doing it normally. If I expand the range to include the entire row (or a range which I know is larger than the array formula), then it all works as expected.
I tried recording a macro and pressing CTRL+/, and the result was:
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document, ".uno:SelectArrayFormula", "", 0, Array())
This sort of works, but:
- it's a bit of a cludge
- you have to have the spreadsheet window focussed (not the basic window, annoying when developing)
- doesn't quite do what I want, it does select the correct cells but doesn't return them as a range, so then I'd have to go another step to find the current selection and get the range from that
So my question is if I have one cell which is part of an array formula how can I find the size/range for _all_ the cells that are part of the array formula to enable me to edit/delete it.ecotenThu, 25 Oct 2018 11:14:36 +0200https://ask.libreoffice.org/en/question/169847/Formula to return all corresponding values from an arrayhttps://ask.libreoffice.org/en/question/151641/formula-to-return-all-corresponding-values-from-an-array/ Hello All, I stumbled upon a problem with VLOOKUP that it only returns first value that meets the search criterion, and so does INDEX-MATCH formula. But in my table there are repeating values so my task is to include them all (in, say, ascending order). Here is my table:
![image description](/upfiles/15231056488620913.png)
Where A and B columns contain original data, C is rank, E is filled with =LARGE($B$2:$B$138, C2) to show which B cell is the largest, the second largest and so on. I want D column to return the corresponding number from A column but using =INDEX($A$2:$A$138, MATCH(E2, $B$2:$B$138, 0)) I found that only first match for the criteria is returned. I highlighted wrong pairs of rows with yellow so you can see that the first row of yellow rows is correct while the second returns the first value again instead of finding the next cell containing the criteria (1006 and 1008 respectively). I also highlighted wrong second value with green to make it more prominent.
I suppose column D should contain an array formula instead of INDEX-MATCH but I didn't succeed in creating one. Please help. Thank you!Sandra90Sat, 07 Apr 2018 15:01:31 +0200https://ask.libreoffice.org/en/question/151641/Lookup by column and row valueshttps://ask.libreoffice.org/en/question/136700/lookup-by-column-and-row-values/I have a MxN table with first column and row used as labels (see https://imgur.com/a/n68oM for screenshot)
Is it possible to extract value from such table having row and column label? For example, for row labeled "5" and column labeled "3,00%" resulting value will be "5,30914". I tried with VLOOKUP and HLOOKUP but these functions does not work for me, as they use column index, not value.sergsWed, 01 Nov 2017 14:20:20 +0100https://ask.libreoffice.org/en/question/136700/Two parameters functions: slope, intercept, etc. do not work with indirect(address()) as one parameterhttps://ask.libreoffice.org/en/question/131818/two-parameters-functions-slope-intercept-etc-do-not-work-with-indirectaddress-as-one-parameter/Hi,
I have a strange problem with libreoffice calc latest version 5.4.1.2 under ubuntu 16.04 64 bit (build 5.4.1~rc2-0ubuntu0.16.04.1~lo0).
I would like to use some functions that take two range parameters as input (slope, intercept, rsq, etc.).
These functions work well if the parameters range are defined as usual:
`SLOPE(absolute_range1,absolute_range2), INTERCEPT(absolute_range1,absolute_range2)` where absolute_rangeX is like this `$A$10:$A$100.`
However, I would like to able to change the range on which the functions work. For this purpose I use ADDRESS and MATCH function:
`H1=ADDRESS(MATCH(value_to_search, range_where_to_search,1)+ROW(cell)-1,COLUMN(cell),1)` and I correctly get the cell address, let say `$C$10`.
After, by using `H2=INDIRECT(H1 &":$C$100")` I can obtain the required range and I able to use it in functions that take one parameter as `SUM(H2), SUMPRODUCT(H2)`.
Unfortunately, it does not work with `SLOPE(H2,$A$10:$A$100), INTERCEPT(H2,$A$10:$A$100)` and `SUMPRODUCT(H2,$A$10:$A$100)` too.
So is there any intrinsic limit about using `INDIRECT(ADDRESS())` together with functions that take two range as parameters?
Thank you
P.S.
For curiosity, I tried also with the same version of libreoffice on windows and with excel 2016 and both do not work.erotavlasThu, 21 Sep 2017 10:29:15 +0200https://ask.libreoffice.org/en/question/131818/Array function efficiency - Data Validity or in sheet functionhttps://ask.libreoffice.org/en/question/130413/array-function-efficiency-data-validity-or-in-sheet-function/ So let's say I have a table of data. One column is 'Title' and another is 'Type'. Now let's also say I wish to use 'Data > Validity' with a drop-down to allow a selection list of only a subset of the data in this table. For example I only want the 'Title' when the value in the 'Type' column matches a specific string (e.g. 'Test'). I can accomplish this with Array functions but am curious as to the efficiency of any given method.
The following Array function...
{=IF(EXACT(B:B, "Test"), A:A, "")}
...Returns the value in column A when the value in column B is 'Test', otherwise a empty string.
I can either put this array function directly into the field in Data > Validity (Cell Range) and it works. I could also use the array function in a separate column on the sheet and use the column reference as the input to Data > Validity. It seems that the latter method would be more efficient as LibreOffice only has to execute the array function one time but this leaves a bad taste in my mouth as I've now got a column of data with no header (using column references as I am I can't find a way to allow the column header to exist and still have the array function work. So I prefer the former option as now I don't have any duplicate data anywhere (I liken this to sort of database normalization, which I know isn't strictly relevant here but I like the .. clean-ness(?) of the solution). Having the function inside the Data Validity field also has a drawback of existing uniquely in every cell within which I want said validity, so if I have to change it I have to change a bunch.
Another option might be to go to column C, do a non-array version of the function and then just drag it down. I don't know if this is more efficient or not but I lose the ability to have an infinitely expandable table without the need to also extend the 'Column C' function.
So have others done in such a situation? Any other elegant solutions out there? Any info on how performance is impacted given the different options? The data set is about 500 rows. I prefer not to use Macros for portability.OhmidonTue, 12 Sep 2017 20:34:08 +0200https://ask.libreoffice.org/en/question/130413/calc - #value when computing vector percentiles?https://ask.libreoffice.org/en/question/83351/calc-value-when-computing-vector-percentiles/The range below basically say "cut off everything before ``$B$8`` and everything after ``$B$9$`` and for the remaining, sum columns ``I,J`` where the values are distributed over multiple sheets (3 in this case, "mw_rep0", "mw_rep1", "mw_rep2").
``OFFSET($mw_rep0.I:I,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.J:J,$B$8,0,$B$9-$B$8,1),OFFSET($mw_rep1.I:I,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.J:J,$B$8,0,$B$9-$B$8,1),OFFSET($mw_rep2.I:I,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.J:J,$B$8,0,$B$9-$B$8,1)``
I will refer to this as ``range1`` (though I have to copy-paste it to use it because apparently, you cannot create a name for it).
So ``=AVERAGE(range1)`` entered with ``CTRL+SHIFT+ENTER`` will calculate me the average sum.
Now I want percentiles. To that end, I modify ``range1`` such that the terms for each sheet are separeted by ``~``, rather than by ``,``:
e.g: ``=PERCENTILE(OFFSET($mw_rep0.I:I,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.J:J,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep1.I:I,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.J:J,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep2.I:I,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.J:J,$B$8,0,$B$9-$B$8,1),0.9)``
when I enter *this* with ``CTRL+SHIFT+ENTER``, I get ``#VALUE!``.
Which I find strange because I have an even uglier range summing up columns ``B,C,D,E``, for which this works:
``OFFSET($mw_rep0.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.E:E,$B$8,0,$B$9-$B$8,1),OFFSET($mw_rep1.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.E:E,$B$8,0,$B$9-$B$8,1),OFFSET($mw_rep2.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.E:E,$B$8,0,$B$9-$B$8,1)``
I will refer to this as ``range0``.
Here, both, ``=AVERAGE(range0)`` and percentiles (for which we again modify ``range0`` such that the individual sheets' terms are separated by tilde, rather than comma) work.
e.g. ``=PERCENTILE(OFFSET($mw_rep0.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep0.E:E,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep1.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep1.E:E,$B$8,0,$B$9-$B$8,1)~OFFSET($mw_rep2.B:B,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.C:C,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.D:D,$B$8,0,$B$9-$B$8,1)+OFFSET($mw_rep2.E:E,$B$8,0,$B$9-$B$8,1),0.9)``
So how do I get the percentiles working in the "easier" case?DiesNutsWed, 07 Dec 2016 13:56:25 +0100https://ask.libreoffice.org/en/question/83351/The following array formula does not work anymore after update from 4.x to 5.0.6https://ask.libreoffice.org/en/question/70477/the-following-array-formula-does-not-work-anymore-after-update-from-4x-to-506/ Hi,
I previously used the following array formula from my main sheet, which worked just fine.
=INDEX(SunEarthTools5min.B$2:VE$367,E806,MATCH(MIN(IF(ISNUMBER(SunEarthTools5min.B31:VE31),ABS(180-(SunEarthTools5min.$B31:$VE31)),"NA")),ABS(180-SunEarthTools5min.$B31:$VE31),0))
The point of the formula was to find the hour of the zenith (azimuth 180°) each day in a sheet with the day as row and time by increments of 5 mins as columns. My main sheet has in column E the days from 1 to 365, which is used as the row of the index.
After updating yesterday to 5.0.6, this formula dos not work anymore, returning #VALUE, and I can't find any workaround nor any error in the formula (there shouldn't be any, as it worked just fine in the previous versions).
The ISNUMBER allows to filter out sunless hours ; removing it from the formula didn't change anything.
I tried this :
=INDEX(SunEarthTools5min.B$2:VE$367,E779,MATCH(180-MIN(IF(ISNUMBER(SunEarthTools5min.B4:VE4),ABS(180-(SunEarthTools5min.$B4:$VE4)),"")),SunEarthTools5min.$B4:$VE4,0))
Which works, but provides an error (#N/A) for a number of rows for no reason that I can figure out. That's however not as good a solution, I know it, that's why I only tried it as a last resort.
So, if anybody can spot an error in my formulas or help, or if it is definitely a bug I should file a report for, I'd be very glad.
Thank you.BaalkaWed, 25 May 2016 19:10:07 +0200https://ask.libreoffice.org/en/question/70477/HELP Extract records using ROWS arrays formulashttps://ask.libreoffice.org/en/question/65461/help-extract-records-using-rows-arrays-formulas/What I am trying to do is from one document to another but I will show you on the same sheet.
I am using ROWS and then $ fixing the number not the letter, but I double checked my formula using an online tutorial
<BR/><BR/>found here; https://www.youtube.com/watch?v=NhRYtZLmtwA 7.15 mins in. <BR/>
Download tutorial doc here; https://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT615-622.xlsm
<BR/>you need tab (616) it is completed on tab (616an)
<font color="ff0000">=IF(COLUMNS($G15:G15)>$F15,"",INDEX($B$2:$B$12,SMALL(IF($B$2:$B$12=$E15,ROW($B$2:$B$12)-ROW($B$2)+1),COLUMNS($G15:G15))))</font>
<font color="00ff00">Then when I press ctrl+shift+enter to make it an array ( or use the array tick box when using ctrl+f2)
then I copy across the formula (or down using ROWS)<br/>
The COLUMNS count doesn't move along and it shows up Err:508</font>
Im not sure where the page is that tells you what that error means but in openoffice it means; <br/>
508 Pair missing Missing bracket or parenthesis; for example, closing brackets but no opening brackets.
Could someone have a look into this for me am I doing something wrong??
![image description](/upfiles/14569350821106654.jpg)
![image description](/upfiles/14569350983282358.jpg)King_ZZWed, 02 Mar 2016 05:56:13 +0100https://ask.libreoffice.org/en/question/65461/bug in tricky array formulahttps://ask.libreoffice.org/en/question/60067/bug-in-tricky-array-formula/Hello all!
So, with the help of this forum a couple of months ago, I got this epic and tricky spreadsheet done with some very complex formulas, which had stumped me forever until someone brilliant used array formulas to solve my problem.
Now, in using the sheet, there is a bug and I am at a complete loss to get it to work right! Please please you formula gurus out there, help me get this bug figured out.
(I am on LibreOffice Calc 4.2.8.2 in English under linux)
The spreadsheet is here attached, and the problem is in the 'current' tab in the F column. The formula pulls data from the 'advocacy log' tab and is supposed to pull the name of the most recent advocate for the person in question (named in the A column of the current sheet on that same line). It works until two entries have the SAME DATE, then as you can see in the example data, it doesn't work (current.F9 should show as Joshua).
[council record.ods](/upfiles/14459528694904954.ods)
I am so lost in this formula, your help is deeply appreciated.
Thanks, JoshuajoshuaosTue, 27 Oct 2015 14:35:54 +0100https://ask.libreoffice.org/en/question/60067/So close to DMAX solution...https://ask.libreoffice.org/en/question/53794/so-close-to-dmax-solution/Okay, so I'm on LibreOffice Calc 4.2.8.2 in English...
I've got one very simple sheet that is to be a 'review_log'... with two columns and data entered...
Date | Who
4/12/13 | Joshua
4/12/14 | Joshua
4/12/15 | Joshua
3/2/15 | James
Etcetera, with names and dates entered forever and the list getting longer.
On another sheet, I enter in the first column just the name, in the test case, Joshua... and I need to get the date of the LAST (chronologically) entry for that name. The closest I've come uses DMAX...
A B
1 | Who | Last Review
2 | Joshua | =DMAX('review_log'.$A$2:$B$1003,"Date",{"Who";"Joshua"}
Now right now, this DMAX works! However, the name "Joshua" is hard coded into it. I need this function to work dynamically so that "Joshua" in the criteria array is replaced with the value of A2... I have tried simply putting A2 there, "A2", =A2, "=A2", using the cell function... and I can't get anything to work!
Anyone have any ideas? :)
Thank you all so much!
[edit]
Thanks to a brilliant function by pierre-yves samyn, I am so so close to having this document fully functional! The problem is I need to pull another piece of data out along with the date! Here is the actual spreadsheet I'm stuck on for clarity. It's the first sheet ("current"), column F is all that's left, with the solutions for C and E already found!
[council record.ods](/upfiles/14383013019209485.ods)
This forum is amazing! Thank you all, I had nearly given up hope this was possible!
--Joshua
[edit]
So in using this sheet we've found a bug, which happens when two entries on the advocacy log page have the same date. As shown in this file, the F9 cell on the 'current' tab should read Joshua, based on the entries in the 'advocacy log' tab. I would really appreciate any solutions you can come up with, I'm at a loss.
[council record.ods](/upfiles/14456423157157275.ods)joshuaosTue, 28 Jul 2015 18:21:10 +0200https://ask.libreoffice.org/en/question/53794/