Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [spreadsheet]

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

0
votes
1answer
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
0answers
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
2answers
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
0answers
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
3answers
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
1answer
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
0answers
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
3answers
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
3answers
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
0answers
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
1answer
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
0answers
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
1answer
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
3answers
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
40 views

how to convert spreadsheet cells to units of time?

This is the goal: +------+---------------------+---------+---------------------+ | A | B | C | D | +------+---------------------+---------+-------...
0
votes
2answers
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
1answer
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
2answers
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
4answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
3answers
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
2answers
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
0answers
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
2answers
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
1answer
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
2answers
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
1answer
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
3answers
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 ...