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

**18**

votes

**7**answers

89k views

### How to count number of distinct values in a range?

I've got a large table that is already organized using filters etc. I'd like to add a summary underneath certain columns that contain the number of distinct values in that column.
There's no ...

**15**

votes

**9**answers

41k views

### Formula for updated currency exchange rates in Excel or other spreadsheets?

What kind of spreadsheets support a formula like the following:
=exchangeCurrency("USD"; "EUR"; 3000)
The result should be 3000 USD exchanged into Euros. Possibly a date could be provided for a ...

**14**

votes

**6**answers

58k views

### How do I get the day name into a cell in Excel?

Given a date, how do I get the day of the week (e.g. "Monday") into a cell in Excel?

**14**

votes

**8**answers

202k views

### How to compare two columns and find differences in excel

I have two columns in Excel that I want to compare and find the differences between them.
Suppose
Col A has 50 numbers i.e 0511234567
Col B has 100 numbers in the same format

**13**

votes

**3**answers

66k views

### Excel: Replace occurences of one character with another in a String?

I want an excel formula that will change Haverford, PA to haverford,+pa. (Text is to lowercase, and spaces are replaced with plus characters.)
The following will get the first space, but not all of ...

**12**

votes

**5**answers

36k views

### How to create a formula for every row in a column in Google SpreadSheet?

I use three columns. A, B and C.
In column C I have a formula every row =A1*(1.6*B1) and then for the next row I have =A2*(1.6*B2) in C2.
How can I do so I don't have to type in the new formula in ...

**11**

votes

**1**answer

20k views

### How do I put logical operators in an Excel =IF Formula?

I'm trying to enter a formula to display text according to an IF condition. The best I can manage is something like...
=IF(myval>=minval & myval <= maxval, "OK", "Not OK")
But this ...

**10**

votes

**15**answers

61k views

### How to get the current column name in Excel?

What is the function to get the current line number and the current column name for a cell in Excel?

**10**

votes

**4**answers

24k views

### Can you do Regular Expressions in Excel without VBScript?

I'm looking for an Excel function that I can put in a cell to do a regex search of the contents of another cell. Is there anything available to do this? I'd prefer not to have to add VB Script to ...

**9**

votes

**3**answers

2k views

### Is it possible to get the sheetname?

How can I get the sheet name in a cell? (I have more than one sheet, I want to get the current on each sheet)

**8**

votes

**6**answers

47k views

### In Excel, how do I check if a cell is in a list of values (a range of cells)

I've got a range (A3:A10) that contains names, and I'd like to check if the contents of another cell (D1) matches one of the names in my list.
I've named the range A3:A10 'some_names', and I'd like ...

**8**

votes

**3**answers

9k views

### Type math formulas in LaTeX way in Microsoft Word?

I wonder if there is some free solutions for typing math formulas in LaTeX way in Microsoft word document (Office 2007)?

**8**

votes

**1**answer

8k views

### allow two possible values in a Excel cell

I would like to create a cell format for one cell to only allow the value "yes" or "no" for this cell.
How could I do this?

**7**

votes

**5**answers

28k views

### What is the difference between function and formula in Excel?

What is the difference between those two when speaking of Microsoft Excel?

**7**

votes

**1**answer

3k views

### Get column number of an address in Excel

In cell LJ131 I have written the text string "$HG$86" (w/ out the quotes).
I need to get the column number of the address value represented by that string, $HG$86. Everything I've tried thus far ...

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

**6**

votes

**1**answer

20k views

### Create a pie chart from distinct values in one column by grouping data in Excel

I have an Excel document containing information from a survey. I want to create a pie chart over location (countries).
How can I make Excel group all the distinct values together and then display ...

**6**

votes

**2**answers

32k views

### What is the Excel hotkey to re-calculate all formula in sheet?

I have a sheet that contains many formulas. But they do not recalculate automatically when I change input.
I'm looking for a hotkey, that can re-calculate the sheet. According to this page, F9 ...

**6**

votes

**4**answers

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

**6**

votes

**3**answers

5k views

### What is a formula to remove the whitespace from an Excel cell when trim doesn't work?

I have a table of Excel data that I copied from an html table in Internet Explorer. A column of table values all seem to have a space at the end of them, but Trim won't remove it. When I paste-special ...

**6**

votes

**5**answers

951 views

### Excel Formula to Calculate Duration of Time Based on a String of Text Such As 9:00am — 12:30pm

I'm trying to develop an Excel formula that would calculate the actual time duration for a string of a time range of the following format: 1:00am – 3:00am
According to this article it seems it should ...

**6**

votes

**2**answers

12k views

### Excel function that evaluates a string as if it were a formula?

Suppose I have a text string like "11+5" or even "=11+5" stored in a cell. Is there a function in Excel that will allow me to actually evaluate that string as if it were a formula?
This would be ...

**6**

votes

**3**answers

4k views

### How do I get a reference to the last occurrence of a value in an array in Excel?

So if
A1:A5 = {3, 3, 4, 4, 5}
what function could I put into B1 to get a reference to A4 (the cell containing the last occurrence of 4)?

**6**

votes

**3**answers

699 views

### Excel Formula to Calculate the Third Point in a Triangle

I need an Excel formula that can help me find the third point in a triangle, given most other information. This is not homework, meaning I do not need to do this just once. I need a consistent way to ...

**6**

votes

**4**answers

33k views

### How to combine values from multiple rows into a single row in Excel?

I have a data dump in Excel that consists of annual customer data for two different values. The data was provided with a separate row for value for every year and customer. I.e. it looks like this:
...

**5**

votes

**3**answers

4k views

### Excel formula to convert boolean values into {0, 1}

What is the Excel formula to convert boolean values {FALSE, TRUE} into {0, 1}?
Supposing there is one shorter than =IF(cond,1,0).

**5**

votes

**4**answers

25k views

### How to search for newline or linebreak characters in Excel?

I've imported some data into Excel (from a text file) and it contains some sort of newline characters. It looks like this initially:
If I hit F2 (to edit) then Enter (to save changes) on each of the ...

**5**

votes

**1**answer

237 views

### Excel formula needed

I'm not so good in excel but i "think" my problem can be solved using excel.. I've got a column with numbers and i'd like to find the best combination of using these numbers. In
our case the best ...

**5**

votes

**5**answers

8k views

### How to return everything between two characters in Excel?

I'm a programmer with lots of languages, but somehow I've never learned Excel macro or formula language. I'm sure there must be a simple way to do this, but I'm stumped.
I have a column of file names ...

**5**

votes

**1**answer

198 views

### Is there a useful difference between ISBLANK and =“”?

In Excel, the ISBLANK function seems to effectively behave the same as a comparator of ="". However, the former requires more than double the amount of characters that the latter does.
Is there any ...

**5**

votes

**3**answers

9k views

### Find missing entries from a list of sequential numbers in a column?

I have an Excel sheet containing the results of 66000 test cases. Or, at least it should...
Now, because I was running the tests asynchronously and kept stopping and starting I had the foresight to ...

**5**

votes

**3**answers

3k views

### Check if Excel cell text is numeric using formula only

I have to find out if my cells text is a numeric value and wanted to use an elegant non VBA method that doesn't impede on its current state or value.
What I've found is that the ISNUMBER() function ...

**5**

votes

**1**answer

246 views

### What should I do if I see a reference in Excel cell formulas to a sheet that I cannot see in the workbook?

Similar to 'Reference Data' in the publicly accessible file described below.
There is the Capacity Planning Guide spreadsheet excel file on Capacity Planning Considerations for Windows Azure Caching, ...

**5**

votes

**2**answers

76 views

### Formatting Dates

I have the following formula:
=D56&" "&"("&D57&" Days"&")"
D56 has a date within the cell. I want to format the date like 5/5/2011 but I'm not sure how to do it.

**5**

votes

**2**answers

1k views

### Excel: Counting number of MAX values in a range

I am using Excel 2003 (version limited due to corporate policy) to keep a score sheet:
A B C D
1 Round | Rita | Sue | Bob
------+------+-----+-----
2 1 | 5 | 3 | 4
3 ...

**5**

votes

**3**answers

349 views

### Excel: autofill formulas in lists

I have a rather huge Excel sheet where the first 20 columns contain input data, further 15 columns contain formulas which use the input data for calculations.
I frequently remove the entire input ...

**5**

votes

**2**answers

1k views

### Highlight row if date is in current week

I'm working on a data table for finances. I want to highlight the row if the date is in the current week:
What it should look like:
The current condition formatting for the yellow fill spans ...

**4**

votes

**6**answers

4k views

### In Excel, how to fill down consecutive integers following a pattern 1, 1, 2, 2,

In Excel, I know how to use the "fill down" feature to populate a column of sequential numbers.
I would like to do this but following a pattern like:
1
1
2
2
3
3
Does anyone know how to do this? I ...

**4**

votes

**2**answers

6k views

### How do I insert formula in between sentences?

How do I insert formula in between sentences? For example, in A1 i want to write:
Welcome =C1
So =C1 will replaced with anything in C1 cell.
If in A1 I write Welcome, then in B2 I write =C1 then I ...

**4**

votes

**2**answers

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

**4**

votes

**2**answers

18k views

### excel VLOOKUP, how to specify dynamic worksheet name?

I'm trying to create a VLOOKUP formula in excel, however, the table array may be in a number of different worksheets.
For example, I have this forumula
=Vlookup(a1, ...

**4**

votes

**2**answers

901 views

### How can I create a reusable function in Excel 2007?

How can I create a resuable function/formula I can use in formulas in a spreadsheet? In particular I need a very simple DateDiff formula and would like a way to encapsulate this function:
...

**4**

votes

**2**answers

3k views

### How to make two formulas reference the same cells in Excel?

I have one row that uses =MEAN(A1:A15), and another one that uses =SUM(A1:A15). But many times I have to alter this range to, let's say, A1:A13;A15 in both cells. I would like to alter the range in ...

**4**

votes

**1**answer

2k views

### Shortcut to accept suggested formula in Excel

I'm working with a lot of formulas in Excel 2007 and I would like to know what shortcut key you need to press to accept the suggested formula.
When I type in =DEG the first suggested formula is ...

**4**

votes

**4**answers

2k views

### Excel 2010, How do I write this formula? Data is on a different worksheet, same workbook

Please help me with this formula:
(worksheet A) has dates in Column A for the whole year in this format (06/19/13) and several different names of people in Column B. Lets use the name (Brian) for ...

**4**

votes

**1**answer

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

**4**

votes

**3**answers

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

**4**

votes

**4**answers

729 views

### Spreadsheet forumula questions to pick a random DVD

I have a list of my (279) DVD’s in an OpenOffice Spreadsheet (version 3.3.0) on my Windows 7 laptop and I want to create a formula to randomly pick a film to watch (for occasions when I can’t make up ...

**4**

votes

**4**answers

21k views

### How to calculate hours and minutes between two time periods?

I’m working on a very simple time sheet for my work in Excel 2007, but I have run into trouble about calculate the hours and minutes between two time periods.
I have a field: timestart which could be ...

**4**

votes

**2**answers

2k views

### Using find() in Excel to remove text from string

I'm using find to remove some of the test string in lookup cell value
For instance I look-up the following in cell A2 Application: sales_rep_support and use find =RIGHT(a2,FIND(" ",a2)+4) to change ...