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

learn more… | top users | synonyms (3)

18
votes
7answers
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 ...
17
votes
9answers
42k 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
6answers
59k 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
8answers
204k 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
3answers
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
5answers
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
1answer
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
15answers
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
4answers
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
3answers
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
6answers
49k 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
3answers
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
1answer
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
5answers
29k 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
2answers
33k 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 ...
7
votes
3answers
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 ...
7
votes
1answer
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
2answers
173 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
1answer
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
4answers
11k 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
5answers
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
2answers
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
3answers
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
3answers
718 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
4answers
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
3answers
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
4answers
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
1answer
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
5answers
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
1answer
200 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
3answers
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
3answers
4k 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
1answer
253 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
2answers
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
2answers
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
3answers
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
2answers
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
6answers
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
2answers
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
2answers
152 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
5answers
12k 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
2answers
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
2answers
905 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
2answers
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
1answer
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
4answers
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
1answer
268 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
3answers
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
4answers
731 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
4answers
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 ...