Questions about using in-cell functions/formulas in spreadsheet software.
11
votes
8answers
126k 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
10
votes
6answers
23k 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 ...
10
votes
1answer
10k 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 ...
9
votes
5answers
20k 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?
9
votes
3answers
879 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)
7
votes
10answers
33k 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?
7
votes
3answers
18k 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 ...
7
votes
1answer
2k 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 ...
6
votes
5answers
41k views
Excel: 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 ...
6
votes
3answers
9k views
What is the difference between function and formula in Excel?
What is the difference between those two when speaking of Microsoft Excel?
6
votes
3answers
2k 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
5answers
688 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 ...
5
votes
5answers
5k 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
141 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
6k 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
1answer
53 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
4answers
3k 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)?
5
votes
2answers
66 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
641 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
252 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
3k 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
62 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
2answers
672 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
3answers
63 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 ...
4
votes
2answers
2k 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
3answers
4k 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
1answer
4k 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?
4
votes
1answer
3k views
Shortest Method To Reference A Single Row/Column of a Named Range in Excel?
I have a named range (NamedRange1) and I need to reference the first row and first column of that range within a formula. I came up with the following:
First row:
INDIRECT("R" & ...
4
votes
2answers
40 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
2answers
392 views
Counting “0” Stock Values Generated by Pivot Table
I have an inventory ledger. It looks like this:
Tran Type Txn Date Item Quantity
Opening Balance 6/30/12 Item1 4
Opening Balance 6/30/12 Item2 7
Shipping ...
4
votes
3answers
13k 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:
...
4
votes
1answer
189 views
Issue in Excel: Copy a cell with formula - pastes value of the copied cell
I have the following copy and paste issue since yesterday. Probably it started when I was trying to edit a locked Excel-doc which i found on a shared server (that is - it was not created with my ...
4
votes
1answer
194 views
Function argument delimiter - Excel 2011 for Mac OS X
I have two Mac OS X machines, both running Microsoft EXCEL 2011.
One uses a comma to separate function parameters, e.g:
function(arg1, arg2)
Yet my work machine uses semi-colons, e.g:
...
4
votes
1answer
2k views
Using INDIRECT and AND/IF for conditional formatting
I am having some issues with using INDIRECT to apply conditional formatting.
I have column G which carries a percentage. I want to conditionally format column H based on the percentage being greater ...
3
votes
4answers
22k views
Best way to remove leading zeros from a non-numeric value in Excel
I have many cells in an Excel sheet, using 9 characters of 0-9 and A-Z, that have some number of prefixed zeros:
000000123
000001DA2
0000009Q5
0000L210A
0000014A0
0000A5500
00K002200
I'd like to ...
3
votes
2answers
499 views
Split a string in a cell in Excel using numbers as the divider
Example cell value: President & Founder, Millennium Pain Center, 1998– present.
I'd want that parsed (split) into "President & Founder, Millennium Pain Center," and in another cell, " 1998– ...
3
votes
3answers
6k views
How to separate number by its decimal point in Excel/Calc?
I've got a number in a spreadsheet like so: 28.686279
When I open this sheet in either LibreOffice Calc or Microsoft Excel, is there a formula function that returns the "whole number", I.e. 28. Also, ...
3
votes
3answers
363 views
Is there a way to set the name box of a cell programmatically in Excel
I would like to programmatically set the name box of a cell in a worksheet I'm working on, either through a formula or through a macro. The end point is that I don't want to set it by hand.
In my ...
3
votes
2answers
677 views
Open Office Calc - Round to 5?
Is there a way I can have a cell round a number to the the nearest 5?
For example, the number 114 would round to 115, or 62 would round to 60
3
votes
4answers
10k 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 ...
3
votes
1answer
293 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 ...
3
votes
1answer
195 views
ADDRESS function nested in SUM returns Err: 502
When working on one problem, I came upon something that confuses me and was hoping for some clarification.
I am trying to use the SUM() function in a very round about way.
The formula
...
3
votes
1answer
9k 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 ...
3
votes
1answer
1k 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 ...
3
votes
1answer
126 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 ...
3
votes
2answers
152 views
How to choose a multiplier based on certain cell input in Excel?
I need some assistance with how to write an Excel formula for the below:
In cell A28 one of the following will be entered: LIN, LOX, LAR, CO2, GH2, GN2 or GO2
Depending on which is entered in cell ...
3
votes
2answers
876 views
Non identical random numbers
I have the following formula in Excel (not vba):
=RANDBETWEEN(1,10)
Then I auto fill down 10 rows. How can I re-write the formula so the numbers don't repeat.
3
votes
2answers
5k 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 ...
3
votes
4answers
7k views
Excel - Delete specific word in each row?
Is there a way to have Excel delete a sepcific word in every row? For example, each row says Mr. and Mrs. {first name} {last name}, and I want to delete the {first name} from each one. (Or ...
3
votes
1answer
74 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 ...
