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

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

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

394 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

147 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

319 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

209 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

764 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

263 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

529 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

734 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

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

457 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

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

**-1**

votes

**2**answers

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

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

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

**4**answers

413 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

413 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

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

**1**

vote

**2**answers

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

67 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

392 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

107 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

203 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

87 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

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

**7**

votes

**1**answer

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

**1**

vote

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

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

**3**

votes

**1**answer

527 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

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

466 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

375 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

36 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

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

**8**

votes

**4**answers

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

190 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

83 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

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

**-2**

votes

**1**answer

141 views

### Count in a column the number of cells in which the first character is a letter [closed]

I have a column of data where most cells have first character "," however I want to include in a countifs formula an argumant which includes counting of cells that begin with a letter.

**1**

vote

**3**answers

3k views

### Display multiple cells if data on another workbook contains “X”value

I export a complex report daily and I transfer that information manually into another excel sheet. My question is: In workbook 1, can I get a group of cells in a row to display the text in workbook 2 ...

**2**

votes

**1**answer

268 views

### Excel =+ formula impact

I can't for the life of me remember but what does putting + after = in a formula actually do?
For example the difference between:
=+VLookup(A1,B1:B10,1,FALSE)
Compared with:
...

**0**

votes

**1**answer

298 views

### Excel, Average dates between transactions?

I have an inventory data set that I am playing with and I have hit a wall in trying to figure out something that I want to do.
The data set has a name column (name of item; e.g., “hammer” or “saw”), ...

**0**

votes

**3**answers

497 views

### Excel - Sum First Value Of Non-Unique Adjacent Column

I have a table:
ColA | ColB
1 1
2 1
2 1
3 1
4 1
5 1
I would like to sum ColB only once where ColA = 2.
Essentially, I would like to sum the values ...

**1**

vote

**0**answers

313 views

### What is the best way to create charts of “database-style” tables and data in Excel?

I'm a biologist and I frequently have to do analysis on relatively large (for Excel) datasets. Usually a few charts are needed to display the data, but creating them seems to require laying out out ...