How to Insert Multiple Formulas in One Cell in Excel
Prologue
In this article, you’ll learn how to insert multiple formulas in one cell in Excel.
You can display output based on input if you combine multiple formulas in one cell. Moreover, text can be added to formulas. Additionally, spreadsheet space is saved when inserting multiple formulas into one cell.
However, you need to keep in mind that inserting multiple formulas makes any formula more complex. This makes it harder to understand and maintain formulas. You should therefore use this process sparingly.
Insert Multiple Formulas in One Cell
We’ll show you how to combine multiple formulas in one cell in Excel. The goal is to show the minimum, maximum, average, and total values of the sales figures in the output cell. We’ve merged the cell range D3:D6, and we’ll refer to it as the output cell.
- Firstly, type the following formula to show the minimum value in the output cell.
=”Min: $”&MIN(C3:C6)
- We’ve used the MIN function to find the minimum value from the sales. Before that, we joined a string with the function using the ampersand (&).
- Secondly, add another formula to the cell.
=”Min: $”&MIN(C3:C6) & CHAR(10) &”Max: $”&MAX(C3:C6)
- We’ve added the maximum value using the MAX function.
- Here, the CHAR(10) creates a line break in the formula output.
- You must select the output cell and click on the Wrap Text feature from the Home tab to make the line break work.
- Thirdly, use the AVERAGE function to attach the average sales value.
=”Min: $”&MIN(C3:C6) & CHAR(10) &”Max: $”&MAX(C3:C6)&CHAR(10)&”Average: $”&AVERAGE(C3:C6)
- Lastly, use the SUM function to attach the total sales value.
- Thus, you can use multiple formulas in a single cell.
How to Use Nested Functions in Formula
Now, let’s see another way to combine multiple formulas by nesting multiple IF functions in Excel. We’ve added a column called ‘Target’. The 4 salespeople belong to the same group. If the sales value is more than or equal to the target value, then a salesperson will achieve the goal for him or her. We need to count the number of goals achieved from the data using a single formula.
The criteria for the status are as follows:
Count of Sales Greater Than Target | Status |
0 | Bad |
1 | Poor |
2 | Good |
3 | Excellent |
- To solve this problem, type the following formula in the output cell.
=IF(SUMPRODUCT(–(D3:D6>=C3:C6))=0,”Bad”,IF(SUMPRODUCT(–(D3:D6>=C3:C6))=1,”Poor”,IF(SUMPRODUCT(–(D3:D6>=C3:C6))=2,”Good”,”Excellent”)))
- The SUMPRODUCT function is used three times in the formula to count the number of goals achieved. We’ll break it down for you in the next section.
Decoding Formula
- D3:D6>=C3:C6 🠊 {TRUE;TRUE;FALSE;FALSE}.
- This part compares the value of each cell in the D column with the C column. Basically, this checks if the sales values are greater than or equal to the target values.
- For positive values, it will return True. The first two sales figures are larger than the corresponding target values. Thus, it gave back two True values.
- –(D3:D6>=C3:C6) 🠊 {1;1;0;0}.
- The double negative before the formula converts the Boolean values to 1 and 0. True is 1, and False is 0.
- You can use other arithmetic operations to do this too. You add 0 to this, multiply by 1, etc.
- SUMPRODUCT(–(D3:D6>=C3:C6)) 🠊 2.
- This portion adds all the Boolean values. You can use the SUM function here.
- If you’ve understood up to this point, then the formula reduces to simple form.
- IF(2=0,”Bad”,IF(2=1,”Poor”,IF(2=2,”Good”,”Excellent”))) 🠊 “Good”.
- In this formula, the third condition satisfies the formula. Thus, it will return the status ‘Good’.
Things to Remember
- You can also use the SUM function instead of the SUMPRODUCT function in the second method.
- Don’t overdo when combining multiple formulas into a single cell, as it makes it complicated for the user to maintain and update.
- If you combine formulas, it is good practice to keep notes on them for future reference. That way, it will be easier to change the formula.
Answers to Commonly Asked Questions
Question 1: How do I combine 2 LEFT formulas in Excel?
ExcelFlash: You can combine two LEFT formulas using the ampersand operator (&). For example, if you want to return two LEFT formulas into a single cell, you can type this formula to do so.
=LEFT(B3,2)&LEFT(B4,3)
The above formula returns the first two characters from cell B3 and three characters from cell B4 and combines them into a single cell.
Question 2: How do I use the VLOOKUP function in Excel?
ExcelFlash: The VLOOKUP function is used to lookup values vertically. The syntax of this function is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: the value you want to match
- table_array: the location to search for the value
- col_index_num: the column that contains the value
- [range_lookup]: sets the match for the value approximately or exactly.
The last parameter is optional. The default value is True (approximate match). Where False means an exact match.
You can also use 0 for an exact match and 1 for an approximate match.
Additionally, this function is case insensitive. So, EXCELFLASH or excelflash will be equal in this formula.
Question 3: How do I combine two sum formulas in Excel?
ExcelFlash: It is similar to adding two values from a shopping cart. Firstly, you find the sum of an item list. Then, you calculate the total value of another list. Finally, you add those to combine them.
=SUM(B3:B6)+SUM(C3:C6)
The above formula adds the values of the range B3:B6 and C3:C6 to combine them in a cell.
Epilogue
You’ve seen 2 ways to use multiple formulas in one cell in Excel. If you have any feedback for us, feel free to comment below. You can also follow ExcelFlash on Twitter and YouTube.