libreoffice-calc interview questions
Top libreoffice-calc frequently asked interview questions
I want to calculate the sum of the A column excluding A1, because I want the result there. Illustration:
A B C
1 =SUM(A2:Ainfinite)
2 1234
3 5678
...
I could write A2:A1048576 which would work to some degree, but it would not be elegant or foolproof. Is there any notation to express the range from A 2 to A infinite?
Source: (StackOverflow)
I would like to produce a grouped categorical histogram with LibreOffice Calc.
With grouped I mean an histogram with N columns instead of a single one for each category.
Something like this:
Any clue?
Source: (StackOverflow)
I have Libreoffice and I want to export a long calc sheet to a pdf document with the fit-to-page function from the page preview in the file menu but it doesn't seems to work. I want to fit my sheet up to 2 pages because it's unreadable otherwise. When I export the sheet it gives only one page and the sheet is unreadable.
Source: (StackOverflow)
I have 150000 observations, which I am trying to work with in LibreOffice (new name for OpenOffice.org). The main thing I want to do is to have some formula calculated for all of these observations. Different parameters of the observations are in the first 4 columns, so I wrote a function in the following column in row 2 that uses the previous columns. Now I need to copy the formula all the way down to row 150000. I tried using the mouse (as usual), but this is just way to slow. Is there a faster way to mark everything all the way to the bottom?
Source: (StackOverflow)
How do you automatically set the background color of an entire row based on the value of a specific column in that row in Open/Libre Office? There are similar questions for Excel, but this doesn't completely work in Libre Office.
I can set a conditional formatting rule using a formula to set the color of a specific cell based on the value in that cell (e.g. =$D5), but I can't figure out how to apply the formatting to the entire row.
Source: (StackOverflow)
I'd like to take a group of cells in the HH:MM:SS format, and add them up. I don't want to have the value wrap on the 24 hour clock. I just want an absolute total of the values. (The sum function seems to wrap by default).
So if I have:
20:00:00
20:00:00
00:10:00
00:00:10
00:00:10
I want to end up with:
40:10:20
A contrived example, but it gets across what I'm looking for.
Source: (StackOverflow)
How do you set the default font used in new documents of LibreOffice Calc? I am using LibreOffice 3.3.2.
Some answers below have been updated for LibreOffice 4 as well.
Source: (StackOverflow)
I have a simple spreadsheet like this:
Invoice_Number Invoice_Date Invoice_Amt
1000 1/1/12 1
1001 1/1/12 2
1002 1/2/12 3
1003 1/3/12 4
How can I get LibreOffice Calc to create a sum of the amounts for each date? I have a feeling it has do with outlining and grouping, but I can't get the incantation right.
Source: (StackOverflow)
I've imported a CSV file having the first column to be date-time values in ISO 8601 format like
2012-01-01T00:00:00.000Z
for the first moment of the year 2012.
Then, willing to make LibreOffice to recognize the format (as I was looking forward to plot a diagram), I've selected the column, chosen Format Cells... and entered the custom time format as
YYYY-MM-DDTHH:MM:SS.000Z
And this seems to work if... I edit a cell to remove a hidden single-quote from its beginning (which serves to protect a cell content from being interpreted) as all the newly formatted cells now store values like
'2012-01-01T00:00:00.000Z
(note the single quote - it is only visible when you edit a particular cell).
And I am to do so for all the cells in the column. How can I automate this?
UPDATE: I've already found a solution for the particular case of mine: it helps to set a column format to "time" in the CSV import dialogue. But I am still curious how could this be done in case I wouldn't have the original .csv data file to import but only the .ods file with the data already imported without the format specified at the import time.
Source: (StackOverflow)
In my professional life, I'm limited to use Microsoft Office. In Excel there is an option ows to render the result of a formula to be pasted as a "fixed value".
For instance A1 = "I",B1 = "ride", C1 = "a" and D1 = "bike"
In E1, I can place a formula to concatenate the values of A1 through D1 to display the sentence "I ride a bike", but if I should copy and paste that cell, I would just get the formula again, both if I write the formula with relative positioning, and if the values in A1:D1 change.
To solve this problem, Excel has "Paste Special > Value", which copies the current value of the cell to the target cell.
At home, I use LibreOffce.
Although I have tried to find a feature identical to "Paste Special > Value" in both LibreOffice and OpenOffice.org, I couldn't find it.
Does anyone know how to do this?
Source: (StackOverflow)
I have an area chart in LibreOffice Calc where I would like the X axis to be measured in one month intervals spaced evenly apart.
The data going into the graph, however, is not regularly spaced at one month. There can be zero, one, or two entries in any given month.
Right now, the chart is keeping the horizontal pacing of the data consistent and adjusting the X axis to accomodate, which is the reverse of what I want. In the example picture below, you can see that there is one data point in March, one in May, and two in June. Along the X axis, April is gone and June is taking up double the space.
Instead of this, I'd like the months to stay the same spacing so there is April, May, and June, and for the data area of the chart to compress or extend horiozontally as necessary. I have tried editing the X axis and adjusting the time intervals, but this doesn't seem to do anything.
Is what I'm after possible?
Source: (StackOverflow)
I'm using LibreOffice 3.6.4.3 on Ubuntu 12.10.
I use cell color and cell borders to group and distinguish groups of cells in my spreadsheet.
I have bold horizontal borders every 4 rows. This is a pain to do because you can't specify "embolden the borders on every nth row". So I do it manually, for enough of the spreadsheet as I need to deal with.
I'm developing the spreadsheet, so the I modify the formulae frequently. When I get the formula working for a cell, I propagate that formula to a bunch of rows below by dragging the cell's fill handle down to the other (several hundred) rows. The problem is that dragging the fill handle copies the cell formatting from the source cell(s) as well as the formula(e). This undoes the emboldened borders on every 4th row and re-colors all the cells too. I then have to select a group of 4 rows of cells and manually embolden the top and bottom borders for each group of 4 rows. It's very tedious.
Is there a way to drag the fill handle and propagate the formula(e) but not the cell formatting?
Source: (StackOverflow)
I'm used to working with VLOOKUP but this time I have a challenge. I don't want the first matching value, but the last. How? (I'm working with LibreOffice Calc but an MS Excel solution ought to be equally useful.)
The reason is that I have two text columns with thousands of rows, let's say one is a list of transaction payees (Amazon, Ebay, employer, grocery store, etc.) and the other is a list of spending categories (wages, taxes, household, rent, etc.). Some transactions don't have the same spending category every time, and I want to grab the most recently used one. Note that the list is sorted by neither column (in fact by date), and I don't want to change the sort order.
What I have (excluding error handling) is the usual "first-match" formula:
=VLOOKUP(
[payee field] , [payee+category range] , [index of category column] ,
0 )
I've seen solutions like this, but I get #DIV/0! errors:
=LOOKUP(2 , 1/( [payee range] = [search value] ) , [category range] )
The solution can be any formula, not necessarily VLOOKUP. I can also swap the payee/category columns around. Just no change in sorting column, please.
Bonus points for a solution that picks the most frequent value rather than the last!
Source: (StackOverflow)
LibreOffice Calc has as Group feature. The official help site just tells you where to find the button and what hotkey to use, but doesn't explain what grouping actually does:
https://help.libreoffice.org/Calc/Group
I remember that MS Excel has something similar, but I can't recall how to make this work -- I have a table that looks something like this:
I'd like to be able to fold it like this:
And also like this:
Can this be achieved using Calc's Group feature?
Source: (StackOverflow)
(I don't believe this is duplicate of LibreOffice Calc prompting to update “links to other files”. Why?. See below for why.)
The title basically says it. I have an .xls file originally created with Excel 2007. As far as I'm aware, it doesn't have any links in it, and Links... on the Edit menu is disabled (greyed-out). When I open it in LibreOffice 3.4.4, it asks
This file contains links to other files.
Should they be updated?
[Yes] [No]
I've always said No. Again, as Edit menu, Links... is greyed-out, apparently there aren't really any external links. I don't think the file even has internal links. It does have multiple worksheets in it.
I've seen the question I mentioned above, but the OP wasn't sure whether Edit | Links... used to be active and stopped getting the question, and the only answer there assumes that Edit | Links... was active, so clearly not an answer to what I'm looking at.
How do I fix the file so I don't get this question every time I open it (without changing my options to always update links)?
Immediately after posting the question, I thought: I wonder if saving it as an .ods file will fix it. So I did that, and then opened the .ods file. I got the question, but when I say "No," the Edit | Links... menu item is enabled, and when I go into it, I find what LibreOffice thinks are links to other files.
So I thought I had an answer, but sadly no. If I tell it to break both of the links it displays and save the .ods file, the next time I go back in, one of the links reappears on the list. Removing it again, resaving, and going back in don't help; I did it at least four times and the link just keeps reappearing.
Source: (StackOverflow)