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

learn more… | top users | synonyms (3)

22
votes
7answers
100k 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
48k 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
7answers
72k 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?
16
votes
7answers
238k 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
14
votes
6answers
80k 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
5answers
45k 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 ...
13
votes
3answers
68k 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
16answers
70k 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
1answer
25k 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 ...
11
votes
2answers
47k 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 ...
10
votes
4answers
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
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
3answers
12k 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
5answers
35k 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
1answer
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?
8
votes
1answer
68 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
1answer
25k 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
4answers
30k 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 ...
7
votes
4answers
14k 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
3answers
6k 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
3answers
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
1answer
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
2answers
211 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
5answers
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
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
977 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
41k 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
2answers
7k 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
3answers
6k 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
1answer
246 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
231 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
10k 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
4answers
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
3answers
6k 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
313 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
1answer
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
2answers
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
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
1answer
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
1answer
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
2answers
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
3answers
379 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
6answers
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
3answers
221 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
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
3answers
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
2answers
20k 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
996 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
4k 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 ...