125 questions
58 views

### libreoffice /open office calc - visualise formula

Let's say I have two alternatives in columns C,D like so: row 6: sample size row 7: sample mean row 8: sample standard deviation and then want to do a t-Test, which requires (amongst other things)...
126 views

### Excel: Based on value of cell in column A (three possible values), and cell in column B (integer), determine the value of cell C

I am trying to work out a problem as follows: Column A: one of three possible values. {apples, oranges, pears} Column B: an integer. The value of column C should be calculated based on the previous ...
284 views

### Sum values in specific rows based on whether the row has a value that belongs to a range

Here's what I'm trying to do: I have a table that has several metrics (example: revenue, cost, etc.) for about a hundred different accounts. I can't modify this sheet because it gets auto-updated by a ...
86 views

### How to fold a horizontal series into a vertical one

I have a set of data that is contained in a .csv spreadsheet arranged like this: name | 2006 | 2007 | 2008 | 2009 -------------------------------- foo | 28 | 48 | 395 | 3829 bar | 83 | 475 |...
3k views

### Numbers conditional formatting based on a different cell in the same row

I have a Numbers spreadsheet, with "quantity required" and "quantity in stock" columns, then each part is on a row. I'm trying to use conditional formatting to turn a cell yellow when # required = # ...
2k views

### Open Source Spreadsheet with Sensitivity Analysis

Is there any good Open Source spreadsheet program that has a solver for linear programs (either included or via an extension) that does a proper sensitivity analysis (shadow prices etc)? It seems like ...
940 views

### Excel: how to illustrate a budget constraint that depends on more than two factors in a table? Calculate how much of a house you can afford

Imagine that you want to make calculation of how expensive flat/house you can afford to buy. You know how much you can spend on housing per month (100). You also know that there are three factors that ...
36 views

### How to show a value of a Cell as it's displayed

I have a cell A2 that shows the value 001. I want to concatenate that value of 001 to a string to show REQ-001. The cell that I want to show this string I have =CONCATENATE("REQ-",A3). But the ...
352 views

### How do I import multiple csv files in gnumeric?

I am trying to import multiple csv files into gnumeric so that each one is a separate sheet in the workbook. This is relatively simple in Excel but I cannot work out how to do it in gnumeric.
7k views

I'm updating a spreadsheet that refers to data in another worksheet =NameofWorksheet for some reason I get #NAME? in some of the cells, even when the actual path is correct. The only way to overcome ...
442 views

### Apple numbers: How can I create a filtered column (B) with only unique values from column (A)?

I'm using Apple Numbers. I have a table with data like: A | B ---------+--------- Spain | U.K | Spain | Germany | U.K | Italy | And I would like to set table B to include ...
619 views

### Histogram of dates in Google Sheets or OpenOffice

I have a list of dates that I would like create a histogram of. date 5/21/2014 5/20/2014 9/16/2014 5/28/2014 6/8/2014 6/8/2014 5/28/2014 5/20/2014 8/11/2014 7/19/2014 8/11/2014 The chart's x-axis ...
179 views

### Unable to enter data into cells in sc

I'm trying to use sc, the classic Unix spreadsheet calculator, mainly because I want to be able to look at some csv files nicely formatted in my terminal. I installed sc 7.16 but am seeing some ...
211 views

### Merging CSVs with some common columns (though columns are in different places)?

I'm looking to merge CSVs that have common columns, but the columns aren't always necessarily in the same place. The CSVs do have a header row. I just want to try to get the data appended to the ...
182 views

### Need to convert password protected Lotus 1-2-3 v4 file

I have a Lotus 1-2-3 version 4 password protected file that I need to open. However, I don't have Lotus 1-2-3. I have not found a converter that will convert a password protected file. Any ...
620 views

I need to make a spreadsheet template that needs to be support both English and Spanish. I know Office 2010 has an option to translate content into desired language but its a manual process. Is there ...
22 views

### In Numbers/Excel how can I do a SUMPRODUCT only on rows matching a specific criteria

Let me explain my spreadsheet a little bit. Here is a good screenshot of it: I have created this spreadsheet essentially because I have gotten tired of the usability issues of just about every ...
18 views

### Excel spreadsheet/workbook data connections: Two way communication with applied filter?

So let me pose you guys a scenario. There are several users all inputting data into a single spreadsheet. This work is initially assigned to these users by a manager who, after the data is input, ...
73 views

### In Excel, is there a way to autofil formulas that link to other files with information from other cells in the current spreadsheet?

I have a master spreadsheet that collates data from other spreadsheets, and shows this via linked cells - for example, I would have ten identically set out spreadsheets, each showing a different total ...
61 views

### Show contiguous time periods without data in Libre Calc pivot table, pivot chart?

A minimal example: our table has sales in 2015, 2017, but not 2016. Date Sales 01/02/15 \$500 10/11/17 \$1000 You make a Pivot Table: Data > Pivot Table > Create Row Fields: Date Data ...
88 views

### How can I transfer data between spreadsheets using vlookup if spreadsheet changes each month? Can I set it to auto-update?

I have the above data in two spreadsheets. I receive the top rows. (raw data) Then total them using formulas. (middle rows) Then need to move them into a separate spreadsheet. (bottom rows, desired ...
56 views

### Excel on Mac - First worksheet overwritten with the second

So I'm new to Mac and I'm working with a spreadsheet in Microsoft Excel. It has one worksheet in it initially. While I'm working with it, I start putting things into a second worksheet. When I'm done, ...
212 views

### Dynamic timeline spreadsheet with selective stickiness?

I'm trying to create my first spreadsheet ever, but there's one function that I'm finding very difficult to find guidance for from the internet, just like the title of this post felt a bit clumsy to ...
67 views

### Excel: Conditional formatting, simplifying retrieving data from a range of rows

I am trying to create a schedule for a limited number of trainings (around 50) that are distributed between a limited number of venues (around 15), and I am looking for a smarter approach than the one ...
194 views

### Show Excel Cell Only If Another Has Data

I am trying to format the below Excel sheet so that when a date is entered into cell J1, cells G24 - G37 populate with a date a specific to what I need. I currently have the cells formatted as follows:...
15k views

### How can I create sub-sheets in Microsoft Excel?

I have a workbook in excel that displays information from multiple processes and each of these processes has multiple sub-processes. I can display information for each process easily using one sheet ...
294 views

### How to create a formula which finds a cell that matches a range of other cells then produces a number if the cell appears.

am having trouble with finding a suitable formula for my spreadsheet in excel. In column A i have a list of postcodes (439 postcodes). In column B-BL i also have postcodes (these postcodes are in ...
85 views

### Conditionally generate Excel sheet

I currently have an excel sheet that is used to record daily a bunch of parameters. Currently, I have a folder for each year, containing a folder for each month, inside of which I have an excel file ...
39 views

### What's going on with my Excel formatting?

I have a spreadsheet in Excel with a few columns that have a different format from the rest of the sheet. I inserted some new cells (old ones moved to the right). None of the old cells had this ...
44 views

### Can I create a sheet which sums groups of N rows in LibreOffice Calc?

I have a LibreOffice Calc spreadsheet which has this structure APPLES ORANGES BANANAS 5 0 10 15 2 12 3 2 105 ...
70 views

### Excel conditional formatting copy in table

Is this known a issue? When I add a new row to my table by inserting email, my conditional formatting does not work properly. As you can see in the picture, only one format works, the other one does ...
683 views

### Splitting excel sheet into multiple sheets based on two columns

I have a table of 10000 rows X 50 columns. The table is all in one sheet and I am planning to split that sheet into multiple sheers based on values in two columns (columns number 5 and 1, ...
45 views

### Deleting rows in Excel based on individual date range

I have seven Excel files containing information about admissions of several hundred patients to various hospital departments, and data appertaining to these (length of stay, type of discharge etc.) ...
15 views

### I cannot modify a password-protected excel spreadsheet despite the only other user with the file open is viewing it as “Read Only”

Title. Even though another user does not have the password to modify the spreadsheet, their viewing of the file is preventing me from getting to the password screen and is forcing me into "Read Only" ...
54 views

### Pulling data from a range of spreadsheets into a single spreadsheet (where sheet names change)

At school we have various spreadsheets to track the progress of our students within their different subjects. To allow us to keep track of the progress we always have a sheet named after the current ...
62 views

### Find first occurrence of a small sequence from a long list in Excel

I have a long list of words in separate cells in a row in an Excel spreadsheet.  For example A B C D E F G blue red yellow blue green red purple ... I ...
31 views

### Data Entry Beginner Asks: How do I 'section' out rows of a spreadsheet to belong to a product/item/object?

I am a complete beginner to data entry and wanted to know how to accomplish the following with the sample dataset provided below. Search through quantity column for "equal to or greater than value of ...
46 views

### libreoffice calc - compute range based on boolean values

Let's say I have a range OFFSET(\$AA:\$AA,start_,0,stop_-start_,1)~OFFSET(\$BD:\$BD,start_,0,stop_-start_,1)~OFFSET(\$CG:\$CG,start_,0,stop_-start_,1) And let's say I have three boolean fields named ...
60 views

### libreoffice calc - vary column in indirect

Assume sheets that have some values on columns B-K. Each sheet has R2=AVERAGE(INDIRECT("b"&\$O\$2):INDIRECT("b"&\$O\$3)) R3=STDEV(INDIRECT("b"&\$O\$2):INDIRECT("b"&\$O\$3)) for, say, O2=...
47 views

### How to create an automatically updating alphabetical list of unique occurrences in a range of cells?

To illustrate, let's say we have a range of 'sheet1'!\$B\$2:\$F\$8 that looks like: Dennis Charlie Frank Dennis Aaron Charlie Frank Erik Aaron Charlie Aaron Dennis Charlie ...
477 views

### How to check the values are increasing or decreasing between different columns?

In the table below each row has a different behavior. Note that, In the first row, the columns "pool1" the values are decreasing. Also decreasing for columns "pool2" and "pool3". In the second row ...
32 views

I have two spreadsheet. Both spredsheets have an email field. Spreadsheet 1 has 400 email fields and the other has 1000 email fields. The 400 emails are in the 1000 email spreadsheet. The 1000 email ...
49 views

### Which versions of MSExcel support the 'WEBSERVICE' function?

Pursuant to Excel formula to take out quotations , I saw that it's possible to use a form of web-querying in certain versions of MSExcel. Incidentally, what I saw was: =WEBSERVICE("http://finance....
191 views

### LibreOffice SpreadSheet Conditionally copy value from one cell into a second cell?

I'd like to have the calculated value of a cell be carried over to another cell if the result is less than a given value. For instance, cell A2 is defined as =A1*.75 If the result is less than x, I'd ...
472 views

### LibreOffice calc; is it possible to automatically make cells coloured according to the HTML code entered into them?

I am creating a LibreOffice calc spreadsheet designed to be basically be a customized version of the table entitled "Default element colors, by atomic number:" on this page http://jmol.sourceforge.net/...
47 views

I want to have a string value prepended to a XL-style formating in a spreadsheet program (gnumeric). The current formatting is [\$-f4f2]hh:mm which should take 1:00 PM as input and convert/display it ...
219 views

### Is it possible to recover cells with hidden content wiped out upon opening with LibreOffice

Some of my .xls Microsoft Excel files contain cells with hidden information formatted using custom code ;;; to deter casual viewing. I made the switch to LibreOffice a year ago and converted all the ...
544 views

### enteries in column are automatically changed to upper case letters

I am very new with Open Office and spread sheets in general. In a column whenever I add a new entry it changes all the letters to upper case. Is there a way to disable this? If not, many entries ...