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

**4**

votes

**1**answer

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

**4**

votes

**1**answer

59 views

### Sorting data without mixing rows

I have three sheets. First sheet renamed General:
Dave Henderson 21/05/2014
Elton Rus 22/06/2014
John Smith 20/05/2015
Second sheet renamed ...

**4**

votes

**1**answer

228 views

### Taking an indirect reference in Excel and making it a direct reference

For many somewhat complicated reasons – it has to do with transposing columns and rows because of third party software limitations, etc. – I have had to use the INDIRECT function in order to make ...

**4**

votes

**1**answer

56 views

### Formula bar view changes when document is saved and reopened

I have an Excel 2007 document with a lot of formulas of the format:
SUMIFS(view1[COLUMN1],view1[COLUMN2],$C13,view1[COLUMN3],I$10)
but when I save the sheet and reopen it each formula is changed to ...

**3**

votes

**10**answers

180k views

### Excel isn't calculating the SUM of cells [duplicate]

I am trying to use a very simple formula which is =SUM(B9:B11). However the cell doesn't compute for some reason.
I've used Excel for years and have never had this problem. Any idea why it may be ...

**3**

votes

**2**answers

225 views

### Excel vlookup reference purposely missing shows #N/A - stop it

I have a cell which does a vlookup.
But the table to which it refers is always changing and when the specific value is there is shows fine.
But when the value isn't there it shows #N/A - how can I ...

**3**

votes

**2**answers

34k views

### How do I get “yes” or “no” in Excel if the information comes from “true” and “false”?

first of all, I have to point out that I am a German visiting this forum (I couldn't find anything useful on the German websites), so excuse my English if it is bad here and there ;-)
Here is my "...

**3**

votes

**2**answers

785 views

### Why is this Excel formula returning two different values?

I am attempting to use Excel (2013) to look up whether the value 1 turns up in a given set (using the CHOOSE() function, then, if so, printing the value in the cell directly to the right of it using ...

**3**

votes

**2**answers

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

**3**answers

6k views

### List of ALL Excel Functions by Version

Is there a table somewhere showing all the Excel functions and the versions in which each is supported? I'm trying to make sure I can share my 2010 worksheets with 2003 users, and don't want to use ...

**3**

votes

**3**answers

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

**2**answers

244 views

### Can Excel use wildcards to determine if a cell contains specific text?

If A1 contains miles, the following evaluation is true:
=IF(A1="miles",1,0)
If A1 contains miles and miles, the following evaluation return false:
=IF(A1="*miles*",1,0)
Every resource I find uses (...

**3**

votes

**4**answers

527 views

### Find largest value, Nth largest and others before it? (while ignoring duplicates) [duplicate]

So I have a column say A that looks like this:
I
15
0
3
15
M
8
0
8
21
Q
0
5
0
0
I can find the largest value with =MAX(A1:A100)
I can find the smallest value with =SMALL(A1:A100,1)
How do i find ...

**3**

votes

**2**answers

537 views

### Fill a large range with a formula in Excel, without mouse-dragging to extend

I have 5000 elements in column A and 5000 in column B
I want the element wise difference and stored in column C.
The subtraction command I know is =A1-B1 then drag all the way down with a mouse.
...

**3**

votes

**3**answers

2k views

### In Excel, can you define a cell or range to be used in a formula based on the contents of another cell or formula?

What I want to do is this:
Have one cell calculate the number of items present in a particular column, e.g. C1 = 129
Have a second cell use that number as part of the definition, e.g. D1 = AVERAGE($...

**3**

votes

**2**answers

4k views

### How do I create a data validation list WITHOUT using cells?

I have a simple data validation dropdown that I'm trying to do, but I DO NOT WANT to use cells to populate my list.
Something similar to what's below, but this obviously isn't working.
What ...

**3**

votes

**2**answers

48k views

### Displaying a value only if another cell has a value in Excel

I'd like to display an empty box (HTML entity □ □ or any other similar box will do) in Excel in a cell if and only if another cell has a value.
I've tried =IF(ISBLANK(C8),,□), but that ...

**3**

votes

**6**answers

6k views

### Is there a way to tell which cells reference a selected cell in Excel?

In Excel, you can tell a cell to reference another cell in order to calculate its value. I'd like to be to do the opposite, to get any cells that reference the currently-selected cell. Is this at all ...

**3**

votes

**2**answers

98 views

### Perform Sum over Conditional Products

I do have a list like this
╔═════╦═══╗
║ 1.5 ║ A ║
║ 2.5 ║ B ║
║ 2.5 ║ ║
║ 5.2 ║ A ║
║ 6.4 ║ C ║
╚═════╩═══╝
and want as result:
╔═══╦═════╗
║ A ║ 6.7 ║
║ B ║ 2.5 ║
║ C ║ 6.4 ║
╚═══╩═════╝
So ...

**3**

votes

**2**answers

433 views

### How to insert the =now() function in a cell automatically when I write in another and hit enter [duplicate]

I would like to know how to:
Automatically have the =now() function in the column B of Excel, only displayed in each cell (B1, B2, .. Bν) whenever I write something in column A in the respective ...

**3**

votes

**3**answers

475 views

### Split Excel Cell by last Delimiter

I need to split a cell by the first delimter (reading right to left) so that I can put that value in another cell.
For Example, my data in cell A1 could look like:
"something - more something - a ...

**3**

votes

**4**answers

11k views

### How do I calculate number of hours > 24 in Excel?

I've got an excerpt of my spreadsheet below. It's a time tracking application containing a start date/day, a session ID and the duration of the session. Column A is populated by adding the duration in ...

**3**

votes

**2**answers

7k views

### How can I change a cell's background color based on the cell's current value?

I have a column with function values of True or False:
=IF(ISNA(A1), false, true)
from row 1 to 5000+ rows. I would like to mark all the False as red background color or text as red. Is there any ...

**3**

votes

**2**answers

1k views

### Excel 2010 formula to delete repeated content based on value

I have a spreadsheet with two columns, a name and a value. The name is repeated several times with different values, for instance:
Name - 1
Name - 2
Name - 3
etc.
I am looking for a formula ...

**3**

votes

**3**answers

5k views

### Multiplying values in Excel 2010 (Same cell)

Another excel question. I'm using Excel 2010.
If for example I have a value in cell A1, lets say 2.59
What I want to do is multiply that cell by 1.333 but in the same cell
I don't want to have to ...

**3**

votes

**1**answer

7k views

### Hide “inconsistent formula” error in a selection in Excel

This may seem easy to more advanced users, but I can't seem to find this out there.
I have a complex Excel worksheet. It has various formulas/calculations throughout the sheet.
Unfortunately, ...

**3**

votes

**5**answers

24k views

### How can I show values in the formula bar instead of cell references?

Here is what I have in my worksheet:
A1=2 B1=3 C1=A1+B1
I want the actual cell to show 5 but when I copy C1 I want the formula in the cell to show =2+3, not =A1+B1 or 5 is there a way to do this?...

**3**

votes

**1**answer

2k 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
=SUM(ADDRESS(...

**3**

votes

**3**answers

2k views

### Convert a formula in Excel into an Array Formula without Ctrl-Shift-Enter?

Question: How might it be possible in Excel to convert a formula into an array formula, without using a hotkey?
Normal Formula
Array Formula

**3**

votes

**4**answers

18k views

### Excel-MATCH function doesn't work on pasted values

I'm trying to find the positions of the values(text) in one column in another column. I ran the function: =MATCH(B1, A:A, 0) and I get a #N/A result. But this result is incorrect...as I clearly see ...

**3**

votes

**2**answers

4k views

### Excel: searching for a maximum lower than X

From the sorted list of values, I'm looking for a maximum that is lower than X
Example:
A B C
1 1 X=7
2 3
3 5
4 8 ANS=5
Do you have any smart ideas for this problem?

**3**

votes

**1**answer

8k views

### How can I get the row number from a vlookup?

So I have two spreadsheets where there is some messy data in one of them that needs to be in another.
What I have done so far is copy the columns I want from the messy spreadsheet into a new tab on ...

**3**

votes

**2**answers

6k views

### Excel reference named range with text and formula

Is it possible to refer to a named range by combining text with a formula? Say I get a name for a range via INDEX which returns a text but I also want to add something after the text like so:
...

**3**

votes

**2**answers

18k views

### sum of vlookup using array formula

I have such table of payments:
account | amount
--------+-------
101 | 3
101 | 5
102 | 7
103 | 9
I named the range of this table "payments". Its first column I called "accounts". ...

**3**

votes

**1**answer

73k views

### Can Excel use formula-input data for cell references?

I have a cell in Excel, to which I want to pull data from another cell that is defined as a value in a third cell.
For example:
A1=5
A2=10
A3=insert syntax here, using A4 as source for cell ...

**3**

votes

**2**answers

868 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

**3**answers

45k views

### Subtracting cells only if they have a value in Excel

I want to subtract two cells in excel but only if both cells have a value. More specifiaclly in my case the formula in cell E2 is currently =D2-C2 but I only want this to be calculated when D2 has a ...

**3**

votes

**5**answers

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

**2**answers

387 views

### Bizarre COUNTIF/COUNTA behavior in excel for counting text cells that are not “”

I have a column of cells filled with formula that give "" on error, and a text string if no error. In a second column I'm giving a formula with:
=COUNTIF(range,"<>")
as well as:
=COUNTA(...

**3**

votes

**1**answer

2k views

### Excel Formula to Check if a Word Begins with a Keyword

As the picture shows, I have 3 keywords from cell A2 to A4: a, bb, and ccc.
I have 5 words to check, listed in B2:B6: aa, bb, bc, ccand d.
I'd like to check, for each word in Column B, that if it ...

**3**

votes

**3**answers

2k views

### Custom user function without using VBA

Is it possible to create a custom user function in Excel without using VBA?
My problem is I have a long string of standard excel functions strung together to make one very long function.
This function ...

**3**

votes

**2**answers

682 views

### Count first occurrence of one criterion based on a second criterion

I am trying to write an excel formula (or formulas) to count the first occurrence of a value in a given year for each Person in my table. I've searched for related questions on Superuser to no avail. ...

**3**

votes

**1**answer

1k views

### How to plot coordinates in Excel?

I have a list of goals scored by Ronaldo and Messi and have created a manual heat map of where they have scored goals from. I want to write coordinates in a cell in a list and for the heat map to ...

**3**

votes

**2**answers

15k views

### How can I filter specific rows from an Excel spreadsheet based on criteria?

I have a spreadsheet from which I need to select ONLY the first five rows in each category.
I only want columns A and B when C is less than 5 -- =IF($C1>5,$A1,) and =IF($C1>5,$B1,) -- but I ...

**3**

votes

**1**answer

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

**3**

votes

**2**answers

336 views

### Does AND work in Excel spreadsheet formulae using Ctrl-Shift-Enter?

I have an Excel (2010) cell with this formula:
{=SUM(IF('Tasks'!$I:$I=$C4,'Tasks'!$F$2:$F$2000,0))}
which works (the correct total is given). But when I enter another item to search for:
{=SUM(IF(...

**3**

votes

**4**answers

74k views

### Extracting Data Between two Characters in Excel

I have the following format in Excel and I am trying to extract the data between the characters:
First Last <flastname@xxxx.com>
I need to extract the email address between < and > into ...

**3**

votes

**2**answers

2k views

### How do I stop Excel from adding double quotes to my formula?

this works:
{=MEDIAN((Table1[MonthFinish]=201012)*(Table1[Days]))}
but if I put 201012 into cell A3, this doesn't done work:
{=MEDIAN((Table1[MonthFinish]=A3)*(Table1[Days]))}
when i do Evaluate ...

**3**

votes

**1**answer

2k views

### Request: Excel equivalent of Google Spreadsheet ARRAYFORMULA + CONTINUE functionality

Google Spreadsheets has a syntax permitting a user to specify an entire row, column, or area with a formula in a single cell. For example, entering ...
=ARRAYFORMULA(ROW(A3:A6))
... into cell C1 ...

**3**

votes

**1**answer

103 views

### Index-Match returning value in cell above desired value

I have a couple index matches going on in a workbook I'm playing with. I basically have a huge lists of addresses in a table and I'm trying to pull specific ones to get mailing codes. I index match ...