How to Create Multiple Formulas for the Same Space in Excel
To combine multiple formulas in a single cell, use Excel's IF function or merge results with an ampersand.
Things You'll Need
- Microsoft Excel
In Excel 2013, each cell only contains a single item, whether that item is a number or a formula. If you need to use two different formulas in the same cell, combine their functions and equations into a single item by using an ampersand or the IF function.
You don't need to use either of these methods just to use two functions within one cell's formula. Nest functions together using parentheses or separate them with math operators, such as "=SUM(A1:A3)-LEN(SUM(B1:B3))"
Combine Using an Ampersand
Excel's CONCATENATE function merges the results from several strings of text — including the output of formulas — in a single cell. The simplest way to use CONCATENATE is by typing an ampersand between each of your formulas.
Start a new formula as usual with =, and then enter your first formula (or a number, text string or cell reference). Type an & and then enter your second formula. Use additional ampersands to string as many pieces together as necessary.
Only include an = at the beginning of the cell, not after each ampersand.
Go back through the line of formulas and insert text strings in quotes, separated by additional ampersands, to visually separate the results of each formula. Press Enter to finish.
If cells A1 through C1 read "1, 2, 3," the formula "=A1&SUM(B1:C1)" resolves to read "15," because the two formula results ("1" and "5") have no visual separator. Fix the problem by inserting text strings — even a single space — in quotes. Changing the formula to "=A1&" "&SUM(B1:C1)" splits the result, making it read "1 5" instead.
Combine Using IF
Use an IF function when you want Excel to perform one formula in certain conditions or another formula in different conditions. With IF, you get to write two separate formulas in one cell, but only one of the two operates at any time.
Start an IF function with =IF( and a logical test. A logical test compares two pieces of data using equals, greater-than and less-than signs. A logical test can range from the simple, such as "=IF(A1=5" to testing the results of a formula, such as "=IF(SUM(A1:A3)>2". After the test, type a comma.
Use >= or <= for "greater than or equal to" or "less than or equal to," respectively.
Type one formula to run if the logical test is true. Type a comma, and then type the other formula to run when the test is false. Close all open parentheses and press Enter to finish.