We’ll use the following dataset to combine formulas in a single cell.
Step 1 – Creating the Data Set
In this step, the SUM function and the AVERAGE function are applied in two different cells to show the difference between multiple Excel formulas used in one cell and multiple formulas in different cells.
- We want to determine the total sales for all the salespeople and their average sales value.
- We apply the SUM and AVERAGE functions to determine the total sales and average sales value in two different cells, respectively.
- Here are the final results of the total sales and the average sales.
Step 2 – Inserting the First Formula
- Use the following formula:
="Total Sales = "& SUM(C5:C12)- Hit Enter.
- You will see the results for the first formula in the image below.
Step 3 – Utilizing the Ampersand Operator
If we want to combine or apply separate Excel formulas in one cell, use the Ampersand operator to put multiple formulas in one cell. We’ll use the Ampersand operator at the last position of the first SUM formula to add another formula in the same cell.
Step 4 – Inserting the Second Formula
- Complete the formula:
="Total Sales = "&SUM(C5:C12)&", "&"Average Sales = "&AVERAGE(C5:C12)- Press Enter.
Step 5 – Showing the Final Results
Download the Practice Workbook
Related Articles
- How to Apply Formula to Entire Column Without Dragging in Excel
- How to Apply Same Formula to Multiple Cells in Excel
- How to Apply a Formula to Multiple Sheets in Excel
- How to Exclude Zero Values with Formula in Excel
- How to Make FOR Loop in Excel Using Formula
- How to Apply Formula to Entire Column Using Excel VBA
<< Go Back to How to Create Excel Formulas | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hey, I managed to complete this after quite a few rudimentary google search results left me thinking it weren’t possible to have more than one function in a single cell, so much thanks to you.
I now managed to show in a single cell the current date minus a given buying date in a different cell shown in amount of days passed (check), as well as shown in years.
However the year is now shown as 0,063013698630137 for 23 days and I want a maximum of 2 decimals -> 0,06 Years.
Sadly, I can’t find a way to do this, the normal Decrease Decimal Format command simply has no effect on my new single cell and an error sound is played.
Same goes for advanced Format menu and trying Number/Date formats.
If anyone knows how to get it fixed (for example is there a limit function to go directly into the formula to limit it to 2 decimals?) would be awesome, thanks for your help.
I left a reply seeking help with this excel article a few minutes ago,
however ChatGPT has helped me out showing me the final working formula to enter for my problem, which is
=(TODAY())-E2&” | “&ROUND(((TODAY())-E2)/365;2)
so I no longer require any help, thanks for showing me that it’s possible with & to enter multiple formulas into a single cell, after initial google search results seemed to imply that this is not possible.
Hello, Kevin King. I am glad that you liked this article and this article helped you when you needed the most. Hopefully, we will help you more with our other articles. Please leave a message if there is any other query.