# Questions tagged [spreadsheet]

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

**0**

votes

**1**answer

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

**0**

votes

**0**answers

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

**1**

vote

**2**answers

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 + ...

**0**

votes

**0**answers

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

**1**

vote

**3**answers

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

**4**

votes

**1**answer

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)...

**0**

votes

**0**answers

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=...

**0**

votes

**1**answer

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

**1**

vote

**1**answer

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

**0**

votes

**2**answers

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

**-1**

votes

**1**answer

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 (...

**0**

votes

**1**answer

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

**0**

votes

**1**answer

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

**3**

votes

**3**answers

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

**0**

votes

**3**answers

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.

**1**

vote

**0**answers

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

**1**

vote

**1**answer

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

**2**

votes

**0**answers

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

**0**

votes

**1**answer

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

**0**

votes

**3**answers

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

**0**

votes

**1**answer

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

**0**

votes

**1**answer

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

**0**

votes

**1**answer

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

**0**

votes

**0**answers

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

**1**

vote

**1**answer

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

**1**

vote

**1**answer

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

**0**

votes

**1**answer

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

**0**

votes

**1**answer

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

**0**

votes

**0**answers

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

**0**

votes

**1**answer

40 views

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

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

**0**

votes

**2**answers

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

**0**

votes

**1**answer

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

**0**

votes

**2**answers

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

**0**

votes

**4**answers

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

**0**

votes

**2**answers

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)?

**0**

votes

**1**answer

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'.

**0**

votes

**1**answer

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

**0**

votes

**1**answer

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,...

**0**

votes

**1**answer

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

**0**

votes

**1**answer

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

**1**

vote

**2**answers

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

**1**

vote

**2**answers

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

**0**

votes

**3**answers

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

**2**

votes

**2**answers

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

**1**

vote

**0**answers

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:...

**0**

votes

**2**answers

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

**0**

votes

**1**answer

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

**0**

votes

**2**answers

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

**1**

vote

**1**answer

64 views

### Can I (and If so How?) use the MATCH function to search two (or more) different spreadsheets for a value?

First I'll explain what I already have (which works), then explain my question, which is about expanding it:
I have two different spreadsheets that will be worked on at the same time, I needed to see ...

**2**

votes

**3**answers

24k views

### Put a checkbox in a cell in Excel v15 for Mac

I am trying to make a checklist in Excel.
Basically I am trying to make a cell:
Into a checkbox that can be checked or unchecked, and saved with the spreadsheet:
It's super-duper easy to do it in ...