# Tagged Questions

**2**

votes

**1**answer

72 views

### How do I convert dates from Open Office format to MS Excel format

I am trying to save a file in Libre Office as an excel file but Excel sees the data displays it as a negative number using a series of ###### symbols.
I believe the problem is to do with the two ...

**-2**

votes

**1**answer

43 views

### How to compare number with number in other column and get corresponding value

If suppose I have data like
column1 column2
504231 504109
504109 504201
504201
504299
then I used the following formula ...

**1**

vote

**1**answer

26 views

### Replicate a Calculation Sheet for each customer

I created a LibreOffice sheet, with the detailed list of expenses. And a second list with the names and debts of each neighbour.
However, I'd like to generate a personalise replica of the first ...

**1**

vote

**0**answers

214 views

### How to copy a column with conditional format by “value” - Libreoffice 4.1

I have a column that contains the data and a set of rules for the conditional format of the cells.
i.e. the conditional format changes the background colour of some cells if they are negative.
I ...

**3**

votes

**1**answer

501 views

### Find last value in a cell range that meets a condition

I'm looking for a formula in LibreOffice Calc to find the last value in a range that meets a condition. Here is sample data:
A B C D E F
...

**1**

vote

**1**answer

198 views

### Make a system call to calculate a spreadsheet cell value

Is it possible for me to make a system call in order to calculate a cell value in LibreOffice Calc?
To clarify, can I set a cell's formula to something like this:
=B3*C4-system("/my/amazing/script ...

**3**

votes

**1**answer

155 views

### Openoffce Calc - Substitute space with line break by function

in my calc document for example cell A1 has the value
A1:"AB 0123"
and in cell B1 I need the same value with a line break instead of the space like
B1:"AB\n0123".
How can I archive this?
I ...

**1**

vote

**1**answer

76 views

### Following Sunday based on date in LibreOffice Calc [duplicate]

I'm looking to create a calculation to display the following Sunday in day-month format based on the current date (=NOW()).
How can I do that?

**2**

votes

**0**answers

1k views

### Openoffice Calc - use cell font color in formula

I have some openoffice calc sheets where some rows are maked with
the font color red. All other rows are in the default font color.
Can I use the font color of a cell within a formula to react to ...

**0**

votes

**2**answers

2k views

### How to do a “great circle” calculation in MS Excel or LibreOffice?

I got two columns with latitude and longitude values. I need to calculate the distance between the points in every row. I'm trying to use the haversine formula as seen here:
That's the so called ...

**-1**

votes

**1**answer

115 views

### How to check if a date is in the future

I want to make a spreadsheet reporting daily and weekly goals for a certain quantity and their achievement, one reporting sheet per calendar week. Imagine that I open it on Wednesday, then I want to ...

**0**

votes

**1**answer

1k views

### LibreOffice/Excel: SUM an adjacent cell if the value in the conditional cell is equal to N (SUMIF(?))

Disclaimer: I have no idea what I'm doing.
In LibreOffice Calc (3.6.2.2), I'm trying to SUM all of the values in G:x (where range is G2:G529), where B:x is equal to a given number N.
The data set ...

**0**

votes

**1**answer

256 views

### Libreoffice Calc SUM returns ###

In Libreoffice Calc, sometimes the SUM function returns ### in the cell when referencing other sums. For example:
=SUM(x1+y1)
where
x1=sum(a1:d1)
y1=sum(a2:d2)
gives
###
in the output cell. ...

**2**

votes

**1**answer

3k views

### How to count how many rows contain particular values in a particular set of cells in libreoffice and excel

This Calc file : http://dropcanvas.com/521xc/107 have a sheet called "Etat" and a sheet called "Total".
In Etat.F20 I have a formula that counts how many rows in Total have the value "Bug" in the D ...

**0**

votes

**1**answer

394 views

### Rows disappear when removing duplicates libreoffice

I pasted a lot of data in Column A.
Then I removed duplicates:
Data > Filter > Standard Filter > More Options > No Duplication
Now I'm stuck in row 2632, i.e. I canĀ“t scroll up to rows ...

**0**

votes

**1**answer

242 views

### Find a Certain Cell based on other Cells in Excel/Calc

I have a spreadsheet:
Beans B-kg Chips C-kg
1.4oz/12 0.47544 6.5oz/20 3.679
1.48oz/12 0.502608 7oz/12 2.3772
1.86oz/12 0.631656 8oz/20 4.528
and a ...

**1**

vote

**1**answer

179 views

### Convert Text into a Formula in Calc

I have a bunch of cells in a spreadsheet like the following:
1.48oz/12
I am trying to replace the oz/ with * and then calculate it.
So far I have:
=REPLACE(G4,FIND("oz/",G4),3,"*")
which yields:
...

**0**

votes

**1**answer

1k views

### How flexible is the 'indirect' function?

My curiosity pushes me to ask this question.
If I were to have a series of functions that referenced a different column in a worksheet but all ended on the same row of data is there a way to point ...

**1**

vote

**1**answer

878 views

### Calc - How to combine rows from multiple sheets into 1 main sheet

I have several statements separating my various bank accounts in Calc, they all use the same columns and have a Date column which I sort them by.
I want to create a 'Overview' sheet which contains ...

**3**

votes

**4**answers

16k views

### How to separate number by its decimal point in Excel/Calc?

I've got a number in a spreadsheet like so: 28.686279
When I open this sheet in either LibreOffice Calc or Microsoft Excel, is there a formula function that returns the "whole number", I.e. 28. Also, ...