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

learn more… | top users | synonyms (5)

4
votes
1answer
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
1answer
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
1answer
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
1answer
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
10answers
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
2answers
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
2answers
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
2answers
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
2answers
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
3answers
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
3answers
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
2answers
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
4answers
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
2answers
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
3answers
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
2answers
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
2answers
48k views

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

I'd like to display an empty box (HTML entity &#9633 □ 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
6answers
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
2answers
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
2answers
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
3answers
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
4answers
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
2answers
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
2answers
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
3answers
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
1answer
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
5answers
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
1answer
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
3answers
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
4answers
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
2answers
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
1answer
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
2answers
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
2answers
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
1answer
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
2answers
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
3answers
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
5answers
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
2answers
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
1answer
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
3answers
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
2answers
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
1answer
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
2answers
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
1answer
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
2answers
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
4answers
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
2answers
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
1answer
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
1answer
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 ...