**0**

votes

**1**answer

77 views

### Get frequency distribution of several columns partitioned by one other column

I'm doing some basic survey analysis. I want to break down responses to various questions based on Male or Female. Each row is a response, each column is a question. Column A has the Male/Female ...

**1**

vote

**0**answers

165 views

### In Open Calc, how do I copy a paste cells that contain references to other workbooks?

I have an OpenOffice Calc sheet with cells containing formulas that refer to another file, e.g: 'file:///C:/path/to/other.xls'#$Sheet1.A1:B2.
When I copy the cells from one sheet to another in the ...

**2**

votes

**1**answer

2k views

### Spreadsheet VLOOKUP: Search in second column and return first

In Excel or OpenOffice calc I have
| Foo | Bar |
+-----+-----+
| A | 1 |
| B | 2 |
Is it possible to use VLOOKUP to search a value in the Bar column and return the value in the Foo column?

**0**

votes

**1**answer

404 views

### Where is openoffice.org add-on located after installed (Ubuntu)?

I installed a tool which now appears in Openoffice Calc under Tools->Add-ons.
I thought that this tool would appear in ~/.openoffice.org/3/user directory, but I cannot find it.
I am especially ...

**1**

vote

**2**answers

3k views

### Open Office crashes, recovers, crashes again

After completely reinstalling my laptop due to apparent registry corruption, I've encountered a problem with Open Office:
I open a simple Calc spreadsheet, it comes up normally, but then after ...

**1**

vote

**1**answer

108 views

### OpenOffice Calc - getting ### as the result to a formula

So I've got a spreadsheet with around 5 different rows, each one with a specific value (specifically a range of cells E2 - E8) and a "Total: " cell at the bottom, whose formula is this:
=Sum(E2:E8)
...

**0**

votes

**1**answer

1k views

### How to make LibreOffice Calc to connect points in a holed series chart with lines?

I've got a time series data table with one function (row) value available for every month and the second for every 3rd month (every quarter), having holes (empty cell values) for every 2nd and 1st ...

**3**

votes

**4**answers

4k views

### Finding min/max values in column where cell matches something

I have a spreadsheet laid out kind of like this:
Date Project#
2012-01-01 130
2012-01-02 153
2012-01-03 153
2012-01-04 130
2012-01-05 130
2012-01-06 130
And I want to get ...

**2**

votes

**0**answers

541 views

### OpenOffice Calc weighted average

I need to find the result of
((C1 * 5) + (C2 * 4) + (C3 * 3) + (C4 * 2) + (C5 * 1))/(5*6/2).
I need to do it for 200 columns, not only 5. I currently use two sheets with sheet 2 containing a ...

**2**

votes

**2**answers

527 views

### Modify value of a cell using the old value as part of the new one?

I have a table in which row 2 has tons of columns. Each of those columns in row 2 has a varying value in the range of 1-1000000 represented by X in the example below.
How can I add modify the cells ...

**3**

votes

**2**answers

2k views

### Excel/Calc/Whatever: Flip my axes - ie reflect my table across the diagonal

Lets say I have a table and I want it flipped across the diagonal. So instead of X by Y its Y by X...
Before:
A B C
RED 1 2 3
BLUE 4 5 6
...

**2**

votes

**2**answers

2k views

### OpenOffice Calc - Highlighting the higest value in a column

So I've got this spreadsheet open using OpenOffice Calc (ver 3.3.0) and its set up a little like this:
A B C D
1.name quantity price total
2.foo 10 ...

**1**

vote

**1**answer

253 views

### Delete cell content in Libre (Open) Office based on the cell value

I have a huge csv file (tens of thousands of rows) that I need to filter based on different criteria. After trying to find a proper CSV editor, I decided to use LibreOffice Calc. CSVed is great, but ...

**0**

votes

**1**answer

1k views

### How to parse numeric prefix on string in Open Office Calc?

In Open Office Spreadsheet I have cells with strings like "123kg" "3,45m" "0,5g" "345apples" etc.
Which is the function that can parse the numeric prefix on these numbers? So I get "123" "3,45" "0,5" ...

**1**

vote

**1**answer

341 views

### Drop down List Not working in Open Office

i have created a file using MS Excel and saved the file as ODS but it will open on read only mode works perfectly but once i saved the file drop down list not working

**3**

votes

**1**answer

5k views

### Open Office Calc: Convert a date to timestamp

I have a column with dates in M/D/Y like this:
8/8/2007
8/9/2007
8/10/2007
8/13/2007
8/14/2007
8/15/2007
8/16/2007
I would like to convert them to a unix timestamp (in a new column). Would anyone ...

**1**

vote

**2**answers

4k views

### LibreOffice pivot table group by month

Im having data like this:
Date Customer Amount Month
09-01-12 A 20 =A2 (formatted so it shows month name)
10-01-12 B 10 =A3 (formatted so it shows month name)
...

**1**

vote

**0**answers

42 views

### How do I insert a rowbreak with OOo Calc macro Basic

With OOo, how can I insert a rowbreak in Calc using a Basic macro?

**3**

votes

**1**answer

7k views

### How to reference variable sheet in OpenOffice Calc?

I want to reference a cell in another sheet, and the formula I get when pressing = followed by changing sheet, clicking on the cell and pressing Enter, gives me
'Sheet2'.A2
Which is fine, except ...

**1**

vote

**2**answers

19k views

### Pull row of data from one place in spreadsheet to another

I have a list of names and their assorted properties.
I am trying to find a way to quickly move an entire row's worth of data into a different sheet so I can manipulate it.
Currently I can ...

**1**

vote

**2**answers

2k views

### Open Office Calc time to decimal

My task is very simple but I cannot figure out how to do it.
I am setting up a time sheet in OpenOffice Calc with 3 columns. The first two columns are Time formatted columns that represent my start ...

**0**

votes

**1**answer

1k views

### How can I convert LDAP Timestamps in Libra Office to a date time format

If I query the directory, I get the following results.
ldapsearch2.4 -x -b "dc=localhost,dc=com" "cn=auser" "+" | grep stamp
createTimestamp: 20100407122221Z
modifyTimestamp: 20100407122221Z
...

**2**

votes

**2**answers

2k views

### unable to use datediff function in apache openoffice calc

I am unable to get a result by using this command =DATEDIFF('dd', '2007-08-01', '2007-09-01') in apache openoffice calc.
Even using two columns of dates and using the formula ...

**6**

votes

**3**answers

5k views

### How do I get a chart in LibreOffice Calc to have regular time intervals when the data is not regular?

I have an area chart in LibreOffice Calc where I would like the X axis to be measured in one month intervals spaced evenly apart.
The data going into the graph, however, is not regularly spaced at ...

**2**

votes

**1**answer

2k views

### Retrieve content of different cells based on input

I have a cell X where the user can enter a number. I then need to take that number N and get the value of another cell Y based on that.
The scenario is this:
The user can enter a product number and ...

**15**

votes

**10**answers

31k views

### Export Excel graphs as vector graphics files (e.g. SVGs)?

How can I export a graph generated in Excel as an editable vector graphics file, e.g. a SVG?
When I right click on a graph in Excel the 'Save as Picture...' option only gives non-vector formats (like ...

**0**

votes

**2**answers

689 views

### Spreadsheets: How do I perform functions on a set/array described by a row of (count, value) pairs?

I have excel 2000, google docs, and the latest open office calc available to me. I'm after a solution in any of those, ideally.
I have a multiset of data described by rows of (count, value) pairs. ...

**0**

votes

**1**answer

348 views

### How do I get numbers before and after a colon within a cell in Calc?

I have some cells which contain times entered in by users, which are written in the following format: mm:ss. There are never any hours. The seconds always have leading zeroes. The minutes may or may ...

**1**

vote

**1**answer

6k views

### How to copy the value from a cell to another (using a formula), but not formula from the source cell

I'm making use of NOW() function at many places in my spreadsheet, which is basically a checklist document.
So my requirement is that for each step, it should record the datetime at which that step ...

**0**

votes

**1**answer

88 views

### Stop open office calc from entering in all pages at once

I must have click something by mistake. Now when I enter info in a single cell in one page, it ends up in the same cell in all of the other pages. How can I undo this please? Thanks very much.
...

**2**

votes

**1**answer

2k views

### Can two rows be reordered into one column in OpenOffice/LibreOffice Calc?

Is it possible in Open/LibreOffice Calc to take the values from a set of two or more rows, and display them in one column? In order words, make a separate column which derives its values from the ...

**1**

vote

**1**answer

78 views

### Can I use two cells in the same column as a Y axis in Libre/Open Office?

I have data arranged as following:
What I'd like to do is create a graph that looks something like the following (which I generated with graphics software):
So, for example, to generate the ...

**1**

vote

**1**answer

1k views

### Is it possible to create a chart in OpenOffice calc using columns and rows?

Is it possible in OpenOffice Calc to create a single chart where one set of data is derived from columns, and another set of data is derived from rows?

**1**

vote

**1**answer

862 views

### Is it possible to create a combination line and area chart in OpenOffice Calc?

Is there a way to create a graph that is a combination of lines and areas in OpenOffice Calc?
Currently the only way I know how is to make two charts, make the background of one transparent, and lay ...

**0**

votes

**1**answer

173 views

### Math function: What's the “this line” placeholder?

When I want to multiply a field I can say =J9*K9.
But what if I want this over a selection of rows? I've selected Line 6-25 and for all I want to say =J{THIS_LINE_NUMBER}*K{THIS_LINE_NUMBER}*.
How ...

**2**

votes

**1**answer

2k views

### Sort Numerically in OO Calc functions alphabetically

Using Open Office 3.3.0
Calc will sort a list of numbers not in numerical fashion, as would seem reasonable, logical, and rational, but in alphabetical order.
Eg: Calc thinks that 103,1000,etc come ...

**1**

vote

**1**answer

1k 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 ...

**0**

votes

**1**answer

1k views

### how to find cells that have a certain text in open office's calc (excel might work also)

I need to calculate averages on my expenses spreadsheet, so I figured out I could do something like this:
- get all cells that have the text "electric bill"
- get the cell at the right of that one, ...

**3**

votes

**1**answer

2k views

### How to set 2 conditions / criterias for VLOOKUP / LOOKUP / etc in OpenOffice Calc (or Excel)

I have this spreadsheet that started as a silly aid for a game (Mafia Wars 2), but grew into a tricky spreadsheet question.
In the game your character have 9 "slots" for weapons and armors, 1 for ...

**2**

votes

**0**answers

176 views

### How do I capture filter information from a DataPilot table?

In OpenOffice Calc, how do I capture filter information from a DataPilot table?
Specifically, I have a DataPilot table which I graph on a chart, filtering on a field not appearing in the table.
I ...

**2**

votes

**1**answer

4k views

### OpenOffice Calc - how to insert BLANK in a formula?

In Calc, as in most spreadsheet applications, an IF() function exists that can be used to control behaviour, for example inserting this formula in cell C1.
=IF(A1>B1;"A";"B")
will display A in ...

**1**

vote

**1**answer

278 views

### Rearrange cells in a Open Office spreadsheet, from data in cols to data in rows

I have this spreadsheet (Open Office) and all the data is filled in so they form a col,
and I would like to change this so they fill a row instead.
Kind of mirror the doc in both axis.
This is how ...

**2**

votes

**1**answer

808 views

### How can I bypass the import dialog when opening CSV with OpenOffice?

A (Python, but that's not really relevant) script I'm writing will issue a command line call to open a CSV file with OpenOffice (OO). I need OO to skip over the import dialog box while opening the ...

**2**

votes

**1**answer

379 views

### Are there nonlinear optimization implementation for OpenOffice Calc?

Are there nonlinear optimization implementation for OpenOffice Calc?
I found one: http://extensions.services.openoffice.org/project/NLPSolver . But the methods provided there are evolutionary ...

**4**

votes

**1**answer

3k views

### Saving to CSV file always adds quotation marks in OpenOffice

I'm trying to use OpenOffice Calc for editing CSV files with TAB as delimiter. No matter what I do, Calc is insisting on adding quotation marks (") to the strings. If a cell content is number only, ...

**2**

votes

**1**answer

4k views

### How do I create a line graph which ignores zero values?

I want to create a graph showing trends over time, calculated from a formula of various data.
However, some data is missing, resulting in zeroes in the calculated data which the chart uses.
When ...

**8**

votes

**3**answers

4k views

### Permanently and globally stop openoffice calc from autocorrecting dates/time

When I type "15:45" or "21/12" openoffice Calc changes it to "15:45:00" and "21-12-2011".
How do I disable this globally and permanently?
I know you can use "Format cell..." and then select text, but ...

**1**

vote

**2**answers

383 views

### Advanced spreadsheet auto-formatting

Is it possible to autoformat spreadsheet like this
IF A1 = C1 then A1:E1 cells background becomes light gray
I use OO Calc, but it's ok if you know how to do it in MS Excel or Google spreadsheet

**3**

votes

**1**answer

2k views

### How do I make a random selection in a spreadsheet using more than one criteria

I have a spreadsheet created in OpenOffice (V3.3.0 in Windows 7 32 bit) that I use as a database of my DVD collection. After asking a question in SuperUser, I have it so it can pop up a random title ...

**0**

votes

**1**answer

1k views

### How can I determine if a number is between two values in OpenOffice Calculator?

I'm trying to write a formula that will do different things depending on which number range the number inputted to it lies in.
If the number is between 0-40 inclusive, I want it to return 0.
If the ...