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

**4**

votes

**1**answer

324 views

### In which order does Excel process its formulae?

I've got a fairly large spreadsheet with major calculations going on, and it's starting to slow down every time a value that's part of a calculated field is modified. I'm in the process of optimizing ...

**0**

votes

**1**answer

112 views

### Do I need a helper column, or can I do this with a formula?

I am using this formula
=IF((LEFT($B26,2)="<p"),0,IF($B26="",0,IF($F26<>"",0,(FIND("""../",$B26)))))
To parse data similar to the following.
...

**0**

votes

**1**answer

11k views

### Apply VLOOKUP formula to multiple sheets

How can I apply a VLOOKUP formula to multiple sheets in Excel 2010?
Our formula is written and it is working on our Sheet 1, but we need to search 3 Sheets (within the same Workbook), preferably ...

**-2**

votes

**2**answers

70 views

### Putting the few rows into one Excel 2007 column instead

Is there any way of putting the few rows I have in an Excel 2007 spreadsheet into one column instead?
a b c
d e f
g h i
to
a
b
c
d
e
f
g
h
i
This is just a sample try out. I am ...

**1**

vote

**2**answers

257 views

### How to count occurences of “ ” in a cell

I was given a spreadsheet containing a bunch of HTML lines sampled below.
<nobr> <a href="../Descriptive_Addresses.htm"><b>address: ...

**0**

votes

**1**answer

2k views

### Excel - Displaying a value in a cell dependent on another cell having text

I have an excel file with 2 sheets. The first sheet is a user input sheet with cells that are properly formatted for us. The second sheet pulls the raw data into table format so that we can inport ...

**-1**

votes

**1**answer

1k views

### Extract specific data from one sheet to another

I am trying to automate my company budget in Excel. The scenario is, On Sheet 1 I have all the account codes which refer to different account type, e.g.
500101
500102
500103
500105
700101
700102
...

**1**

vote

**3**answers

471 views

### Using a pre-existing function for a new row

I have an Excel document that contains X columns and N number of rows.
The very last column of a row performs a SUM of the first X-1 columns. The problem I have is, the user of this Excel document ...

**1**

vote

**1**answer

132 views

### In Excel 2007 does the data from a formula referenced in another cell get used, or the formula itself?

Simple question and more than likely a simple answer.
I have a workbook with a few worksheets, one of which is a 'data' worksheet. This information is utilized on another worksheet 'main' to fill in ...

**1**

vote

**2**answers

599 views

### Excel formula depending on whether a cell is text or not

=IF(F46=0,I45-C46,I45-F46)
This works. But now I want to include the following as well: Let's say F46 is text – I want the cell value to be I45 (the previous cell value) but if it's not text I want ...

**0**

votes

**1**answer

601 views

### How do I fill in data using index, match, etc. in MS Excel?

I have data formatted like so.
1, 2, 3, 4, null, null, null
A, B, C, D, null, null, null
1, null, null, 4, 5, 6, 7
A, null, null, D, 8, 9, 10
I want to copy the data from row three into the ...

**0**

votes

**3**answers

1k views

### How can I compare Excel serial dates WITHOUT converting to mm/dd/yy type dates?

I have a table that contains a number of values representing Excel serial dates. After a number of unsuccessful attempts to compare fields, my current approach is to do comparisons between serial ...

**2**

votes

**3**answers

3k views

### How do I `SUM` by multiple columns in Excel

I have a comma delimited file that includes two columns date/time (which imports as Excel's mm/dd/yyyy hh:mm custom format) and status of 1 or 0. The status represents a piece of equipment either ...

**2**

votes

**3**answers

295 views

### How to sum up numbers conditionally in Excel? [duplicate]

Possible Duplicate:
Sum Values in One Range Based on Criteria in Another
I have the following Excel document, which tracks hours needed to implement various tasks by the development ...

**-1**

votes

**1**answer

3k views

### Calculate age in days/months/years in OpenOffice

In need to find the age in days - months - years in OpenOffice.
There is DATEDIF() in Microsoft Excel. You can use it to find the difference in days/months/years between two dates.
Age Calculation
...

**3**

votes

**2**answers

5k views

### Excel - define a formula in one cell to be used in other cells with different inputs like a function

I use excel 2010.
I want to use a formula defined in one cell as if it was a user-defined function.
Example:
In A1 I put something like (I know it's not a real syntax):
= 3 * Input + 15
And then ...

**0**

votes

**1**answer

255 views

### How to omit header values in Word formula?

I am new to superuser. Kindly pardon if I make some mistakes.
I am working with tables having numerical data in Word (no not even embedded Excel tables - don't ask why).
It so happens that many ...

**1**

vote

**2**answers

3k views

### excel formula to calculate average of consecutive 24 hour blocks?

Microsoft Excel 2010, input file of thousands of rows of hourly data, thus there are 24 values (rows) per day.
Let's assume that my data starts at B1, and let's assume that I put my average value for ...

**2**

votes

**4**answers

4k views

### How do I calculate number of hours > 24 in Excel?

I've got an excerpt of my spreadsheet below. It's a time tracking application containing a start date/day, a session ID and the duration of the session. Column A is populated by adding the duration in ...

**0**

votes

**3**answers

66 views

### microsoft excel apply formula wrongly

Basically I am facing some problem due to excel auto generate formula that is wrong in such situation. The formula at B6 is =A6*B1. Then the rest of the rows are using cursor bottom right drag down. ...

**2**

votes

**1**answer

259 views

### Excel: Exporting Formula to other languages?

I am currently using excel to simulate some hardware control logic for a processor (for a class). I am only using four formulae: If(), Or(), And() and string operations MID(). I am trying to convert ...

**1**

vote

**1**answer

251 views

### Excel column comparing

I am working on comparing markup values between three different databases. I have each database in a seperate colomn. What is the formula to see if all 6 markup values are the same. To get a Yes if ...

**0**

votes

**1**answer

214 views

### Delete rows in Excel based on returned value in row cell

I have a worksheet in Excel with values in some rows in column B that have been returned as zero (0) but still have a formula in the cell. I want to be able to delete just these rows using a macro.

**1**

vote

**2**answers

1k views

### When drag-copying formula cells to the right, table-references are changed

I have a simple Excel table containing a formula that is referencing columns in another table, and is using the automatic table-range-names (not sure what they're correctly called)
=A1 + ...

**0**

votes

**2**answers

151 views

### How to find specific phrases in unspecified locations and add to new column on MS Excel

Sorry if the questions is not very explanatory. Basically I have a large sheet of data on prospect clients. The data includes their email addresses, however they're sparse and not in set locations ...

**4**

votes

**3**answers

16k views

### Only show a formula result if it meets given criteria

I like to keep my Excel spreadsheets fairly clean. So, often times I'll end up writing statements like this:
IF([formula x]=[value],"",[formula x])
What that essentially says is that if the ...

**0**

votes

**3**answers

9k views

### Formula to return value from one column when condition is met for the first time in another

Example:
Given a spreadsheet with four columns - A, B, C, and D, and row 1 is a header row...
A2=1
B2=(Random positive integer)
C2=(Another random positive integer)
A3=A2+1
B3=B2-C2
A4=A3+1
...

**0**

votes

**3**answers

583 views

### Make numbered list with update feature?

Is it possible to do something like this in Excel? Like a list of active rows with an ordinal number in them.
There is a column B. It needs to show an ordinal number which represents the active row ...

**1**

vote

**1**answer

132 views

### How to reference cells from a previous worksheet?

I want to sum all the numbers from B75 to DY75 from worksheet Sheet1 and add that to the sum of all the numbers from B74 to DY74 in worksheet Sheet2 to get my total average. In other words:
total ...

**1**

vote

**1**answer

443 views

### Copying formulas in Excel

I have an Excel spreadsheet containing many sheets, most of which have formulas.
I have managed to copy most of the sheets (with formulas) into a new Excel file. To copy the sheets I used Copy and ...

**0**

votes

**3**answers

1k views

### Excel formula to bring back one of two values based on another cell being populated or Null

I am trying to create a formula that will reference a cell. If that cell is blank, I want it to bring back a value of "LTL Direct". If it is not blank (anything at all showing), I want it to bring ...

**0**

votes

**1**answer

331 views

### Excel formula to change a number from + to – when word “Db” is next cell

I have a many long statements converted from text, the credits and debits all are in same column as plain positive amounts, to distinguish the credits to debits the cell next to the amounts shows the ...

**0**

votes

**2**answers

591 views

### Spreadsheets: How do I perform functions on a set/array described by a row of (count, value) pairs?

I have excel 2000, google docs, and the latest open office calc available to me. I'm after a solution in any of those, ideally.
I have a multiset of data described by rows of (count, value) pairs. ...

**1**

vote

**3**answers

1k views

### How to easily change columns into rows in Excel?

I have a huge Excel table that I need to transform into paragraphs for a Word report, and I can't find an efficient way to do it
The source looks like this:
And I would ultimately need something ...

**1**

vote

**1**answer

245 views

### Alternative, more efficient scraping method for a noncoder, than Google doc's importxml and xpath?

I've searched throughout the net for a simple solution, but it seems everyone has their own unique method (coding language) of achieving this. I'm only just beginning to learn Linux, and my coding ...

**0**

votes

**1**answer

304 views

### Excel 2007: Need a count when comparing differences in two columns

I have one column that contains budgets and another that contains actuals. How can I get a count in a cell above the actuals column that will tell me how many of them are under their budgets? ...

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

**0**

votes

**1**answer

2k views

### Excel INDIRECT function and conditional formatting - highlighting a row

I'm having an issue with conditional formatting using the INDIRECT function. I'm doing something similar to Using INDIRECT and AND/IF for conditional formatting , but the only answer there isn't ...

**1**

vote

**2**answers

202 views

### Set a formula on many cells with minimal manual edits

I need to set the following formula on many cells:
=VLOOKUP(MAX(Historical!$A$5:$FZ$99999),Historical!$A$5:$FZ$99999,7,1)
This formula looks up the most recent date in the History table, and ...

**0**

votes

**1**answer

654 views

### Searchable tags in Excel?

I'm building a database of spiders, organizing them by name, country, and region within that country.
The problem is that many of the spiders exist in multiple countries and regions within that ...

**0**

votes

**1**answer

147 views

### Google Docs / Exel - need to know how to add formula

I'm using Google Docs / Exel and can't work out how or where to add a 'formula'.
I have a column "i" which is the number of items. I have another column "j" which is the cost of those items. Column ...

**0**

votes

**2**answers

2k views

### Lookup Multiple Results for Multiple Criteria

I've got a list of parent SKUs for items I need to create in my inventory system. This list has been finely paired down to the 165 products we would like to carry. However, each one of these 165 SKUs ...

**0**

votes

**1**answer

58 views

### Auto updating similar formulas

I'm trying to see if there's a way to get my Excel sheet to auto update with new formulas as I enter it.
For example if D4 down to D27 was the same formula(=SUM(B4/25)) and I wanted to change it too ...

**2**

votes

**1**answer

149 views

### Why is Excel showing 'c' as the arg separator for fomulas?

In Excel 2007, I have a cell with
"=if(true,1,0)"
as the contents. If I hit the ENTER key
I get an error dialog which says, "The formula you typed contains an error."
The flyover help for ...

**0**

votes

**1**answer

56 views

### Excel 2007 How To Reference A Seperate Cell In WS1 From A Common Cell In WS2

I have a simple file with two worksheets in. In the first worksheet (a product list) are a number of columns, including Product Code and Product Description. In the second worksheet (dispatches in ...

**2**

votes

**2**answers

1k views

### Turn Excel spreadsheet into a formula

I have an Excel spreadsheet that has a complex computation that is not trivial to turn into a macro or a single-cell formula. The spreadsheet has about 10 different inputs (values a human enters in ...

**2**

votes

**1**answer

518 views

### Split time in arbitrary periods, EXCEL

I have a list with date and quantity of items used
IE:
2009.03.18 -1
2009.06.05 -2
2009.06.22 -1
2009.06.29 -2
2009.07.14 -1
2009.07.14 -1
2009.07.14 -2
2009.07.20 -2
2009.07.30 -1
...

**0**

votes

**1**answer

1k views

### How do I create a dynamic formula on Excel?

On Excel, I have a DDE formula on B1 which reads =server|info!someText.data
I want to change the formula so that someText is written on A1 and then reference it on the DDE formula. Something like ...

**0**

votes

**2**answers

788 views

### Excel: How to print unique pages with only one sheet

I am trying to create a template in MS Excel to print similar pages but with an unique ID within each printed pages (e.g. ID: 001, 002 and so forth) but printing from one sheet only.
I am creating ...

**2**

votes

**1**answer

229 views

### Excel: Weighted SUM of IFs [duplicate]

Possible Duplicate:
Sum Values in One Range Based on Criteria in Another
I have a spreadsheet with a list of weights and another list based on a drop-down selection with the choices "Yes" ...