Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenFri, 29 Sep 2017 02:11:58 +0200Base: run query on connected spreadsheet?https://ask.libreoffice.org/en/question/133133/base-run-query-on-connected-spreadsheet/ Howdy,
I created a a Base database that links to a huge .ods spreadsheet of weather data organized into date, time, and temperature fields. The link works fine and creates a properly formatted table in which I can see all the data.
The problem occurs when I try to run a query on the table. I want to display the maximum daily temperatures. So I add the date and temp fields to the query. But when I try to access the GROUP or MAXIMUM functions in the drop-down menu, I find the drop-down menu is empty. In fact, there are no functions available in queries at all.
Am I doing something wrong, or is this a limitation of connected databases in Base?
Is there a work-around?
DinoGuyFri, 29 Sep 2017 02:11:58 +0200https://ask.libreoffice.org/en/question/133133/Adding values from a named listhttps://ask.libreoffice.org/en/question/127774/adding-values-from-a-named-list/ I have a sheet of resistors, names in one column and value in an other column, it is possible to lookit up with VLOOKUP or HLOOKUP but I should like to get the values automatic added from a list of resistor names.
Sheet 1: list of resistor names and its value; Sheet2 a column given a list of resistor names and a field there the resistors is added up. Say "R1+R2+R342+A34" and its value it's added up, other formula should work also if operator is possible,
operator @ as VLOOKUP and given tables of names and value. "@R1+@R2+@R342+@A34"
More complex ""@R1//@R2" there R1 and R2 resistors are in parallel recording formula R=1/(1/R1+1/R2) there // is an other operator. Now I don't know if that is working in any languages or exchange @ and // with SER(R1,R2) och PA(R1,R2) there SER o PAR is working more as a function.
Its little hard to explain. HelgeTue, 29 Aug 2017 15:00:35 +0200https://ask.libreoffice.org/en/question/127774/Aggregate Function Not Working Properlyhttps://ask.libreoffice.org/en/question/85827/aggregate-function-not-working-properly/ I have a formula that works in Excel, and I thought I had it working in LibreOffice, but I am getting different results between programs. I am dealing with a forum database tens of thousands rows long, so I can't do it manually like I have it here.
I have a table that should look like this (and does in Excel):
messageid threadid replyto Last messageid in thread
3550 3550 0 3550
3867 3867 0 4677
3947 3867 3867
3998 3867 3947
4337 3867 3998
4414 3867 4337
4481 3867 4414
4677 3867 3867
3925 3925 0 7200
4653 3925 3925
4959 3925 4653
7200 3925 3925
4092 4092 0 4092
5800 5800 0 5800
5802 5802 0 5802
7202 7202 0 7202
7203 7203 0 7656
7294 7203 7203
7305 7203 7203
7342 7203 7203
7406 7203 7342
7436 7203 7305
7656 7203 7203
Quick Explanation, I am trying to find the last 'messageid' within a threadid. If the 'replyto' is 0, then I get a result, if it's <> 0, then blank.
I used this function in Excel with success
=IF(C2=0,AGGREGATE(14,6,$A$2:$A$24/($B$2:$B$24=B2),1),"")
In LibreOffice, I get different results using the same function. Instead of the last (largest) number from Col. A showing, I get the first number, so it looks like this:
messageid threadid replyto Last messageid in thread
3550 3550 0 3550
3867 3867 0 3867
3947 3867 3867
3998 3867 3947
4337 3867 3998
4414 3867 4337
4481 3867 4414
4677 3867 3867
3925 3925 0 3925
4653 3925 3925
4959 3925 4653
7200 3925 3925
4092 4092 0 4092
5800 5800 0 5800
5802 5802 0 5802
7202 7202 0 7202
7203 7203 0 7203
7294 7203 7203
7305 7203 7203
7342 7203 7203
7406 7203 7342
7436 7203 7305
7656 7203 7203
Can someone please help me?
I don't have access to Excel at work, and I use LibreOffice, which I normally enjoy and have no issues with.
Thanks In Advance,
Melvins138melvins138Fri, 20 Jan 2017 20:26:38 +0100https://ask.libreoffice.org/en/question/85827/calc - calculate with custom timestampshttps://ask.libreoffice.org/en/question/82737/calc-calculate-with-custom-timestamps/Let's say I have cells with custom timestamps, e.g.
2016-10-18T14:14:09.831Z-GET
2016-10-18T15:05:09.854Z-GET
And I want to calculate the time elapsed in-between them (in this case, roughly ``3060``seconds).
How do I do that?DiesNutsTue, 29 Nov 2016 12:31:49 +0100https://ask.libreoffice.org/en/question/82737/question about match function for calchttps://ask.libreoffice.org/en/question/63062/question-about-match-function-for-calc/I have an array, for example {3;2;1;2;3;2;1;0}, I would like to find the position with value larger than 1.5 in descending order. I use MATCH(1.5,{3;2;1;2;3;2;1;0},-1} and expecting it would return 2. However it returns value 6. It is not like what is told in help. Is it a bug?
My expectation when using MATCH() this way, MATCH() will start making the comparison to 1.5 from left (3). When it encounters the 3rd value(1) which is smaller than 1.5. it will stop and returns position of (2) which is larger than my search value (1.5).
following is copied from help:
"MATCH(SearchCriterion; LookupArray; Type)
If Type = 1 or the third parameter is missing, the index of the last value that is smaller or equal to the search criterion is returned. This applies even when the search array is not sorted. ***For Type = -1, the first value that is larger or equal is returned."***benkmyThu, 14 Jan 2016 18:13:02 +0100https://ask.libreoffice.org/en/question/63062/LO Spreadsheet: If cell-range contains X, then do Yhttps://ask.libreoffice.org/en/question/27099/lo-spreadsheet-if-cell-range-contains-x-then-do-y/I'm unsure how to do this.
Task: If e.g. cell B12 to B41 contains value "1", then and only then, create SUM of cells C12 to C41 with the value they have (but only for the rows in which the B-column does have a value "1") and enter that value into target cell C 60.
Maybe I better describe what I'm trying to do here: I keep track of something in a table that has colums Mo-So for the weekdays in which I enter a 1 if the event is on that day. Next to that I have columns going from 9-24 which is the hour of the day. If a certain even happens I enter a "1" for that hour of the day.
What I now want is some formula that does the following: Create a nice weekly table for all weekdays and add events at the correct hour they happened.
I'm unsure how to do this. Guess it's possible but the formula needed makes me dizzy and I don't know where to start.nighthomeFri, 13 Dec 2013 15:28:26 +0100https://ask.libreoffice.org/en/question/27099/