Questions about using in-cell functions/formulas in spreadsheet software.

**0**

votes

**2**answers

477 views

### Excel 2010 line chart; limiting data points to fewer than the max

In Excel 2010, how do I limit the number of data points on a line graph to the latest (or last) 4 data?
I update the spreadsheet often, but only the latest data is graphed and reported.

**0**

votes

**1**answer

22 views

### Spreadsheets Formula Check In/CheckOut Autopopulate Data

I am working on a spreadsheet (I am doing it in Numbers since it will easily let me work on the file on my mac, iPhone, iPad and online at icloud.com)... but this is essentially also an excel question ...

**0**

votes

**1**answer

18 views

### Auto-fill rows from columns in another sheet?

I'm using Google Doc's Spreadsheet at the moment and I'm attempting to create the same view in another sheet.
First Sheet:
(yes I need these merged cells)
Second Sheet:
Unfortunately Google ...

**1**

vote

**1**answer

16 views

### How do you auto update row count in a formula that yields a percentage?

How does one get a formula to auto update when a row is added when this formula is meant to yield as a percentage, which is directly linked to a pie chart? For example: my formula reads
...

**1**

vote

**2**answers

41 views

### Excel- How do I find common text values from multiple (>2) columns?

I have 9 columns of text data and need to know the common terms that are found in all nine columns (or possibly different combinations of columns). I can do 2 columns ...

**0**

votes

**2**answers

56 views

### Conditional Matching Based on Datetime

I have a set of names and datetimes on two sheets. One sheet we'll call LargerSheet and the other we'll call Sheet1. On LargerSheet each name has multiple datetimes associated with it. On Sheet1 ...

**1**

vote

**1**answer

4k views

### Automatic copy rows with matched column to other worksheet

Now I have a worksheet named sheet1 like the left side of image
I want to automatic copy all rows of sheet1 that have the column C = NO to worksheet sheet2 like the right side of image. And each ...

**0**

votes

**2**answers

12 views

### How to use SUMIFS function for summing values if criteria range exists in a range?

My data:
A B C
a 3 d
b 1 a
c 8 e
d 5
I want to use SUMIFS function, sum range is B1:B4, if the corresponding value in column A exists in column C, sum the values in column ...

**22**

votes

**10**answers

51k views

### Formula for updated currency exchange rates in Excel or other spreadsheets?

What kind of spreadsheets support a formula like the following:
=exchangeCurrency("USD"; "EUR"; 3000)
The result should be 3000 USD exchanged into Euros. Possibly a date could be provided for a ...

**0**

votes

**1**answer

20 views

### Ignoring value IF “[Value_if_false]”

I am trying to figure out the average difference in monthly sales versus previous years, and I have an IF function within an Average function. I want to skip zero values in the average function if the ...

**0**

votes

**2**answers

3k views

### Insert cells in Excel without updating adjoining formulas

Sometimes I visually compare two datasets by pasting them side-by-side in Excel with a formula in the middle like =A1=C1. This will return true or false based on whether the cells are an exact match. ...

**2**

votes

**2**answers

1k views

### “Fill Weekdays” in excel 2010 fills sunday to friday

I have an excel sheet, in which I filled the date 01/03/2013.
I used the autofill feature to fill in the weekdays only, but excel filled Sunday to Friday, instead of Sunday to Thursday.
Any way to ...

**2**

votes

**1**answer

34 views

### Can I use table relationships in formulas?

Are table relationships only for pivot tables & charts, or can they be used in formulas too? The introduction of tables made it easy to write really readable sumifs like this:
...

**0**

votes

**0**answers

5 views

### Menu option Insert -> Script… is missing in my Spreadsheet, how do I fix it? [migrated]

I've 2 documents, in 1st one I was able to add 'Script...' (as suggested here) from the Insert menu, but on the 2nd one and all new which I'm creating I don't have this option present.
Why this ...

**0**

votes

**1**answer

693 views

### How to bulk replace links that are embed to words in Excel cells

I have a huge list of words that have links. All the URLs have text that repeats. I want to mass replace this text with another. Right now, "Replace all" only searches between words, not through the ...

**0**

votes

**1**answer

555 views

### String match using values in one worksheet with long list (52K) of values from a column in another worksheet

In worksheet 1: I have a fairly short list (~75) of values (text strings) and would like to find all matches of these strings with values from a column in another worksheet (worksheet 2)... these ...

**0**

votes

**1**answer

13 views

### Excel 2013. Address from Value

I have two spreadsheets, one with a Calendar, and the other one with Data Chart.
I'm trying to make it so that the Data Chart will only start using Data from Today's date in the Calendar. (Note: ...

**2**

votes

**2**answers

26 views

### I would like to copy data from 1 row to another in Excel

I am trying to solve the following issue in excel: I am working in two sheets, Sheet 1 and Sheet 2. There is a column of data in sheet 1 (say from A5 to A20). I need every row from that column to be ...

**1**

vote

**4**answers

7k views

### How do I make Excel recognise TRUE or FALSE text as boolean values?

I have been using the INT formula to convert boolean values TRUE and FALSE into 1s and 0s. However, I have copied some data I have been given into my spreadsheet and Excel hasn't recognised the text ...

**2**

votes

**3**answers

5k views

### How do I find out what's linked to my Excel Worksheet?

I've got an Excel 2007 worksheet that I inherited, and each time it opens, I get the dialog box asking if I would like to update values from an outside, linked worksheet. I'm trying to track down if I ...

**0**

votes

**1**answer

28 views

### Excel 2010 Cell Formatting

I have a crew schedule made up in excel. There are 4 different shifts A B C or D. There are 5 people on the schedule, and the schedule is laid out like this in excel:
Each person works the same ...

**2**

votes

**1**answer

31 views

### What does “--” used in Excel function mean?

I came across this reference in a spreadsheet I'm looking at and don't understand what it means.
=SUMPRODUCT(--NOT((ISBLANK(CONSTRUCT))))
Can anyone tell me what the -- in front of NOT refers to?
...

**-1**

votes

**0**answers

20 views

### Excel 2010, IFERROR, INDEX, MATCH

I've got a sheet consisting of various lines like the following.
POH-00006366 CYM001 Cymot NAD 6000
POH-00006366 CYM001 Cymot NAD 7000
POH-00006366 CYM001 Cymot NAD 8000
...

**0**

votes

**1**answer

29 views

### Make the background/text colour follow another cells background or text colour

Ok so I have a large spreadsheet (so I don't want to have to set each cells colour individually or manually) and I have many cells copying other cells. So for instance I have cells N9, N
3, N80, N117 ...

**0**

votes

**1**answer

3k views

### Any trick to calculating percentages when the base is a negative number

I am trying to calculate percent change when the base number (at time 1) can be a negative and the resulting number is positive (at time 2). My problem is the percent change from -4 to +4. A change ...

**2**

votes

**2**answers

26 views

### Problems figuring out weekly scheduling and hours past midnight

This is a screenshot of what I have
What I'm trying to do is have the hours for each individual person posted as shown or a way that the hours can be posted like this and then have the total amount ...

**0**

votes

**0**answers

34 views

### comparing two columns for matches then return values meeting certain conditions

I have the following:
column D ( 300 values representing the average of three cells, A1:A300 & B1:B300 & C1:C300)
column H ( 50 values )
column E & column F & Column G; they are to ...

**0**

votes

**0**answers

21 views

### 2 EXCEL (array?) questions

I am trying to perform some lookup calculations on a pivot table and having some abysmal luck finding the correct function/formulas to use. I've searched the Interwebs and can't find something that ...

**2**

votes

**4**answers

13k views

### Excel 2010: Add text to each cell of a column

How can I add @gmail.com or something similar to a column of usernames without having to type this into each cell? I am using Excel 2010. I have a list of several hundred usernames that need to have ...

**-1**

votes

**0**answers

15 views

### Selecting values from different rows in an Excel 2010 table and merge them in order in a sinlge row [on hold]

I want to use a formula, or set of formulas, for selecting values from a data table were the number of values differ between rows (see attachment). I do not want to cut and paste or use any commands ...

**-1**

votes

**1**answer

17 views

### In Excel 2013 Is there a way to code an auto-complete function ibased on values in another tab

I want to create an auto complete cell in Excel for the resource name. The list is too long for a custom list and I really do not want a drop down list as in data validation. Does someone have an ...

**0**

votes

**3**answers

69 views

### Excel filter duplicate values in column A and filter out rows which have column C blank

How to filter duplicate values in column A and filter out rows which have column C blank.
I have 2200 rows in Col A (the first 1400 rows also have data in Col B & C, the next 800 rows are ...

**1**

vote

**2**answers

14 views

### OOO: Increment sheets for fill down

I have a row of cells on "sheet1" referencing a set of cells on a different sheet. I want to fill down identical sets of cells while incrementing only the sheet.
Another way to describe the problem: ...

**0**

votes

**2**answers

5k views

### Comparing two columns in different Excel spreadsheets

I have two Excel spreadsheets. In the first spreadsheet I have two columns. Column A has 240 Accounts entries and Column B has Values for each account entry. Once again Spreadsheet 2 has its data in ...

**0**

votes

**1**answer

16 views

### Looking for a formula(s) that will return the next date (higher than the starting date) from a row of dates, then the next date from that one

Worksheet 1 lists all of the "legs" flown by our aircraft. The leg or legs for each day would then go to Worksheet 2 for Day 1, Worksheet 3 for Day 2, and so on. Legs are listed in columns starting ...

**1**

vote

**3**answers

6k views

### Find a non-case-sensitive text string within a range of cells

I've got a bit of a problem to solve in Excel, and I'm not quite sure how to go about doing it. I've done a few searches online, and haven't really found any formulas that seem to be useful. Here's ...

**-2**

votes

**0**answers

14 views

### Search for multiple variables in multiples rows at the same time in Google Sheets/Drive [migrated]

I'm using Google Sheets (only option, sorry, know this should be a database).
Sheet 1: List of 200 employee names, and up to 10 location codes they are frequently assigned to.
Sheet 2: List of 50 ...

**0**

votes

**4**answers

28 views

### Excel - How to merge rows from 2 tables based on a common column?

I am trying to merge 2 worksheets where only one col matches in content.
Column"A" in both worksheets have matching values but all other fields are different.
Worksheet1
Col"A" colb colc cold
...

**-2**

votes

**1**answer

26 views

### Display formula result only if inputs are defined

I have a simple spreadsheet, with one row of equations to get sums or differences. What I'm trying to do is enter an equation so that if only one part of the end equation is present, the field is left ...

**0**

votes

**1**answer

15 views

### How to update range of columns in excel

I have an excel sheet where from one sheets's Id used as reference in next sheet.
The Id's are sequential. Like this :
Sheet 1 Sheet 2
ID Name ID Sheet_1_ID Text
1 ...

**1**

vote

**2**answers

23 views

### Average with multiple criteria in same and different column

I have to find average number of days where Status = new or open, Severity = 4, and Area = Apps.
Demo table
Area Severity Status Days
Apps 4 new 20 ←
Apps 3 ...

**7**

votes

**4**answers

8k views

### What is a formula to remove the whitespace from an Excel cell when trim doesn't work?

I have a table of Excel data that I copied from an html table in Internet Explorer. A column of table values all seem to have a space at the end of them, but Trim won't remove it. When I paste-special ...

**3**

votes

**4**answers

4k views

### How to stop numbers converting to date format on copy paste in excel?

When i copy data from a site and paste in excel numbers are converting to date format
EXAMPLE: 2-1 converting to 1-feb how to stop this. i have so many records so cant do manually. is there any quick ...

**-1**

votes

**0**answers

20 views

### MS Excel 2007 Copy Range Across Worksheets and Increment or Offset by 50 rows

I'm looking to copy and paste a list of names from Sheet 1 to Sheet 2 but space them out. For example, I'd like Sheet1!A6 -> Sheet2!A51, Sheet1!A7 -> Sheet2!A101.... Sheet1!A41 -> ...

**0**

votes

**0**answers

12 views

### How can I calculate the grouped averages and maximums per day for a range of dates and times?

I have a CSV dump describing the utilisation percentage of some servers in my organisation, covering data samples from several times per day. Each server belongs to a group. For each group, I have to ...

**0**

votes

**2**answers

2k views

### How to do a “great circle” calculation in MS Excel or LibreOffice?

I got two columns with latitude and longitude values. I need to calculate the distance between the points in every row. I'm trying to use the haversine formula as seen here:
That's the so called ...

**2**

votes

**2**answers

1k views

### Excel: ROUND() to the next unit of 5?

I'm creating a lot of graphs, so I'm making a common scale to be able to compare them using MAX/MIN values that I'm hiding in the graph as a separate series. My Vertical AXIS (Y) is in units of 5.
...

**1**

vote

**0**answers

28 views

### Excel VBA - User Defined Function to turn string into formula

I'm looking for something similar to the solution posted here:
Excel function that evaluates a string as if it were a formula?
Problem is this does not seem to work with a function in there.
This ...

**1**

vote

**2**answers

326 views

### Excel IF statement

I am trying to look at column E and if it is >0 put a 1 UNLESS column D has a value. If column D has a value, I would like to copy the contents of column D. I was thinking the following formula would ...

**0**

votes

**1**answer

31 views

### Excel - How can I collapse data, eliminating blank rows?

I have a 3 column, 20 row table. (A1:C20)
The 3 columns are each 1 digit of a 3 digit number.
Some of the rows have 3 digits, some are blank.
I need a way to return all the non-blank rows of 3 ...