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

**-1**

votes

**1**answer

30 views

### using date ranges and numbers

I have a spreadsheet that uses both dates and numbers. For example I have a job that takes 4 hours to complete and needs to be done on 10/11/13. I have several jobs that end on this date but the time ...

**1**

vote

**2**answers

6k 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

**1**answer

1k views

### Subscripts inside Excel formulae

I have a formula in Excel that evaluates the l_x's (number of lives aged x) in mortality data:
=IF(J3>J2,"l_x increased",IF(J3/J2-1>=devianceThreshold,"Deviance too high",""))
Accross the ...

**3**

votes

**2**answers

649 views

### Excel 2010 formula to delete repeated content based on value

I have a spreadsheet with two columns, a name and a value. The name is repeated several times with different values, for instance:
Name - 1
Name - 2
Name - 3
etc.
I am looking for a formula ...

**0**

votes

**1**answer

86 views

### vlookup datetime FALSE; return value from 1 of two columns based on exact datetime

Based on data below, I am trying to do a vlookup such that if the datetime in ends in 18:59 return the value in column_3, if the datetime ends in 6:59 return the value in column_4...
10/7/2013 18:59 ...

**0**

votes

**0**answers

35 views

### Excel for Mac 2011 move column 2 in sheet 1 to column 1 in sheet 2

I would like to move column 2 into column 1 for each successive sheet, one sheet per week, automatically. Is that possible? I do it with this formula ='1st week'!D2 and manually changing '1st week' to ...

**0**

votes

**1**answer

2k views

### In excel how to do a 'sumif' function that is dynamic to a changing column

I'm trying to use a formula to do a sumif of a column of monthly data that changes depending on the current month. For example say I am entering this formula in column A of tab 2. Since it is ...

**1**

vote

**1**answer

50 views

### Excel 2010 - Merging multiple rows by the value from the first row

I must find a way to merge multiple rows by the vallue from the first row.
For example the image in the following
Any idea how to do this?

**1**

vote

**1**answer

151 views

### Excel 2010 formula to track records and replace them when surpassed or bettered

I've found using game data is an excellent way to learn the ins and outs of Excel. I made a workbook complete with personally made achievements (all of which work), but I can't find a formula that ...

**2**

votes

**1**answer

151 views

### Remove pseudo-blanks in Excel?

Is there a way to make supposedly blank cells truly blank?
I have had a bunch of formulae evaluate to "", whereafter I proceeded to copy the results and paste values. Now when I check for blanks ...

**1**

vote

**1**answer

906 views

### How a count of values which have more than 1 occurance in a column

In cell B5 I have a formula:
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10&"")-(COUNTIF(A2:A10,A2:A10&"")=1))
If column A has the data as shown below, the formula returns the ...

**0**

votes

**1**answer

981 views

### Use 'if' to check a condition. If condition is not met, then do not change the value in the cell. Keep the previous value as it is

Cell B1 has value 1
Cell B2 has value 1
Cell B10 has no value.
I write 'if' formula: B10 = if(b2=b1, b2,)
The value in B10 changes to 1.
Now, I change B2 value to 2.
The cell B10 changes to 0. ...

**2**

votes

**1**answer

2k views

### Excel array formulas to find smallest date with multiple criteria

The array formula I have will give me the smallest date from a Service table, based on the following criteria:
1) The value in the item column matches the item # in cell A1
2) The value in the ...

**0**

votes

**1**answer

336 views

### How to create a formula that can count values across multiple columns in specified rows

I have an Excel sheet where I have multiple tasks and multiple products. Each task for each product is rated red. amber or green depending on progress so far. Each task also has an owner.
I want to ...

**0**

votes

**3**answers

86 views

### Excel formula with multiple conditions

I want to tell certain combinations of genes that make bacteria species.
So:
sample number, gene A, gene B, gene C.
The formula should be:
If all 3 are negative then it is a rest group
If A and ...

**0**

votes

**1**answer

384 views

### Excel - value of the last changed cell in row

I hope someone can help, I have a number of cells in a row which is filled automatically by another application, I need to read the last changed value in that row into one cell. I know I can create ...

**3**

votes

**1**answer

610 views

### Calculating difference between 2 times, excluding weekends, then averaging the results

I have an Excel 2007 worksheet where I need to calculate the difference between 2 times, excluding weekends, then at the end of a week or month, be able to find the average of the amounts of time in ...

**2**

votes

**4**answers

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

**0**

votes

**1**answer

195 views

### Move rows to new sheets in excel

I'm having a problem. At my work we often get spreadsheets with locations. We need to spread out each of the locations to a different worksheet. Because then we list nearby businesses to advertise to. ...

**1**

vote

**0**answers

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

**0**

votes

**0**answers

44 views

### MIcrosoft excel 2003 auto populate cells

What I would like is input text into a cell say A1 and I want that text to then be duplicated in cell D1. But I can not type the formula into D1 itself i.e. no =a1.
Is this possible? Thanks

**1**

vote

**3**answers

112 views

### Collect Row # of Matching Elements in Excel

I'm trying to collect the row numbers that match a certain value in multiple columns of an excel worksheet. The behavior I'm looking for reflects the table below:
|---|---------|---------|---------|
...

**2**

votes

**0**answers

1k views

### How to copy a column with conditional format by “value” - Libreoffice 4.1

I have a column that contains the data and a set of rules for the conditional format of the cells.
i.e. the conditional format changes the background colour of some cells if they are negative.
I ...

**0**

votes

**2**answers

263 views

### Return a column value based on looking at 3 other columns

I have a pivot table that has 3 columns with values. I need to perform a search of these columns and return a value from another column. The issue is this - I need excel to look at the 3 columns and ...

**0**

votes

**1**answer

150 views

### Best way for bidirectional referenced table rows for double-entry booking

I want to move to a Google Spreadsheets setup to handle my private booking.
Currently I have four tables:
Overview
Accounts
Income
Outcome
Except of "Overview" all share following columns:
Date
...

**2**

votes

**1**answer

252 views

### How do make an Excel cell reference a particular sheet according to the current date?

I have an Excel spreadsheet for tracking sales with the first page being for totals, then a separate page for each month's sales, which has subtotals on each page.
Can I make the "this month's total" ...

**0**

votes

**2**answers

4k views

### Excel - how to either have direct data input or cell value from formula

I would like to either populate a cell value from direct key input or have the cell value derived from a formula.
Example: either directly input a number into cell A4 or have the value for A4 = A2 + ...

**3**

votes

**1**answer

427 views

### How to plot coordinates in Excel?

I have a list of goals scored by Ronaldo and Messi and have created a manual heat map of where they have scored goals from. I want to write coordinates in a cell in a list and for the heat map to ...

**2**

votes

**2**answers

5k views

### Excel-MATCH function doesn't work on pasted values

I'm trying to find the positions of the values(text) in one column in another column. I ran the function: =MATCH(B1, A:A, 0) and I get a #N/A result. But this result is incorrect...as I clearly see ...

**0**

votes

**1**answer

462 views

### Find and Replace VBA/Macro in Excel 2010

Another Excel VBA question. I'm using Excel 2010
I want to create a Macro that will remove the following 3 symbols in any cell for a selected range:
[ ] "
For example; If I select a range of ...

**0**

votes

**2**answers

142 views

### Is there a way to compare two tabs of data and place Difference at end of one of the datasets

I need to find a way to compare two spreadsheets and if there is a Differences, pull data from one sheet to the other.
Sheet1 has a list of all Dealers in my Zone.
Other Tabs in my workbook have ...

**2**

votes

**4**answers

7k views

### Generate a comma-separated list of cell contents, excluding blanks

To concatenate a certain four cells, I'd use:
=CONCATENATE(A2,",",C2",",D2,",",F2)
This would make it so that...
A2 = "Matthew"
C2 = "Mark"
D2 = "Luke"
F2 = "John"
would result in ...

**2**

votes

**2**answers

4k views

### Copy cell reference with multiple row increment

I've exported delivery addresses from Pastel into an Excel Spreadsheet. Each address record that Pastel spat out has a lot of data for each customer that I don't need.
Given the screenshot here, I ...

**0**

votes

**3**answers

296 views

### Can I use an IF statement as a parameter in a COUNTIF function?

If someone can help improve the title, by all means. I've been struggling with it for a while...
I'm using Excel to analyze a list of filenames for potential issues...special characters, excessive ...

**0**

votes

**1**answer

3k views

### Excel Spreadsheets: If A:A does not equal C:C, how do i list columns A, B and C somewhere else?

I have an issue with referencing an excel spreadsheet:
I have data in columns A and C that's (mostly) equal, I want to isolate and create a separate list for the rows that are not equal.
For ...

**2**

votes

**1**answer

197 views

### Get specific value from table with table name, row and column index

I am trying to create some sort of manual filter. If you look at the image below, you can see two tables named ONE and TWO. The table to the right contains 3 dropdown lists.
The idea is that you ...

**-2**

votes

**1**answer

3k views

### Match up 2 columns in excel from one worksheet to another worksheet

I'm having trouble comparing two worksheets. I have worksheet A and worksheet B. In each worksheet, column A have the customer number and column B have the date. The same client and same date can ...

**4**

votes

**1**answer

2k views

### Linking Excel 2013 cell to Word 2013 table cell adds unwanted line breaks

I have a problem where I am trying to copy an excel cell that contains content using a formula, into a word document table cell and link them such that the formatting is used in word.
What happens is ...

**3**

votes

**3**answers

2k views

### Multiplying values in Excel 2010 (Same cell)

Another excel question. I'm using Excel 2010.
If for example I have a value in cell A1, lets say 2.59
What I want to do is multiply that cell by 1.333 but in the same cell
I don't want to have to ...

**0**

votes

**2**answers

2k views

### can Excel take data from a Text Cell and place it into a Formula Cell?

I am tracking several stocks, and saving their chart data in separate spreadsheets titled by their ticker symbol (i.e. aapl.xlsx, goog.xlsx, etc...). I want to have a single spreadsheet that I can ...

**-2**

votes

**2**answers

52 views

### Excel - make a sum with some criterias [duplicate]

-----A ------B------------C
1||Name ||31-03-13 ||1-04-13
2||test1 ||>>>> 150 ||>>>> 10
3||test2 ||>>>> 750 ||>>>> 11
4||test3 ||>>>> 800 ||>>>> 12
5||test3 ||>>>> 900 ...

**2**

votes

**1**answer

2k views

### Is there an Excel formula that can conditionally sum rows based on column headers?

I need to be able to put a formula into the last cell of a row (shown by = in the image) that totals the values in that row that don't have a cell with the text 'Done' at the top of the relevant ...

**1**

vote

**1**answer

360 views

### Change Excel Worksheet background colour based on values

Hopefully some one can help, but not sure if its possible. I'm using Excel 2010.
I want to change the background colour of a whole worksheet to green if the value in cell c20 is equal to or greater ...

**1**

vote

**1**answer

280 views

### Highlight field which has a passed due date in google drive spreadsheet

I calculated a due date based on Column A with EOMONTH (Column A, 3). The resulted dates are in Column B.
What I cannot achieve is to highlight the fields which are older than the current day.
In ...

**3**

votes

**1**answer

678 views

### Find last value in a cell range that meets a condition

I'm looking for a formula in LibreOffice Calc to find the last value in a range that meets a condition. Here is sample data:
A B C D E F
...

**0**

votes

**0**answers

55 views

### Formula to find after how many cells a value repeats itself

I've been looking around like crazy for this:
I.m looking for a formula to count after how many cells does the value 'O' occur. For example, in the below:
xyz 7 O 7 8 ...

**0**

votes

**1**answer

1k views

### Counting and comparing rows for same values in excel 2007

I want to compare two columns of different sizes and count the number of rows in the two columns that are a match and report this in a separate cell. I have one column that is a list with the day of ...

**3**

votes

**1**answer

196 views

### Excel macro copy same entries once in another sheet paper

I'm really new to VBA Excel.
Let's say I have paper 1 with in column A many alert types, and column B the place where it happened (I'm working for a security company). What I need to do is read ...

**1**

vote

**3**answers

90 views

### How can I avoid typing in a value which already exists in another range in Excel 2010?

I am working up a basic project tracking spreadsheet in Excel and would like to have two sheets - one which shows my active projects and one which shows my completed/cancelled/archived projects. I ...

**1**

vote

**1**answer

151 views

### How do I lookup matching values in two Excel tables?

I have two tables, each with one common index column. One table has multiple entries of the index value with other information in adjacent columns; the other table has one entry of the index value ...