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

learn more… | top users | synonyms (3)

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