If you need to display the results of two calculations in a single cell, the simplest way is by concatenating the results of 2 formulas together as text either using an ampersand (
&) or the
CONCATENATE function. If your formula results are numbers or dates that need formatting, you will have to do this using the
TEXT function inside the formula. The number format options on the menus will not work on text cells.
Every cell in Google sheets only allows for a single formula entry. So, the simple answer to this question is NO. You can’t display two formulas in one cell in Google sheets. What you can do is display two calculations in a single cell using a single formula.
I’ll show you how to do that using
GOOGLEFINANCE to display stock and index price changes since yesterday, both as an amount and a percentage in the same cell. Here’s the example sheet if you want to follow along or just jump in and see how it’s done.
How to concatenate (join) formulas as text
If your formula returns a number, joining it to another formula will return the results of both as TEXT (a string).
There are 2 easy ways to join formulas in Google Sheets. Using the CONCATENATE function or the ampersand (&) operator.
CONCATENATE(string1, [string2, ...])Concatenate will accept many arguments and join them all together as text, i.e:
=CONCATENATE(5, " is ", "alive!")would return
5 is alive!You can use it to join formulas, cells and numbers and turn them into a string.
The shorthand operator that does the same thing is ampersand (&), it acts as a synonym for CONCATENATE().
The ampersand operator & You can also use it to join formulas, cells, and number and turn them into a string.
=5&" is "&"alive!"would return
5 is alive!
How to format numbers in text strings
When you have a number in a formula that is being joined to another and displayed as part of a string, then Google Sheets won’t be able to format your number using the standard Format > Number menu choices. Or even any custom number format you may have defined. This is because these rules work on the value of the cell, and your value will contain the results of 2 formulas.
The solution is to use the
TEXT function to format the number within the formula.
TEXT(number, format)The TEXT function is the way to apply a custom number format to a number within a formula. It accepts 2 parameters:
number- The number, date, or time to format.
format- The pattern by which to format the number, enclosed in quotation marks. For example:
There are a lot of number and date format combinations, checkout custom number format help, to see them all.
Putting it together – 2 formulas in one cell
In our example finance sheet, we retrieve stock and index prices using the GOOGLEFINANCE function. You can tell GOOGLEFINANCE to return a specific attribute of a symbol such as “
change” or “
changepct“. We want both, but we want to display them together in one cell, and also format our percentage change so it displays in brackets.
We do this by concatenating both the values together using ampersand (
&) and formatting the percentage change using
TEXT and our custom number format of “
(0.0\%); (-0.0\%)“. That format will add a space in front of and show the percent change inside brackets, and to one decimal place for both negative and positive values. We’ve added the backslash to the format so that it will display a percent symbol and not format the numbers as a percent (as they already are a percentage when returned from google finance).
=GOOGLEFINANCE(C2,"change")&TEXT(GOOGLEFINANCE(C2,"changepct")," (0.0\%); (-0.0\%)")
We also added 2 custom format rules to the example sheet that color the price change rows. Just like the number format, you can’t use the rules based on the value of the cell, because that is now your custom text. So we format the rows based on the value of another GOOGLEFINANCE call.
Checkout the example finance sheet, to see how it all works together!