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

**25**

votes

**7**answers

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

**18**

votes

**9**answers

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

**17**

votes

**7**answers

75k 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?

**17**

votes

**7**answers

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

**16**

votes

**5**answers

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

**14**

votes

**6**answers

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

**13**

votes

**3**answers

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

**11**

votes

**16**answers

72k 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?

**11**

votes

**2**answers

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

**11**

votes

**1**answer

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

**4**answers

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

**1**answer

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

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

**3**answers

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

**4**answers

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

**8**

votes

**5**answers

36k views

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

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

**8**

votes

**1**answer

82 views

### How does the NOW() function get evaluated cross time zones in Excel Online?

How does EXCEL ONLINE evaluate the NOW() function when a spreadsheet is accessed from different time zones? For example, if user PAUL is in London, UK and accesses a sheet at 5PM GMT and user SUE is ...

**7**

votes

**1**answer

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

**7**

votes

**4**answers

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

**7**

votes

**3**answers

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

**7**

votes

**3**answers

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

**7**

votes

**1**answer

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

229 views

### Formula for finding 'near perfect' matches [duplicate]

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

**5**answers

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

**4**answers

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

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

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

**2**answers

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

**5**

votes

**3**answers

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

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

250 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

**1**answer

244 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

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

**4**answers

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

**5**

votes

**3**answers

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

331 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

**1**answer

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

**5**

votes

**2**answers

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

**5**

votes

**2**answers

77 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

**1**answer

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

**5**

votes

**1**answer

1k views

### Conditional formatting to highlight the whole row

I would like to highlight the whole row 5 if J5="T".
I used to do it with the old Excel, the 2010 conditional formatting ignores it. So my rule is like this and it does not work. I used the formula ...

**5**

votes

**2**answers

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

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

**4**

votes

**6**answers

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

**3**answers

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

**4**

votes

**4**answers

190 views

### Showing whether a list in Excel has a 1 or a 0 in it

So I have a column in excel that I want to roll up into one row. Essentially the column has a 0 if a feature isn't present, and a 1 if it is.
What I want to do is get excel to check a column, lets ...

**4**

votes

**3**answers

237 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

**5**answers

14k views

### Using excel, how can I count the number of cells in a column containing the text “true” or “false”?

I have a spreadsheet that has a column of cells where each cell contains a single word. I would like to count the occurrences of some words. I can use the COUNTIF function for most words, but if the ...

**4**

votes

**3**answers

3k views

### Exclude outliers from average calculation

I want to calculate the average of numbers given in Column A as shown in the figure below. But I want to base the average on values equal to or greater than 15 and equal to or less than 25, via the ...

**4**

votes

**2**answers

21k 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, ...