# Questions tagged [spreadsheet]

Questions about spreadsheet software in general and interoperability between spreadsheet programs.

476 questions
68 views

### libreoffice calc - average of sums

Let's say I have some data on columns B through E that I want to average as follows: for each row: sum the four columns then take the average such sum Do I have to get another column for the sum and ...
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 ...
64 views

### Excel: How do you add a number to each cell in a range then multiply them together?

Let's say I have the following values in cells: A B C D 1 0.1 | 0.5 | 0.3 | 2.1` I am looking for the formula with which I can calculate without intermediate cells (1 + 0.1) * (1 + ...
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 ...
145 views

### Excel “If Then” formula

I'm trying to write a formula which says "if sum of cell F24 to I24 plus sum of cell L6 to L9 sum up smaller than 50, show value in cell L9 in cell N9, and if not, subtract sum of cell F24 to I24 plus ...
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)...
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=...
119 views

### Using a Column Name in a VLOOKUP

I have a spreadsheet that takes data from the other 52 spreadsheets and totals it. The columns in the sheet correspond to the names of the other spreadsheets (using dates, i.e., 01/01/2017 column ...
954 views

### Excel - compare three cells, if same then something, if not then something

=IF(F9=G9=B9; H9*C9;"Problem") This is the formula I'm using, but it always results in "Problem", even when the three cells have same text in them. I want to compare the cells, and if they are the ...
551 views

### How to add a row without changing formula in Numbers app on Mac

I have rows of data sorted by date (descending). Therefore it is important that a new row can be added above all other rows but before the "header" row. However, when I select "Add row above" the ...
174 views

### Excel swaps month & day in csv import

So, I download a csv from an analytics website. When I try to open on excel (or google sprd, open office, ...) the date is wrong. 01/01 -> 1st Jan (correct because is palindromic) 02/01 -> 1st Feb (...
49 views

### EXCEL: Need formula that returns last time a value was >= current value

In my current Excel spreadsheet, I have the date in column A and S&P 500 closing prices in column B. I'm trying to write an formula that returns the date for the last time that closing price was ...
102 views

### Google Sheets - need formula for three cells of 5 that match

I need an excel-style formula for Google Sheets. I have five columns. I want to create a sixth column that contains a formula that examines the cells each row for the 5 columns. Each cell in each row ...
1k views

### How to create an array of values in LibreOffice Calc?

I'm using LibreOffice 5.1.4.2 Calc and need to calculate the internal rate of return for a certain payment (say, in cell A1), a certain number of times (say, 100). If I had the value repeated 100 ...
147 views

### How can I calculate age in MS Excel if the year of birth is provided in one column?

No DOB, only year of birth data is there in one column, need to calculate corresponding age in the other column based on today’s year/date.
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 ...
241 views

### Visualising 3D data with a spreadsheet

Is there a (possibly spreadsheet?) program that enables manipulation of 3D data? The problem: There're several tables (A, B, C, …) Each table (an m x n matrix) represents measured quantities of a ...
440 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 ...
5k views

### Comparing two Excel spreadsheets to highlight changes

I need to compare two large, multi page spreadsheets and combine them into one spreadsheet showing the data that was in the original one and also what it was changed to in the second one? I have ...
81 views

### How can I count the number of cells that match a given month in Numbers

I'm using Apple numbers to run some calculations on CSV export data from Mailchimp. The data is (loosely) like: ID Email, SignupAt 01 me@example.com 2015-09-04 02 you@example.com ...
88 views

### lookup several values in a table and return the sum of those values

I'd like to lookup several values in a second table and add them up. let me go into more detail I have one table with numeric values and id's id||amount >----------< 3||12 34||8 16||40 ...
43 views

### How can I put the data from a pivot table that updates everyday into a sheet and add the new values at the bottom and not inbetween?

Good day, I have read hundreds of these and can not seem to find a way to achieve this. Problem. I have a sheet with a pivot table that updates daily at 06:00 from a database. The pivot table also ...
20 views

### Automation of Grouping Dates Weekly

I start out with an excel spreadsheet that looks like this; I then manually set column widths and change the alignment of the header row so eventually I get something like this (I also apply ...
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 ...
271 views

### Find highest number of consecutive outcomes for each person in list

I've got an Excel list that looks like this (with the name in column A and the score in column B): N. Nam Score 1. Jim 7 2. Jim 4 3. Jim 4 4. Jim 7 5. Jim 7 6. Jim ...
525 views

### Change adjacent cell where a cell matches a certain value

If I have the following table: ------------------ | X | helloworld | ------------------ | X | random1234 | ------------------ | X | random5678 | ------------------ | X | helloworld | -----------------...
117 views

### Pasting exact values in Excel

I apologize if the question title isn't very informative. I've got a long list of numerical codes that I'm trying to paste into a column in Excel. The problem is that the vast majority of these codes ...
193 views

### Is there a way to make a single bar chart that tracks color based on date in Google Sheets or Excel?

I've attached an image that's the best way to describe it. As the date increases from left to right, a different color for that range is displayed. Some sample data: Date Temperature Week 1 ...
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 ...
40 views

### how to convert spreadsheet cells to units of time?

This is the goal: +------+---------------------+---------+---------------------+ | A | B | C | D | +------+---------------------+---------+-------...
430 views

### Is there a spreadsheet version of .rtf?

For a text document, there's multiple format options: .doc, .docx, .odt, .rtf, etc. I use .rtf for all text documents, because its compatible with nearly everything. My question is, is there a ...
86 views

### Ticket report - Auto populate from other worksheets into specific form

Hello ladies and gentlemen, I'm quite a dummy in excel and programming in general and I have a question related to building a report: I must present a ticket report of which tickets are opened, how ...
350 views

### Excel function that works out the average by first excluding all zeros and then excluding the minimum and maximum value

I have around 300 values on an excel spreadsheet (many of which are zero) and I want to work out the average by excluding the zeros, the highest value and the lowest value after zero. What would be an ...
22k views

### Excel 2016 not updating automatically even though automatic set in calculation mode

I set calculation mode to automatic but when I enter "=Now()" inside a cell I do not see the time updating even when formatting is changed to show seconds. That worked in prior Excel versions. Is ...
1k views

### How can I sum with partial parts of cells?

Let's say I have a spreadsheet look like this. A |B ---------------- 1 |1.0 2016-05-07 2 |0.5 2016-04-12 3 |x.y yyyy-MM-dd How can I sum those first values(1.0, 0.5)?
782 views

### Excel Sheet Format, Showing Parent/Child nodes & Information

I currently have the following format in excel: Does anyone have any suggestions on how to display this information in a 'prettier' way? I will have many nodes and many 'information columns'.
160 views

### Comparing dates using logical operator

I have this excel sheet which I will be used to send notification The first 3 columns contain date of expiry and next 3 column notify 1 , notify 2, notify 3 contain a date which is 30 days prior to ...
475 views

### Merge/concatenate/append spreadsheet documents, not spreadsheet contents

There is much information here and on the web about merging/combining the data of several spreadsheets into a single one. I want to do something much simpler but cannot find how. I have several files,...
498 views

### Create graph of values, mean per day, grouped by variable

I have a spreadsheet containing the following values (slightly simplified): Date - day in number - Temperature - Windspeed - Hours of sun. Data example: 01-09-2000 - 01 - 20 - 15 - 9 02-09-2000 - 02 -...
2k views

### How to add prefix or suffix to range of cells in Excel?

I have cell A in excel full of images name without the extension I want to add ".jpg" as a suffix to these ranges of cells however I need to skip non text cells by not adding anything. Any help to ...
234 views

### Highlighting Groups of Alternating Names in Excel

I have been trying to find a good way to do this but it seems like I can not find a good Conditional Formatting formula to work with what I need. I am trying to do something along the lines of this ...
61 views

### Merge / glue exported pdf from spreadsheet application

Spreadsheet applications offer to export tables as pdf. Often, the table is to wide to fit on a single page (without scaling), so several pdf pages need to cover the full width of the table. Let us ...
662 views

### Transpose data from row/column that matches certain criteria

I conducted a survey that asked for a Score and a Priority for each question. The response data lists each component in individual rows, so that it's treating Score and Priority as different ...
1k views

### AVERAGEIFS values that are not NA

I have the following AVERAGEIFS that averages column I grouped by the values in column B = AVERAGEIFS(B:B, A:A, A:A) Problem is, sometimes, the column B sometimes contains an NA() error. How do I ...
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:...
602 views

### Date stamp when editing rows in spreadsheet

I have a spreadsheet which needs date stamp for each row as soon as there are changes being made in cells within that row. For example, I change something on cell B14 or E14 or G14 and today's date ...
72 views

### Categorize spreadsheet lines - ala SQL GROUP

I want to consolidate a series of name-value pairs by name and sum the values. The pairs are in two columns of a LibreOffice Calc spreadsheet, about a hundred rows. Input: Output, to another part of ...
113 views

### #VALUE! Error while trying to use a referencing cell in a formula

I would like to use the value inside a cell which continuously gets updated from a web page. The cell called C20 linked to another sheet called ratedatasheet and that sheet displays a page and table ...