1
vote
0answers
56 views

Conditional Formatting a date/time comparison

The function I'm working on specifically compares & colors a daily recurring time range set in 2 different cells. A specific date in the target cells (produced by a formula) compare to that daily ...
1
vote
1answer
118 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
1answer
93 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
0answers
725 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 ...
1
vote
2answers
240 views

How do I drag down complex calculations in open office.

For example if A1 contains 30 and in A2 I write =A1-6 then I'll get 27 in A2 and if I click the little black squair in the lower right hand corner of A2 and drag it down then I get a column that looks ...
2
votes
2answers
143 views

OpenOffice Calc regex returns wrong value - what's wrong with my regex?

I have a column of text with values concatenated by my bank, as in a typical monthly statement. Here is an example (column A240): OCT 26 BALANCEPROTECTOR PREMIUM $10.05 I'm trying to set the ...
0
votes
1answer
75 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
1answer
5k 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 ...
0
votes
2answers
531 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. ...
1
vote
1answer
758 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
1answer
752 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
1answer
1k 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 ...
0
votes
1answer
3k 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 ...
3
votes
1answer
1k 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
1answer
921 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 ...
0
votes
1answer
890 views

Cell value string to Int in OpenOffice Calc?

I have "1 hour", "2 hours", in a column in Calc, and I would like to extract the numbers only. I've tried VALUE(A9), but it doesn't work.
3
votes
2answers
1k views

Open Office Calc - Round to 5?

Is there a way I can have a cell round a number to the the nearest 5? For example, the number 114 would round to 115, or 62 would round to 60
2
votes
2answers
2k views

Get adjacent column value in OpenOffice.org Calc

I'm trying to get the value of adjacent columns in OpenOffice.org Calc. I have several rows, each with a column that should have its value incremented by one when a column immediately to the right ...
0
votes
1answer
413 views

Count the number of contiguous empty cells at the end of a row

I have rows of data, some cells are empty and some contain a number. I need to identify how many contiguous empty cells exist at the end of each row. I've tried using a string of nested IF ...
12
votes
5answers
36k views

How to create a formula for every row in a column in Google SpreadSheet?

I use three columns. A, B and C. In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2. How can I do so I don't have to type in the new formula in ...
4
votes
4answers
729 views

Spreadsheet forumula questions to pick a random DVD

I have a list of my (279) DVD’s in an OpenOffice Spreadsheet (version 3.3.0) on my Windows 7 laptop and I want to create a formula to randomly pick a film to watch (for occasions when I can’t make up ...
0
votes
3answers
1k views

Excel: How to sum values in a column based on multiple criteria

I have a list of criteria in row 20, like this: A20: "My Company, Inc." B20: "Client Expenses" C20: "Travel" The data is below, like this: B40: "Client Expenses" C40: "My Company, Inc." D40: ...