Questions tagged [openoffice-calc]

OpenOffice's spreadsheet application. Tag questions about macro programming or formulas with macros or worksheet-function respectively

Filter by
Sorted by
Tagged with
0
votes
1answer
14 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 ...
0
votes
1answer
39 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-...
0
votes
0answers
16 views

Libreoffice calc multiple horizontal window splits

On a libreoffice calc spreadsheet I would like to have two horizontal splits to yield three window areas. I have the top several lines split off and frozen to provide headings, title and unit ...
0
votes
0answers
7 views

OpenOffice Calc understanding this user defined cell format

I'm using OpenOffice Calc to write to a template spreadsheet from HMRC. They're very strict in their formatting so they've laid everything out and I need to fill in the data. However, when I add a ...
0
votes
0answers
16 views

How to use multiple if statements in open office calc

The question is to Write the function to Grade of total marks of all subjects. Criteria of grade: A+ >= 90 B+ >= 75 C+ >= 50 D+ >= 33
1
vote
1answer
25 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 ... .. .. .. ....
0
votes
0answers
26 views

In a LibreOffice Calc (or OpenOffice Calc) spreadsheet, how can you maintain multiple groups of columnar data?

I have a LibreOffice Calc spreadsheet that contains 3 sets of data. It is organized like this: SUBJECT A SUBJECT B SUBJECT C Date X Y Date X Y Date X Y Date X Y ...
1
vote
1answer
31 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 ...
0
votes
1answer
25 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
0answers
25 views

How do I keep just the first row of each unique occurence of a value in a column in LibreOffice Calc?

I have a sheet in Calc that looks like this: A B C D E 1 Call # Title Barcode Status Check-out date 2 070.49 GUIB The ...
0
votes
0answers
47 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
0answers
43 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
1answer
36 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 ...
0
votes
0answers
19 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
1answer
304 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 ...
0
votes
0answers
20 views

Is it possible to create a run chart (X=date, y=some numeric) in Calc where some specific dates are annotated or called-out in some way?

I'd like to create a chart from some time-series data and I'd like to be able to annotate the chart with a call-out that says "on this specific date something changed". I'm fairly certain I ...
0
votes
1answer
94 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"...
0
votes
0answers
12 views

Single Column, 1000's of Rows, Print as 2 Columns on Single Page

Imagine this... Just column A, every row down to say... 200, populated with first names. John Jane Bob Mark Joe Timmy When I print this... I want it to come out as: John     Mark Jane     Joe Bob      ...
0
votes
1answer
20 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 ...
2
votes
1answer
585 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 , ...
0
votes
1answer
125 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). ...
-3
votes
2answers
57 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
1answer
91 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 ...
1
vote
1answer
112 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 ...
0
votes
1answer
181 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 ...
0
votes
1answer
39 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 ...
0
votes
1answer
245 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
vote
1answer
230 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 ...
1
vote
1answer
42 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 |...
1
vote
2answers
222 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
2
votes
1answer
102 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:...
0
votes
1answer
86 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 ...
1
vote
1answer
22 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 ...
0
votes
2answers
192 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 ...
0
votes
1answer
88 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 ...
0
votes
0answers
93 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 ...
1
vote
1answer
75 views

Open Office Calc: How to find a set of values (like any digit) in a string?

Modeled off a solution I found for Excel, I tried the following in Open Office Calc and got an error: =FIND({1,2}, A1) The goal is to find the coordinates in the string at A1 where either the number ...
4
votes
0answers
173 views

In LibreOffice Calc, how can I change the cell selection black ring to something lighter for dark backgrounds?

In LibreOffice Calc, how can I change the cell selection black ring to something lighter so it can be easier to see for dark backgrounds? I will attach an image to show what I mean. It would be nice ...
1
vote
1answer
2k views

How can I add duplicate rows to LibreOffice Calc to make there be one per day?

I have some data like this (it's stock market data) 29/07/2015 35972.82 32056.2 17751.39 30/07/2015 35961.86 32046.43 17745.98 31/07/2015 35848.13 31945.08 17689.86 03/08/2015 ...
-1
votes
1answer
79 views

Is it possible to “countif” cell contain value of specific order?

I work on a warehouse using WM apps that resulting exported data for daily transaction. We use open office calc for spreadsheet data processing. The data contain cells for location, with format like: ...
1
vote
1answer
858 views

How do I lock some columns and some rows in a Libreoffice Calc worksheet?

I am using Libreoffice Calc 5.2.0.4. I am building an appointment book. This book has many sheets/ pages. I am not experienced, so I was experimenting on different sheets. And now I have three ...
1
vote
1answer
749 views

If cell contains value then 'Column header'

Looking for help A B C D E F 1 Food Petrol Rent Wages 2 **Food** 12 3 **Petrol** 35 4 **Rent** 120 5 ...
2
votes
0answers
471 views

Dropdown Lists in both Excel and Open Office

I have an Excel workbook which works as a time sheet. Users select the project they worked on and how many hours they spent on it that week. The project can be selected from a dropdown list showing ...
6
votes
2answers
4k views

Handle pasting of decimal with dot (1.7) when locale is expecting comma (1,7)

I've a datasource where the dot is used as decimal separator, i.e. : 1.49 means "one and almost-half". Cannot change it. This number must be copy-pasted to a numeric cell in Excel or Calc. The file ...
2
votes
2answers
2k views

Adding a percentage to every cell in a column in LibreOffice calc

I have a spreadsheet with about seventeen hundred rows and I need to add 3% every cell in a column. Example: Column ------ 1800 1500 5435 3583 3544 etc I need to figure out what 3% of 1800 is then ...
-1
votes
1answer
59 views

Matching cell to a range and inputing matched cells corresponding cell

I used "=IF(MATCH(A1;E$1:E$4;0);A1)" in Openoffice (FROM THIS AWESOME SITE) to search a range to match a cell. Is there a way to report the corresponding cell value of the matched range cell? E.g. ...
0
votes
1answer
421 views

Sync sheets in Libreoffice

Hopefully someone can help. I have an eccommerce dropship website on Woocommerce which I update via CSV using WPAllImport. I currently download a CSV Spreadsheet from my supplier which contains ...
1
vote
2answers
836 views

How to just add conditional formatting to a cell without overwriting the cell's direct formatting

I have different cells with different background colors. Depending on a Conditional Formatting I'd like to add borders around some of these cells. Applying the borders works but the cells' ...
0
votes
1answer
150 views

Match 2 values on excel / calc

I have this table structured as described below with some data for example: +-------+-------+-------+-------+-------+ | name1 | code2 | data3 | name4 | code5 | +-------+-------+-------+-------+------...
2
votes
1answer
172 views

Open Office is not showing in English, even though the English language is selected

I don't understand what happened with this Open Office installation. It's on a college computer (Linux), so I can't do a reinstallation without admin access. I have English language selected in ...

1
2 3 4 5
7