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

**0**

votes

**2**answers

608 views

### Formula help for matching data from multiple columns in multiple worksheets

What formula do I need in my worksheet DFW column C that will do the following?:
On worksheet DFW take data from cells A2 together with B2 and see if there is an exact match in worksheet Feb columns ...

**0**

votes

**1**answer

247 views

### Get unique comma separated values from list of comma seperated values

I have a list of csv in a single cell, and I need to get the unique values from that list and put them in another cell. For example, in one cell I would have:
DIV-154, FOD-371, UDL-913, DIV-154
And ...

**1**

vote

**2**answers

2k views

### How can I filter specific rows from an Excel spreadsheet based on criteria?

I have a spreadsheet from which I need to select ONLY the first five rows in each category.
I only want columns A and B when C is less than 5 -- =IF($C1>5,$A1,) and =IF($C1>5,$B1,) -- but I ...

**0**

votes

**2**answers

515 views

### Sumproduct fails when range is only 1 row and part of the calculation is blank

I have the following formula that works when the ranges have more than 1 row, but generates a #VALUE error if there is only 1 row and one of the factors is blank.
=SUMPRODUCT(cost, units)
Cost ...

**0**

votes

**1**answer

537 views

### Unable to get the VLookup property of the WorksheetFunction class

I want to implement a barcode finder in excel, so I have item names and barcodes in separate sheets depending on what group an item belongs to. The user clicks on a radio button (selecting Group 1, ...

**0**

votes

**1**answer

157 views

### Complex excel formula help needed?

I have a complex formula i need to create to look up various fields, with percentages of totals to be given if certain criteria are met, and need some guidance.
In column P i have a monetary figure.
...

**0**

votes

**1**answer

2k views

### How to specify dynamic tab in a sumifs formula

I'm trying to get the below SUMIFS formula to update the worksheet it looks at depending a month referenced in some other cell, say A1. I have sales data from each month in a separate worksheet so ...

**0**

votes

**1**answer

953 views

### Formula to copy a specific character from one cell to another

I am using MS query to pull in data. I need to create a column that copies the first character in another column. For example:
Cell A1 - E00633
Cell A2 - B00842
Cell B1 - I want this cell to ...

**0**

votes

**1**answer

354 views

### Can cell addresses (vs. cell contents) be used as operands in Excel formulae?

Consider the following scenario illustrating the data I have vs. the data I want. Very simply, I have a single column that represents Field 1, Contents of Field 1,...etc. and is more appropriately ...

**1**

vote

**1**answer

141 views

### Guidelines for developing an Excel grading sheet

I'm trying to develop a convenient solution for preparation of a summary sheet comprising of lots of grading sheet underneath it.
I have a tabular excel sheet used for obtaining feedback from random ...

**1**

vote

**1**answer

277 views

### Complex formula in Excel

I am trying to apply a complex formula in an entire Excel spreadsheet column.
What makes it complex, apart from the fact that I am not experienced in that level of Excel, is the fact that the formula ...

**0**

votes

**1**answer

186 views

### Easy MS Excel - MIN column with IF condition checking another column

I'm analyzing some personal expenditures for somebody on Excel.
I have a column for calendar dates and a column for expenditures on that date.
Of course, there are days when there are no expenditures ...

**0**

votes

**3**answers

596 views

### In excel, I need to compare the “rank” value of items in two sheets, and print the original rank in the new sheet

I've got two reports, pulled in as separate sheets in one excel file. This is the approach I came up with, but I don't know how to implement it:
For each cell in column "merchant_name" in sheet 1, ...

**1**

vote

**2**answers

195 views

### Subtracting percent of each cell from itself then subtracting a constant from each of those numbers and adding all of them together

I have an excel spreadsheet with several PayPal payements in a column. I want account for the paypal fee; which is 2.9%+.30cents per transaction. How do I do that?

**2**

votes

**2**answers

445 views

### Is this the best strategy for displaying a negative time value in Excel?

I am trying to send up a calculated cell as duedate - now() in order to quantify overdue vs. time remaining (negative values = overdue, positive values = time remaining). Consider:
Col A ...

**3**

votes

**1**answer

580 views

### Is there a way in Excel to combine the contents of multiple cells and still leave line breaks?

Consider:
+---+----------------------+
| | A |
+---+----------------------+
| 1 | Instructions to foo |
+---+----------------------+
| 2 | 1.) Foo ...

**0**

votes

**1**answer

1k views

### Excel formula sorting data by date but group by column value

Morning people
I've been roped into helping someone with an excel sorting issue and I've got myself a bit stuck to be honest. I need to sort a spreadsheet by date (oldest to the newest) but group ...

**0**

votes

**2**answers

401 views

### Excel formula for summing columns based on a variable

I have created the below spreadsheet to track outstanding bills for clients.
I am trying to create a formula that will sum all the clients who have "No" for the value in the Paid column so I can ...

**2**

votes

**4**answers

4k views

### extract date from string in excel

How can I extract date from such strings in excel?
John8/11/12
Jackson09/11/2013
Desired result:
John 8/11/12
Jackson 09/11/2013
Can I do this using only excel formula using excel ...

**0**

votes

**1**answer

5k views

### How do I split single cell contents into multiple columns without seperators?

I have an excel spreadsheet that has the following:
Cell A1 ABCD 1234
I would like to split this to be
CELL A1: ABCD
CELL B1: A
CELL C1: B
CELL D1: C
CELL E1: D
I tried using the ...

**1**

vote

**1**answer

928 views

### How do I set up Simpson's rule in Excel?

I know there are example spreadsheets online, but I look at the formulas and don't quite understand what is going on.
I know the formula for Simpson's Rule but I don't know how to implement it in ...

**3**

votes

**1**answer

1k views

### Sample data to create Excel timeline

I am trying to create an Excel timeline like the one shown here. I am not understanding how the sample data would look and how to go about making this. Please post a screenshot or explain how to ...

**1**

vote

**1**answer

1k views

### Dragging the index formula down when using arrays

On this older page, Dan O'Day provides and excellent excel formula to assist with looking up and returning multiple reference values for a given key in separate columns. The formula is:
...

**2**

votes

**3**answers

329 views

### Robust way of creating formulas in Excel

To simplify the problem, let's say I have an Excel sheet which has different numeric values in the cells A1 to B100 (two columns by 100 rows). In column C, I would like to have a formula that will ...

**0**

votes

**1**answer

1k views

### Compare two excel sheets with almost identical data and come out with matched and mismatched data

There are two excel sheets with almost similar data. One sheet is huge with around 1800 rows. and the second one is a small with around 100 rows. And these 100 rows may be present or not , if present ...

**0**

votes

**1**answer

316 views

### Spreadsheet fill down for a formula

I want to add a formula to multiple cells in a column without typing them all out. now don't just tell me about the fill option as that does not do what I want (as far as I can tell).
When I used ...

**4**

votes

**2**answers

2k views

### Excel - concatenating cells where reference number matches [duplicate]

I have a worksheet with many rows. Some of the rows share reference numbers (contained in, say, column A). What I'd like to do is, where a row shares a reference number with another row, I'd like to ...

**0**

votes

**2**answers

12k views

### Looking-up the values of one worksheet in another worksheet

Basically, I could do this manually, but as the data is huge, it would take a lot of time. I am sure there must be a formulaic way of doing it.
I have two worksheets in Excel.
Worksheet 1.
Column-A ...

**0**

votes

**2**answers

64 views

### How do I select the correct interest rate from a table of historical interest rates in Excel?

I have two worksheets in my workbook.
The first worksheet keeps track of the value of my mortgage/loan. The first column contains a date, the second contains the interest rate that pertains to that ...

**0**

votes

**0**answers

300 views

### Complicated Formula Using Multiple Inputs

Some really good threads here but couldn't find what I am looking for.
My formula is very complex on 1 spreadsheet (sheet). There are 6 variables to input that eventually calculates a single answer. ...

**1**

vote

**1**answer

101 views

### Excel Lookup Not returning Proper Result

I have a simple lookup function, which I am using in place of a Switch Case statement.
Following is what I am trying to accomplish:
switch(F5){
case "apple":
return "1";
break;
...

**0**

votes

**1**answer

156 views

### Excel date formatting after formula calculation

Say I have the following two dates in column 1 of my spreadsheet:
Tuesday, February 12, 2013 10:13 AM
2/12/2013 10:13
When I highlight the column and change the cell formatting to a date type of ...

**1**

vote

**3**answers

82 views

### Complex Summing Values in a Cell

Let's say I have formula result that returns this +10+5+1+40, as text. I want to then sum the those numbers to get the total.
The result could be any number of values, so like +2+5 or +10+5+7+1+8.
...

**1**

vote

**1**answer

289 views

### Excel formula for extracting prefixes

I have a table of 2 columns - file name & PC ID.
What I need is list of all 3-letter prefixes - for each PC ID separate. For example:
For:
BUSstop1 PC1
BUSstop3 PC2
TRMstop8 PC1
BIKnet1 PC1
...

**5**

votes

**1**answer

4k views

### How do I copy the results of a calculation without copying the formula behind it? Open Office Calc

How do I copy and paste only the results of a calculation without copying the formula behind it? I'm learning to use Open Office Calc. I remember other spreadsheets that had a values function, or ...

**4**

votes

**2**answers

1k views

### Is there a way to express a complex Excel formula in terms of final (non-formula, value-only) cells?

I have a very complicated Excel spreadsheet (formulas accessing cells with other formulas across multiple worksheets) that in the end computes a single cell (output) with input from several other ...

**0**

votes

**1**answer

1k views

### LibreOffice/Excel: SUM an adjacent cell if the value in the conditional cell is equal to N (SUMIF(?))

Disclaimer: I have no idea what I'm doing.
In LibreOffice Calc (3.6.2.2), I'm trying to SUM all of the values in G:x (where range is G2:G529), where B:x is equal to a given number N.
The data set ...

**1**

vote

**2**answers

3k views

### Excel formula to get time difference as a decimal number

I have a table like this (the C column is blank):
A B C
1 19:30 23:00 (3.50)
2 14:15 18:30 (4.25)
I need to calculate the time difference in each ...

**2**

votes

**0**answers

406 views

### Excel: Why is VLOOKUP returning inconsistent and unexpected results?

I'm automating an Excel column to assign account values based on the vendor paid. For instance, if AT&T gets paid, the account value is Utilities:Telephone, and when the post office is paid, the ...

**0**

votes

**3**answers

8k views

### Excel how to SUM a column filled with Yes's and No's

I'm not summing numbers, but rather the number of occurrences of a keyword.
How is that done?
Sample Data:
Item Completed
---------------
a No
b No
c Yes
d Yes
e No
Total ...

**2**

votes

**2**answers

359 views

### How can I flag the presence/absence of a specific substring within a string in Excel?

We have a list of keywords associated with a list of documents. The list was created from frequency counts of the words in the document text. We are trying to add a weight to the keywords based on ...

**4**

votes

**3**answers

304 views

### Suggestions for building and managing complex functions in Excel

Far too often, I've got a formula that wraps functions inside of functions inside of functions, and when some new condition requires that I wrap another function around it, I often find myself ...

**0**

votes

**1**answer

28 views

### How does one add all values that occur before a specific date?

I know I need the SUMIF function, but I can't figure out the condition for the life of me. Given the following table, how would I get $150?
Value | Date
------+---------
$100 | 1/1/2013
$50 | ...

**0**

votes

**2**answers

561 views

### If / Else logic involving text i Excel

I want to perform the following logic in a Microsoft Excel 2010 worksheet:
In cell E3: if C3 = Off or off then VALUE=0, else Value=C3*D3
Where cell C3 will contain either a number or the word 'off' ...

**1**

vote

**2**answers

3k views

### How does Excel's VLOOKUP handle multi-word lookup values?

I have a worksheet that's returning unexpected VLOOKUP results, and I'm trying to narrow down the issue. I've run into a question I can't answer in my search for the solution.
Consider:
I have a ...

**2**

votes

**1**answer

3k views

### Why would two Excel cells that appear to have the same values test as not matching?

I'm creating a validation column for a worksheet. I've attached an image of what's going on.
Column A contains an indexing function and works properly. The formula appears at the bottom of the ...

**7**

votes

**4**answers

20k views

### Is there an Excel function to create a hash value?

I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real ...

**0**

votes

**1**answer

167 views

### Extracting delimited substring in Excel

I started with this question, but it doesn't quite address my needs. I have a series of filenames in a worksheet. Some of the filenames have cross-references enclosed in square brackets. Consider Foul ...

**0**

votes

**1**answer

76 views

### how to show the result of a search as a sum in excel

I have a list of sales in an Excel file with vendor name and article price.
This list is on one page, now I want to display the sum of all the sales by one person on a second page.
I have tried to ...

**1**

vote

**2**answers

3k views

### Match up date and time values in different Excel sheets

I have an excel file and need the following.
There are two sheets in one Excel file. The first sheet shows date and time values in column C & D. The second sheet does this too in column A & B. ...