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

**0**

votes

**2**answers

165 views

### How to generate weekly dates from bi-weekly pay period in Excel

I'm trying to convert bi-weekly pay period to weekly dates. Lets say I have 3/2/2012, 3/16/2012, 3/30/2012 and some gaps and then again biweekly dates.
generate
3/9/2012, 3/16/2012, 3/23/2012, ...

**-1**

votes

**1**answer

100 views

### How to get data from separate worksheets displayed in a single separate worksheet

I am working on a spreadsheet in Microsoft excel 2010. In the spreadsheet I have 3 worksheets. These are known as:
1st
2nd
Cases
Now in the 1st and 2nd Worksheets, there is a table at the bottom ...

**1**

vote

**2**answers

119 views

### How to find specific text syntax format in a cell with an Excel function?

I am looking for a cell formula to find/identify/validate if an adjacent cell contains characters in the correct format.
If there is a character "|", then it must be preceded or followed by any ...

**0**

votes

**1**answer

110 views

### How to fill sequence in Excel from ranges given in another sheets?

I have an Excel sheet where two columns (say J and K) among data in sheet1 are as follows:
J ------------ K
1 ----------- 25
26 --------- 50
101 ------ 150
Which are some ranges. ...

**0**

votes

**2**answers

123 views

### How to extrapolate or predict a project completion date

I have a few data points consisting of a percentage completed and a date. I would like to be able to predict the expected completion date based on the progress made so far.
If this cannot simply be ...

**0**

votes

**1**answer

713 views

### Comparing columns in two different excel sheets and if they match copying third column

I have limited working knowledge of Excel and I hope someone will be able to help me with my problem.
I have two different work sheets (say F1 and F2) with last name in Column A, first name in Column ...

**0**

votes

**2**answers

62 views

### How do I determine if a value is between range of values specified in two columns and return another value from the row where condition is true?

I have a value that I enter in cell B5. I need a value returned from column C and the corresponding row where the value is between minimum values in A10:A12 and maximum values in B10:B12. In the ...

**0**

votes

**1**answer

64 views

### Excel 2010 - Show cell contents - wrong number appearance

I was trying to create a simple function in Excel VBA editor. When I click on a cell within the certain range, I want to copy its value into another cell I have set.
Private Sub ...

**0**

votes

**2**answers

43 views

### having cell show a paticular number if the answer in the formula in the cell is a certain number

so I don't know if it's possible but I want to have a cell show a particular percentage in the formula I put in the cell comes up with a particular answer. for example, if I put in the formula =6/3 ...

**0**

votes

**1**answer

153 views

### Line chart from multiple tables in Excel 2013

I have two sheets, "Graphs" and "Data".
In "Graphs" is a line chart with a single line that gets X and Y values from tables in "Data". I want the chart to be updated automatically when I add ...

**7**

votes

**2**answers

172 views

### Formula for finding 'near perfect' matches

I have a list of +-8000 items which is the result after applying numerous techniques to remove duplicates (the list started at over 10000 items).
I now have the following issue -
Example
1. Jack ...

**0**

votes

**1**answer

30 views

### how to sum the amount of cells with the same number in a column - Microsoft Excel 2010

My english isn't that good, I hope you understand what I wan't to accomplish
I have a column (A) with different zip codes ( total of 3583 rows). I need a formula/function to go through each cell and ...

**1**

vote

**0**answers

30 views

### How can I move columns based on the column header title to another workbook?

I have to average grades based on each objective for a new report card we have to complete this year. Column one has students names, each additional column will have the objective associated with the ...

**1**

vote

**2**answers

41 views

### Excel Conditional Summing

I want to create a formula so that I can keep track of how many feet get out of a drill bit. The data will be arranged in this way:
I want to show results of the total footage drilled by:
Any ...

**2**

votes

**1**answer

90 views

### Excel VLOOKUP using results from a formula as the lookup value

I have a cell that I must remove the first 2 characters "RO" for each value in a column on a sheet called RAW DATA and put into a cell on a sheet called ROSS DATA. Some of the values in that cell have ...

**0**

votes

**1**answer

58 views

### Excel: Look up function for combinations of cells in a single column

I'm looking to find the number of times a certain combination of values appears in a single column, I was hoping to do this in Excel but I'm starting to think it may not be possible.
As an example, I ...

**1**

vote

**1**answer

108 views

### Excel access.Vlookup? IF?

What im looking to do, is move stuff from one excel sheet, to another sheet in the same workbook.
We will call the sheet with the data the "suppliers list" and the sheet where i want it to go, the ...

**0**

votes

**2**answers

468 views

### Conditional formatting of a cell based on value in 4 adjacent cells

I'm trying to get a cell C3 to highlight using conditional formatting, but based on a value in 4 other cells.
If 4 continuously adjacent cells (e.g. AS3, AT3, AU3, AV3) contain the X value only then ...

**3**

votes

**1**answer

75 views

### Flatten Excel Formula (2007)

Say cell A1 has the formula A2 + A3
and B1 has the formula B2 + B2
and C1 has the formula A1 + B1
I need an easy and quick way to create the formula for C1 in the flattened state..
i.e.
(A2 + A3) ...

**1**

vote

**1**answer

63 views

### If A1>1, I would like the next 3 cells to be red, i.e.a2,a3,a4 red. But if A2>1 I also want the next three cells red

What I would like is: if ANY cell has a number greater than 1 - then the next 3 cells TO THE RIGHT trun red
Is there a way of doing this, without me having to type the formula in to every single ...

**1**

vote

**3**answers

11k views

### How to Merge Data From Multiple Excel Files into a Single Excel File or Access Database?

I have a few dozen excel files which are all of the same format (i.e. 4 worksheets per Excel file). I need to combine all the files into 1 master file which must have just 2 of the 4 worksheets. The ...

**-1**

votes

**1**answer

45 views

### IF Statement Budget File setup for Monthly Amounts

I am working on a budget formula and am stuck on the termination date, I can't get the formula to work if a tenant will remain all year (no termination date). It works perfectly if a tenant has a ...

**1**

vote

**2**answers

559 views

### In Excel how can I sum all the numbers above the current cell?

I want to have a column in Excel that consists of a header, a bunch of numbers, and then have the sum of those numbers at the bottom. I'd like the sum to adapt to the insertion of new numbers above ...

**0**

votes

**1**answer

49 views

### Preparing an Excel 2010 workbook for Excel 2013 compatibility

I'm going through an important workbook currently in Excel 2010 to make sure it's fully compatible with Excel 2013. I found a list of changes to the object model in 2013, but it didn't cover your ...

**1**

vote

**2**answers

199 views

### Cumulative average using data from multiple rows in an excel table

I am trying to calculate a cumulative average column on a table I'm making in excel. I use the totals row for the ending cumulative average, but I would like to add a column that gives a cumulative ...

**0**

votes

**0**answers

51 views

### Comparing two lists of strings based on logical operatiors

I'm trying to find and merge several groups of people based on the following boolean algebra:
Let
'*'=AND and '+'=OR
group1=A*NOT(B)+C*NOT(D)
group2=NOT(A)*B*NOT(C)*D
...

**-4**

votes

**1**answer

183 views

### how do you ignore empty cells?

I'm using this function to rank data:
=SUMPRODUCT(($B$2:$B$11>B2) / COUNTIF($B$2:$B$11,$B$2:$B$11&"")) + 1
This version assigns 1 to the highest value
and sequentially increasing ranks to ...

**0**

votes

**0**answers

48 views

### Hold cell spacing when copy/paste

I need to copy only certain cells, example.
A1, D1, G1, J1, M1
To:
A6, D6, G6, J6, M6 or H6, K6, N6, etc
(Notice the equal spacing between the cells to copy and where to paste.)
But when i paste ...

**-1**

votes

**2**answers

56 views

### If A:A is more than -500 and less than -400, then sum values in corresponding rows in B

I have a list of locations with corresponding values and need to sum the values for a range of locations.
Example
middlePeak tags
-499 107
-495.5 114
-493.5 ...

**3**

votes

**3**answers

4k views

### Is there an Excel formula to identify special characters in a cell?

We have about 3500 documents whose filenames need to be manually scrubbed to remove special characters like brackets, colons, semicolons, commas, etc.
I have a text file that I've dumped into excel, ...

**0**

votes

**0**answers

19 views

### Cell reference is not updating in manual calculation mode

I have defined a variable in Date-format (Date_selected), shared in the entire workbook. On a different worksheet a cell has a direct reference to this (=Date_selected). Also formatted as a Date. When ...

**0**

votes

**2**answers

141 views

### Multiple conditions in if statement on non-contiguous cells in Excel

I would like to perform an if statement with conditions on the values of multiple non-contiguous cells, something like
...

**0**

votes

**0**answers

25 views

### How to use the Integer i.e. currentregion's row from the macro in a cell's formula

How do I use the row from the current cell, in another Cell's Formula on another sheet?
I created this Macro, but don't know how to use the Integer from the macro in a cell's formula.
In a macro I ...

**1**

vote

**2**answers

235 views

### Excel formula/VBA to detect a property of a group of rows

I have a large dataset in an Excel sheet where:
column A: quote number
column B: opportunity number
column C: sales order number.
One quote number will have multiple opportunity numbers. A sales ...

**1**

vote

**2**answers

101 views

### Average using Elapsed Time processing a number of docs

I process documents and want to get a formula showing the average time to process each document.
Here's my data:
number of docs processed (column G)
start date/time (H)
end date/time (I)
elapsed ...

**1**

vote

**1**answer

80 views

### Google Spreadsheet. Retrieve only one cell from ImportData-result [closed]

I have this function:
=ImportData("http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=7&c=2013&d=0&e=7&f=2013&g=d&ignore=.csv")
...which returns how a given stock was ...

**0**

votes

**1**answer

72 views

### Excel summing named range

I have a workbook with many sheets in it. I want to sum column C2:C50 on each sheet. However, in the future I anticipate having to sum the range C2:C?, and rather than have to go into each sheet to ...

**0**

votes

**0**answers

66 views

### IF word in A1 matches word in Col C AND word in B1 matches word in Col E AND

Cell A1 contains a paragraph made of Hebrew words (They are Bible verses in Hebrew, so sometimes more than 254 characters long in each cell).
Cell B1 contains a paragraph of English words (They are ...

**0**

votes

**1**answer

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

**2**

votes

**1**answer

32 views

### Excel 2007 nested function “if, then”

I keep getting a message that I have too many arguments. I am trying to ask: If value 1 = x, then value 2; otherwise value 3. BUT if value 3 = 0, then blank.
It doesn't like:
...

**1**

vote

**1**answer

64 views

### MS Excel 2007 If Then Formula Troubles

This is a simplified case of an issue that I am seeing using MS Excel 2007.
Format your sheet to be currency with 2 decimal places.
Cell A1 type 25
Cell A2 type 20.13
Cell A3 type ...

**1**

vote

**1**answer

42 views

### Identiying repeating digits in Excel cell

I have a list of phone numbers in excel, some of which are obviously duds because they contain repeating digits like this '5555555'. Is there any easy way of identifying these?
Its Excel 2010.

**1**

vote

**1**answer

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

85 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

265 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

57 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

23 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

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

**2**

votes

**1**answer

42 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

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