Questions tagged [libreoffice-calc]
Spreadsheet component of the LibreOffice suite
621
questions
0
votes
0
answers
19
views
LibreOffice Calc: How to get total for HH:MM:SS cells for graveyard using 24 hour clock
I have a week. From B28 to H28 I have start time. B29 to H29 I have End time the next morning. From B30 to H30 I have actual time from each day with this formula: =(24-B28) +B29. This gives me the ...
- 1
0
votes
0
answers
14
views
Applying a template to each sheet
I can create a template (.xltx), and then create new workbooks (.xlsx) based on it. But it seems, when I do that, the first sheet of the workbook alone is based on the template, and the later sheets ...
- 1
0
votes
1
answer
30
views
Error "Unrecognized database format" when opening a .mdb file
I have a .mdb file which I do not know which version of ms access was used to create it. When I try to open it, I get "Unrecognized database format" error. I tried using MS ACCESS 2003 and ...
- 61
0
votes
1
answer
46
views
How to apply a calculation on a table column with a range of value per cell
I have a large table where in the first column for some row rather than just a single value there is a range e.g.
I want to apply a calculation on all the data elements, but since in the first column ...
- 205
0
votes
0
answers
15
views
LibreOffice calc: create multiple chart series from column values
Sorry for the basic Calc question, but I'm very inexperienced with spreadsheet charts and need some help. I've tried searching for the answer but surprised that I can't find anything that answers what ...
- 131
0
votes
0
answers
33
views
How to use one table (ods) file as a base for another table file?
I have an accounting ledger table (.ods) file which should be the base for other table files.
This table file should be the base for other table files such as:
2023.ods
2024.ods
2025.ods
These are ...
0
votes
0
answers
22
views
How do I use a `TRUNC` function in a LibreOffice Basic script?
I have to write my own custom function that converts number of days since Jan 1, 1900 to a date. I am recreating some legacy code that does this but uses 32 days a month, 384 days a year (I am not ...
- 170
0
votes
0
answers
37
views
How to do nested if statements in Librecalc?
Are nested if statements supported in Librecalc?
According to this article about Excel, if statements should be nestable.
However, if I enter this formula into a Librecalc spreasheet:
=If(1>2, &...
- 7,600
0
votes
1
answer
10
views
troubleshoot: LibreOffice on Debian testing (gnome), insert current date shortcut non-functional
I can't seem to be able to figure this one out. I often want to insert the current date in a libbreoffice calc spreadsheet. But when I press the ctrl+; shortcut for this, I seem to switch to a ...
- 151
0
votes
0
answers
17
views
How to follow precedent into another sheet in LibreOffice Calc
I am trying to analyze complex data sheet full of formulas referring to another cells using Tools - Detective - Trace Precedents/Dependents.
It works fine until the source for the formula calculatiion ...
- 111
0
votes
4
answers
157
views
Excel: How to convert a column letter to the corresponding column number without using COLUMN() and INDIRECT()?
The standard solution seems to be =COLUMN(INDIRECT(letter&"1")) but there should be a better solution that avoids the volatile INDIRECT function.
Sample Column Letter
Corresponding ...
- 101
0
votes
1
answer
29
views
How to remove large white blocks from Excel preview in Google Drive?
Does anyone know how to remove these large white blocks from the preview of an Excel file in Google Drive? The file itself is somewhat old and has gone through many large changes, including ...
- 5,646
3
votes
2
answers
179
views
When filtering data, is it possible to put in a cell the value of the filter?
I have this spreadsheet (link to google drive --- notice that the online app is not showing the filtering, you need to download it...):
It works very well and lets me see the total when I filter ...
- 186
0
votes
0
answers
17
views
What's (if any) the formula to get multiple matching values in LibreOffice Calc? [duplicate]
Suppose I have the following data:
A
B
1
idx1
VAL1
2
idx2
VAL2
3
idx3
VAL3
4
idx4
VAL1
5
idx5
VAL4
6
idx6
VAL1
7
idx7
VAL1
8
idx8
VAL2
9
idx9
VAL5
10
idx10
VAL3
Is there a LibreOffice Calc ...
- 167
2
votes
1
answer
27
views
How to return in a cell all the matching values present in a column?
Suppose I have the following data:
A
B
1
idx1
VAL1
2
idx2
VAL2
3
idx3
VAL3
4
idx4
VAL1
5
idx5
VAL4
6
idx6
VAL1
7
idx7
VAL1
8
idx8
VAL2
9
idx9
VAL5
10
idx10
VAL3
For each "VAL[n]" ...
- 167
2
votes
1
answer
52
views
How to populate a cell with a comma separated list version of a list-like text?
Suppose I have the following data in a cell (note: list-like text with minus signs and line breaks):
- val1
- val2
- val3
How can I populate another cell with a comma separated list version of the ...
- 167
0
votes
1
answer
67
views
LibreOffice Calc: How to make AutoFilter changes permanent?
When I apply an AutoFilter to my LibreOffice Calc spreadsheet, it's natural to have some rows removed, like in this screenshot. However, it's been clunky dealing with it, and I don't like this ...
2
votes
2
answers
78
views
How to return multiple match results and output them to a cell as concatenated text?
Suppose I have the following data:
A
B
C
D
1
val1
Str1
2
ValM
Str2
3
val2
Str3
4
ValM
Str4
5
ValM
Str5
6
val3
Str6
7
ValM
Str7
8
val4
Str8
9
val5
Str9
10
ValM
Str10
I would like to:
find ...
- 167
1
vote
0
answers
22
views
Graphing coordinates in Calc using direction, distance, and speed
Suppose I have the following data:
Location
Direction (Degrees)
Distance
Speed
Origin
N/A
N/A
N/A
A
090
5
50
B
180
15
75
I can make a chart using the following data to show the path something ...
- 177
0
votes
0
answers
23
views
concatenate text to create math that can be calculated
I'm not sure if the title is descriptive enough! I've used the CONCATENATE formula to create the following:
=$'2022'.a17
It goes like this:
=CONCAT("=$'";TEXT(A20;"YYMM");"'....
1
vote
1
answer
51
views
Cumulative count of non-empty cells that aren't contiguos
I want to write a formula that gives the cumulative count of non-empty cells that aren't contiguous. Like an index count column for lack of a better term.
A - Count
B - mix of empty and non-empty ...
- 655
0
votes
2
answers
141
views
How to get the type of a cell in Libreoffice Calc?
I would like to know if a cell is a date, a number, a string or what. It would help a lot, exactly what/how should be done with it.
I think, librecalc knows it, because it affects the behavior of the ...
- 2,372
0
votes
1
answer
368
views
How to generate a column of unique IDs (UUIDs) in LibreOffice Calc?
How to insert a column of unique codes (UUIDs) in LibreOffice?
If that's not possible with LibreOffice itself, how could one generate UUIDs in a way so that they can be simply copy and pasted into a ...
- 329
0
votes
1
answer
59
views
How to combine the values from multiple rows in a column (separated by commas) where there are duplicates in another column
I have a large dataset where I want to combine the values of multiple rows in one column where there are duplicates in another column. See my Example below:
CURRENT DATA
Product Type
Email
Product 1
...
- 135
0
votes
2
answers
89
views
How to paste numbers with a different separator in LibreOffice Calc
I tried selecting the cells and setting their format as “Text, English (USA),” and set the language in the status bar to “English (USA)” as well, but when I paste data from this page, e.g.
Food
kcal ...
- 1,608
3
votes
1
answer
330
views
Include an "at" symbol ("@") in a text cell
I'm organizing some stuff in a spreadsheet. None of it should be interpreted as anything other than than text, but it does contain a lot of non-alphanumeric characters.
If I include the character @ in ...
- 133
0
votes
2
answers
312
views
How to solve freezing of LibreOffice 7 on Mac OS X Sierra issue?
Using LibreOffice 7 (Calc) on Mac OS X High Sierra.
It constantly freezes (turning colored wheel) so I have to force quit it.
How should I proceed to find the cause? Is there a log somewhere that I ...
- 954
0
votes
1
answer
29
views
LibreOffice Calc - do not apostrophe-escape dates when opening a file
I have switched system, and the old system had locale en_DK with reasonable ISO formats, the new one currently has en_US with "imperial" stuff, bad date formats etc. While I try to figure ...
- 111
0
votes
0
answers
61
views
How to make LibreOffice Calc correctly interpret the desired number format for Time
I am appending to a CSV file programmatically with Python, which currently contain timings in the format: 0:1:10 (e.g. representing 0 hour, 1 minute, 10 seconds).
When I open the CSV file in ...
- 1,327
1
vote
0
answers
20
views
How to set custom alpha in paired t test at libre office calc?
In libre-office calc I can perform a paired t test with alpha 0.05. In Excel I can change this value to e.g. 0.01. But Libre-office calc I couldn't find any setting how to change the intial alpha ...
- 111
0
votes
1
answer
45
views
How to use a non-contiguous range for diagram?
(Similar to question How do I select non-adjacent ranges in libreoffice calc?, but not identical)
I have a time-based series of data (date&time, value), and I have a diagram showing the data.
The ...
- 314
0
votes
0
answers
32
views
Libre Calc - color code a number of columns based on one column
I have a number of columns of data (one for each month) that I would like to color code based on the average value for all months. So each row in each column should be compared to the row in the '...
2
votes
1
answer
86
views
Libreoffice: Fill down without affecting format
If I fill cells in libreoffice by dragging on the small black rectangle (on the bottom right corner of a cell), it doesn't only fill the formula (which was my intention) but also the format (font, ...
- 143
1
vote
1
answer
149
views
LibreOffice Calc: Use different max value for color scale conditional formatting in each row
I have a dataset like the following (only much larger): Each row represents a task with a maximum number of points available in column B. Every column starting at C represents a Person with their ...
- 205
0
votes
0
answers
77
views
LibreOffice Calc button control: delayed response to mouse left-click only
I have the following problem when using LibreOffice Calc 7.2.2.2 (x86) on Windows 10 pro build 19043.1288 (I had uninstalled the 64-bit version and replaced it with the 32-bit version in the hope that ...
- 111
1
vote
1
answer
240
views
How to highlight the min and max value in a row wise manner?
In LibreOffice Calc (6.0) I try to highlight the minimum and maximum of each row in group of column (col A to E)? Any idea how to solve this?
- 131
1
vote
1
answer
45
views
Libreoffice Iterative Calculation is not automatically converging
I have a circular set of equations with a corrector function built in to converge on a solution. When I change the input value, the dependent cells begin to calculate but stop prematurely. Should I ...
- 508
0
votes
2
answers
238
views
Why does index match get wrong result in Microsoft Excel?
The formula that I use is
=INDEX(F:F,MATCH(K2,A:A)*(B:B=L2),0)
- 1
0
votes
2
answers
66
views
"Update Links when Loading" - what exactly it updates?
Tools > Options > LibreOffice Writer > General > Update Links when Loading
What is this setting is actually used for? Which links does it update?
(For example, when I moved my document ...
- 833
1
vote
1
answer
79
views
Date + 14 days. The date is generated by a regular expression
I have a speadsheet in which the first column is like
Column 1
-------------------------
Foo 1234 (22.09.21)
Baaaar 5678 (20.10.21)
And a second column contains dates only and is generated from the ...
- 833
0
votes
1
answer
105
views
Remove metadata from XLS in LibreOffice
Having a XLS file I created for my father back in the day.
I am using a copy of this for myself to generate some documents I export to PDF.
Issue is that the file's metadata put my fathers name as the ...
- 3
0
votes
0
answers
46
views
How to make a conditional format apply to all cells in Libreoffice Calc?
I've made a conditional format color an entire row green when a formula is true. However, it's only applying to a specific range of rows, and I'm wa to make it apply to all rows.
My formula is:
AND($...
- 7,600
0
votes
1
answer
126
views
How to edit a column in Libre Office Calc to set all URLs as clickable links?
I have a column with many URLs, some of them are not clickable (as blue), I need to select one by one, then ctrl+x, ctrl+v, then enter to transform URLs as text to HTTP links.
It's very annoying, I ...
0
votes
1
answer
42
views
Combining several rows to one data range to circumvent the 1024 column restriction
I want to work with a larger data set (deaths per week since 2000), exceeding the 1024 columns restriction of LibeOffice Calc.
My source has got 52 (or 53) numbers per row for each year. I'Ve manually ...
- 101
0
votes
1
answer
220
views
LibreOffice Calc "Find" command matches row indices
I wanted to search a certain value in a column, so I naturally selected the column, CTRL+F, and entered the value to the box. However, LibreOffice matches all cells with a row index containing my ...
- 133
0
votes
2
answers
293
views
How to *format* time duration as hh:mm:ss in LibreOffice/Excel?
I would like to copy the time duration (in seconds) to a cell, then have it displayed as (hh:)mm:ss or similar. For example:
Input: 0.49
Display: 0.49 or 0.49s or 490ms (any will do)
Input: 857.26
...
- 133
0
votes
1
answer
105
views
How to create a summary report from a LibreOfficeCalc PivotTable
This is a question about the feasibility of solving a problem with LibreOffice/Calc.
I have a table of data of many years with basically the following columns:
date/time ( Y/M/D time )
quantity
Now, ...
- 111
0
votes
1
answer
460
views
Get minimum value from column B where column A is less than or equal to cell value
In LibreOffice Calc I have created a die rolling sheet. In A6:B1005 I list the values in column A and number of die rolls with that value in column B. Example with a d10:
Die Value
Number of rolls
1
...
- 139
1
vote
1
answer
86
views
How to reject LibreOffice Calc autocomplete suggestion?
LibreOffice Calc likes to aggressive suggest cell content based on a prefix.
It is easy to accept the suggestion by hitting ENTER.
But how to I reject it, which is something that is needed even more ...
- 1,094
0
votes
0
answers
21
views
LibreOffice Calc — line chart double zero unit at XY-axis crosspoint
The line chart in LO Calc has two zeroes, one for Y-axis and one for X-axis (see attached pic). This is wrong in my opinion and looks bad. I've been trying to figure out how to get rid of one of the ...
