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
2
votes
1answer
220 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
25 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). ...
0
votes
0answers
15 views

Libreoffice Calc paste over filtered rows

I have a problem pasing text over filtered rows in Libreoffice calc. Imagine I have 1000 rows and, when I apply a filter over them, the result is 50 visible rows. Well, if I copy a text with 50 ...
-3
votes
2answers
52 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
67 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 ...
0
votes
0answers
14 views

OpenOffice Calc - Highlighting the higest value in multiple columns

Question is similar to a the following question: OpenOffice Calc - Highlighting the higest value in a column but it's not quite what I'm looking for. I have a table that looks like the following I'...
0
votes
0answers
20 views

Filtering long lists in OpenOffice Calc

I have 3 keyword lists separated into columns: Column A, Column B, and Column C Column A is the primary list, containing 100s of keywords. Columns B and C are secondary lists, containing many, but ...
1
vote
1answer
59 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
0answers
36 views

How do I set Calc to copy to clipboard in csv format?

Looks like when I select cells and press ctrl+c, Calc exports it in a tab-separated format to the clipboard. How can I make it export in csv format, instead?
0
votes
1answer
92 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
33 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
107 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
123 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
30 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 |...
0
votes
2answers
37 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
80 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
69 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
93 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
69 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
69 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
30 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
150 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
1k 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
64 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
442 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 ...
0
votes
0answers
22 views

Office Calc, matching Column header to cell [duplicate]

Snippit from current spreadsheet I'm having trouble with a formula for 'G' Column, I want to have the header the cell with a (negative or positive) value to be copied into the 'G' (or Code) cell that ...
1
vote
1answer
268 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
244 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 ...
4
votes
2answers
2k 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 ...
1
vote
2answers
1k 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
57 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
306 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
605 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
140 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
139 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 ...
2
votes
2answers
3k views

Enter the same data into multiple, non-adjacent cells in OpenOffice.Org Calc

I want to enter the same data into multiple cells that are not adjacent. I have selected the cells and pressed CTRL + ENTER but this doesn't work.
0
votes
0answers
102 views

How to change scale of units in OpenOffice

I have made a chart in OpenOffice but in x-axis the values are very small ~10^-3. How can I just change the numbers so that they are scaled without changing the data? Here's a picture of the x-axis: ...
0
votes
1answer
1k views

Open Office Calc lookup vlookup

I am trying to return a value to a cell, columns C to H depending on what is chosen in the drop down in column B. The figures I need to return are held on a separate sheet . Any help with this would ...
-2
votes
1answer
258 views

Convert minutes and seconds over 60 seconds to whole minutes and seconds

I have times consisting of minutes and seconds displayed with a decimal as a separator, and the seconds can exceed 60 (two digits, so up to 99). For example, 3.92 equals 3 minutes and 92 seconds. I ...
2
votes
1answer
601 views

OpenOffice Calc macro in Python - macro triggered by a click of a button, how to get a name of that button?

I am really new to this and OpenOffice Developer's Guide is too difficult for me to understand or I'd help myself. I have a large number of rows with data and in each row I want a button that would ...
1
vote
0answers
93 views

Does LibreOffice Calc calculation time out or have any limits?

I have a relatively large spreadsheet (2300 rows, out to column EL) and lately the auto calculate has been unreliable. I would have to click on a cell I was sure was incorrect, hit F9 and see its ...
0
votes
1answer
141 views

OpenOffice calc colorize data points differently

I gathered some FTP file size statistics for my boss and I wondered if OpenOffice calc had the ability to colorize the data points differently. I want to color green the data points within one ...
0
votes
2answers
1k views

How do you get multiple if statements working within the same open office calc cell

I am trying to sort a column out in open office and need the cell values to stay between 10 and 0. I currently have an If statement as follows: If(X6>0;(((S7/V7)-($B$6))/$B$4)+$X$5;0) I need to ...
0
votes
1answer
84 views

Open Office Calc - What formula can be used to find a value in a column based on another value being reached in a different column

So I have 2 separate columns (Column A / B for this matter) which use different formulas to increment for approximately different amounts. I need a formula / equation which when column B reaches a ...
3
votes
3answers
6k views

Highlighting every other row in LibreOffice (or OpenOffice) without conditional formatting?

Besides using conditional formatting, is there any way in LibreOffice Calc (or OpenOffice Calc) to highlight every other row in order to make the spreadsheet easier to read? LibreOffice has a serious ...
2
votes
1answer
471 views

Preventing capitalization in OpenOffice Calc (or LibreOffice Calc)

In OpenOffice Calc (and LibreOffice Calc), the first letter of each cell is auto-corrected to be capitalized. Is it possible to prevent this, without affecting the behavior of the other applications ...
0
votes
1answer
57 views

How to apply a fomula multiple times at different intervals to entire column? OO Calc

I am using Open Office Calc on windows 8.1 Pro I have the following data already sorted nicely. A B C --+----------------------- 1 | Bmw Gold £80 2 | Bmw Silver £60 3 | ...
0
votes
1answer
274 views

Unexpected Behaviour using VLOOKUP in OpenOffice Calc

I am using VLOOKUP in OpenOffice Calc on Windows and trying to use a lookup table to populate some other cells. My Lookup table ranging A1:B6 is Car 4 Wheels Florida No Bus 8 ...
0
votes
1answer
177 views

Multiple EqualTo Strings in Conditional Formatting IF statement

I am using Open Office Calc on Windows When using Format > Conditional Formatting and selecting Formula is drop down under Condition 1, I am typing my formula IF($B10="BMW") This works great. ...

1
2 3 4 5
7