Questions tagged [openoffice-calc]
OpenOffice's spreadsheet application. Tag questions about macro programming or formulas with macros or worksheet-function respectively
351
questions
0
votes
1
answer
23
views
Excel/LibreOffice Calc - Setting a value dependent on another value as that second value rises at a uniform rate
Essentially the value in C24 is dependent on the value in C18.
The value in C24 should be 33 if the value in C18 is 2230 or less.
The value in C24 should be 39.5 if the value in C18 is 2950 exactly.
...
1
vote
1
answer
18
views
Open Office Calc, round to next largest multiple of 5?
I'm trying to get a formula to work that always rounds to the next largest multiple of 5.
For example, if I calculate 248, and I use MROUND, I get the correct 250, but if the calculation returns 246, ...
- 13
0
votes
1
answer
48
views
How can I copy and paste a string of characters into libreoffice calc with one character per cell?
ACDAEACCDDAEFFAFCCDFABAADACCCDDAFEDGABEGFCADEHBHACAGHACDEDECDAFAFAFCACDCDEAFCDFCGHDDFF
1 ACD.E..........F......................................................................
2 ...A...CD...F...........
- 215
1
vote
0
answers
27
views
Is there a way to find the two smallest numbers in a column in openoffice without repeats
The obvious but incorrect answer would be Small(Range;2) except that if there is more than 1 cell with the smallest value then it will return the smallest value instead of the 2nd smallest value.
...
- 21
0
votes
0
answers
19
views
Define a sheet as a function in Libre Office Calc
I have a sheet. If i write a number in one cell, the sheet will output a different number in a different cell after doing a lot of calculations.
I want to get the output for a lot of different numbers,...
0
votes
0
answers
10
views
How do I make custom date cells in Libre Calc?
So I want my dates to look like this:
12.9. - 18.9.
19.9. - 25.9.
"Date of monday - date of the next sunday" in the same cell. I can't figure out how to make the program calculate these for ...
- 1
0
votes
1
answer
18
views
How do you get multiple statements working within the same open office calc cell
I am trying to create a check register in open office. I want the Balance column to remain blank if I don't enter a transaction. If I do enter a transaction I want it to do the math and display the ...
0
votes
0
answers
18
views
Excel graph from two tables/matrices
I have two tables with values;
tableA
A B C
1: x1 x2 x3
2: x4 x5 x6
3: x7 x8 x9
tableB
A B C
1: y1 y2 y3
2: y4 y5 y6
3: y7 y8 y9
Is there a way to use these values to create a graph ...
- 101
0
votes
1
answer
70
views
Solution to display a long list of words in a single cell : autogroup / sum in libre office calc
I have a long list of words (1000+) where each word maps to another list of words (around 100 per word of the first list).
Currently I have it stored in excel (libre office calc, in fact) in the ...
- 3
2
votes
1
answer
416
views
Timestamp (date/time) system used by LibreOffice Calc / OpenOffice Calc spreadsheet applications?
What system does LibreOffice Calc / OpenOffice Calc use to represent timestamps (dates/times) as numbers?
It's apparent that they don't use the standard integer epoch times that are common in computer ...
1
vote
2
answers
92
views
Excel / LibreOffice Calc: find item in column with latest first occurrence?
In a Calc or Excel sheet I have a column with bunch of names. Each name can occur one or multiple times.
Each name has a 'first occurrence' i.e. the first row in which it appears.
I'm looking for an ...
- 982
4
votes
1
answer
590
views
Issue with IF statement in Libreoffice Calc
Here is the statement
=IF(L7>7,"$500",IF(L7>11,"$1200",IF(L7>15,"$2000",$0)))
I simply need this to resolve a value based on the set conditions. I've tried ...
- 43
0
votes
0
answers
122
views
Character encoding in LibreOffice Calc of an existing XLW file
I have an existing .XLW file (created in the early 1990's with Microsoft Excel). LibreOffice Calc (7.2.7.2) opens it and displays it very nicely.
However, since it contains characters in a non-...
- 237
0
votes
1
answer
291
views
Sort LibreOffice Calc rows through all columns
I have a list of items with set of properties. Some of the properties are unique, some are the same. I want to sort the properties through all items to see which items have equal properties.
Maybe it'...
- 1
0
votes
2
answers
510
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
3
answers
109
views
Turn a row's background black if cell has #VALUE! - OpenOffice Calc
I'm currently playing around with formulas and made one that works perfectly for me.
Some row will get the value #VALUE! due to them being empty when imported and I am trying to make the whole rows ...
- 105
0
votes
1
answer
791
views
Extract string after a delimiter like "/ " OpenOffice Calc
I have a file that converts inputs for me into an SKU-number (Stock Keeping Unit).
The names and attributes I have are in one line and one cell like so:
Temperature Displaying Smart Ring / Rose Gold – ...
- 105
0
votes
0
answers
17
views
Open office Assign a number to a color
What I'm trying to do is calculate the number of hours worked according to the color and the person, all of this to be determined in the nombre d'heures column in front of each person.
Knowing that ...
- 1
0
votes
1
answer
379
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
0
answers
64
views
grep-like filter in office suites (OpenOffice)
The GNU grep command allows to search inside the incoming stream of data, for example inside the files. It have two similar and very useful CLI parameters: -A & -B:
-Annn shows nnn lines following ...
- 101
0
votes
1
answer
137
views
Automatically reference to a cell on other sheets
I have a libre calc file with many sheets and I want the first sheet to contain a list with the other sheets names and next to each one the contents of a specific shell of every one of those sheets.
e....
0
votes
1
answer
769
views
LibreOffice Calc: How to use OFFSET Function when Selection Intervals should increase?
Assumed we have this table style in sheet 1:
A B C D E F G H
1 0.1 0.2 0.5 0.7 0.4 0.5 0.9 1.0
2
3
In sheet 2 there have to be averages made of cells ...
- 135
0
votes
1
answer
506
views
Retrieve European Central Bank currency rates XML into LibreOffice Calc cells
The premise is to retrieve current currency rates into a Libre/OpenOffice Calc spreadsheet. Previously I did it using a JSON service, with manual tweaking of substrings, which isn't all that user-...
- 779
1
vote
1
answer
70
views
Calc/Excel aggregating data with same ID
Hello i have to solve this problem in Libreoffice Writer or excel.
I have a table like this:
ID V1 V2 V3 ...
1 10 20 30 ...
1 30 40 50 ...
1 5 7 8 ...
.. .. .. ....
- 11
1
vote
1
answer
357
views
Conditional formatting in libre office calc
I am trying to achieve the following:
For each columns RN, RO and RP, color the background of the cell in red if it is less than the number in the RR column on the same row, and in green if it is ...
- 259
0
votes
1
answer
184
views
In LibreOffice Calc, is it possible to use a complex function and a data range as arguments for a MIN() function?
I'm going to simplify a bit to hopefully get at the crux of my question.
I have a spreadsheet in which a cell has a massive formula. (Again, I'm simplifying to avoid a question that is so long no one ...
0
votes
0
answers
426
views
How to set the color of the input bar in LibreOffice Calc spreadsheets (for dark theme/mode)?
In LibreOffice Calc, I have been able to almost configure a usable dark theme/mode.
To accomplish this, go to Tools > Options... > LibreOffice > Personalization and set the dark theme, and ...
1
vote
0
answers
79
views
OpenOffice Calc: Is it possible to create conditional formatting to highlight a row based on checkmarks?
This is kind of a two-part question.
Is it possible to create conditional formatting to highlight an entire row with a certain color based on whether the checkmark object in the row is selected? For ...
1
vote
1
answer
173
views
Spreadsheet: Find Another Cell With The Same Value
I have a column of names; Each name has exactly 1 duplicate, Is there a function I can use to find the row number of the 2nd copy of the name.
So the values should be 2,1,4,3,6,5,7,6 and so on.
In ...
- 113
0
votes
0
answers
32
views
Can OpenOffice Calc AutoInput reference data on a different sheet?
I've set up a spreadsheet in OpenOffice for project estimation at a contractor. The first column is a description of the item or service that I'm adding into the price. Since there are many commonly ...
1
vote
1
answer
3k
views
How can I escape commas in a CSV file?
I thought there were two ways to do this: either enclose the field containing the comma in quotes, or place a backslash before the comma. However neither of these is working; when I load up the CSV ...
- 439
0
votes
1
answer
521
views
Conditional formatting with Libreoffice Calc activated with a letter and the condition being applied to more than one cell
Is there a way to do condition formatting based on a letter and having that format apply to more than one cell?
The desired result is shown below, where typing either A, B, or C in column "B"...
- 421
0
votes
1
answer
32
views
Every time I open a csv OpenOffice Calc resets cell formating
I am using OpenOffice Calc to view and change a particular csv. This csv has some numbers that start with 0 and some dates. I need to have both the numbers and the dates in a Text cell format. When I ...
- 421
3
votes
1
answer
2k
views
Both '.' and ',' for decimal separator in LibreOffice Calc?
I have read How to set decimal separator in Libre Office Calc? but I haven't found an option for this:
Is it possible to set both . and , as decimal separator in LibreOffice Calc?
In France it's , ...
- 1,571
0
votes
1
answer
271
views
Runtime error when iterating over cells in LibreOffice Calc
I have a table where the values are very large, in order of millions. Basically I want to iterate through all the cells(In a particular column) and divide each of them by some number (like 10,00,000).
...
- 103
-3
votes
2
answers
74
views
How to subtract (compute delta) between two sheets?
I have two snapshots of data in big tables on two different sheets. Basically the sheets have identical content but the numbers are from different moments of time. I need to compute the delta between ...
1
vote
1
answer
191
views
Is it possible to combine two IF(AND functions into one? (OpenOffice Calc 4.1.7)
Is it possible to combine two IF/AND functions into one? (OpenOffice Calc)
I have two IF/AND functions that work perfectly well, however I need them to both work on the same line. Is there anyway to ...
- 13
1
vote
1
answer
308
views
How to copy a new line from libreoffice calc to txt?
I have the following function in cell 'B2' a libreoffice calc document:
=CONCAT(A1,CHAR(10),A2,CHAR(10),"end")
Which, with in cell A1="function plotName = getPlotName(plotData)" and A2=" plotName ...
- 383
0
votes
1
answer
319
views
How can I stop Open Office Calc from changing the data?
I am using Apache Open Office Calc to view CSV data. I just noticed that Calc rounds the data on saving, reducing its precision, or at least I think that's what's going on.
I show below the same data ...
- 273
0
votes
1
answer
44
views
Displaying values in a spreadsheet up to X days away from current date
I'm looking to get some assistance in untangling the mess that is a data entry and summary spreadsheet I've had for the last few years in LibreOffice Calc. The general layout of the spreadsheet's ...
- 209
0
votes
1
answer
456
views
How to track frequency of multiple randbetween() outcomes in a single cell
I am attempting to simulate a large quantity of dice rolls using Excel or LibreOffice Calc. I managed to find here a formula to generate multiple random numbers based on values specifying how many ...
- 1
1
vote
1
answer
423
views
OpenOffice Calc import special characters in CSV incorrrectly?
I'm trying to import a CSV file to OpenOffice Calc, but the program is reading special characters incorrectly.
For example, one of the fields in the CSV is "Puma La Cerámica", but Calc imports it ...
- 521
1
vote
1
answer
331
views
Is there any formula for OpenCalc which can count rows that contain some values but do not contain other values?
I have the following cell structure in OpenCalc:
|--A--|--B--|--C--|--D--|--E--|
|--1--| A1 | B1 | C1 | D1 | E1 |
|--2--| A1 | B1 | C1 | D1 | E1 |
|--3--| A1 | C1 | D1 | E1 | F1 |...
- 113
1
vote
2
answers
2k
views
Spreadsheet- How to update the value of the same cell
I want to update the value of a the cell based on the current value of that cell and value of another cell like A1=A1+A2what is the correct formula
- 111
2
votes
1
answer
148
views
OpenOffice Calc: Graph ON/OFF times
I have a simple spreadsheet with every row containing times (24h) something was switched on and off again. So it looks something like this:
10:20:15 10:25:36
11:09:02 11:15:54
13:00:38 13:01:...
- 133
0
votes
1
answer
140
views
Calc pie chart for 25000 rows with long tail
I have a file that contains 25,000 rows, each row containing a number, looking like this:
4000
3000
1500
700
...
2
1
1
1
From this I want to make a pie chart.
The first few pie parts will be ...
- 10.5k
1
vote
1
answer
28
views
Is there a way to average data that has a common value in a column?
I have some CSV files that I have to format, which is the easy part, but then I have to create an average for values in each column based on the hour. The hour is in its own column and the values are ...
- 11
0
votes
2
answers
403
views
Calc print several sheets in same page
I have a Libreoffice Calc spreadsheet that holds around fifty sheets (each one with no more than a dozen valued rows); this is good for data organization — each sheet holds a sequence of events ...
- 205
0
votes
1
answer
132
views
Long Calc Sheet - subtotal at bottom of page, transfer to next page?
Assume i have a LibreOffice Calc sheet spanning multiple pages in printout. Is there a way to calculate a subtotal for each page and print it at the bottom of the respective page as well as at the top ...
- 10.1k
0
votes
0
answers
243
views
Summing Euro amounts in Libreoffice calc
I have a spreadsheet with a column full of Euro amounts. This spreadsheet is created from a CSV and the amounts are interpreted as strings (text) rather than as numbers.
I've been trying to get the ...
- 118