Google sheets multiple functions in one cell

Functions in google sheets are a great way to do complex calculations. Single functions in a cell are helpful for simple calculations but for complex calculations involving logical conditions, you need to combine multiple functions. Also useful when you just want to print the output from multiple functions in one cell for better data visualisation. 

In this tutorial, we will learn how google sheets multiple functions in one cell work.

The binary concatenate operator (&) combines multiple functions in one cell in google sheets.

Let’s say you want to use these steps to write the SUM() and AVERAGE() formula in one cell.

Here is a quick animation showing google sheets multiple functions in one cell:

The need of having multiple functions in one cell depends on the type of problem you are solving. In this guide, I will explain all these scenarios with examples for easy understanding. 

Table of content:

• Use the binary concatenate operator (&)
• Use concatenate function 
• Use logical expressions

Google sheets multiple functions in one cell using the “&” operator

Let’s say we have the below data from a customer lead form.

Example: 5 formulas in 1 cell

The owner wants to know how many people are looking for Sneakers and who they are. 

You can answer these questions by using individual formulas in separate cells. 

You can have one COUNTIF formula for counting the total number of people and one QUERY formula for showing their name and another QUERY formula to show their age.

Or you can choose to combine all formulas in one cell.

Check the below result:

Here is the formula:

="The following "&COUNTIF($A$2:$A$10, "Sneakers")&" People would like to purchase Sneakers:"&char(10)&arrayformula(join(char(10),(QUERY(A2:C, "Select B where A='Sneakers'")&", Age "&QUERY(A2:C, "Select C where A='Sneakers'"))))

All formulas are joined by using the & character between them.

The COUNTIF formula will count the number of times the word “Sneakers” appears in the cell range A2:A10.

char(10) will add a new line in the result. 

join formula will add the comma to the result

The first QUERY formula will select cells from column B where the cell in column A has “Sneakers” in it. This will output the name of the customer.

The second QUERY formula will select cells from C where the cell in column A has “Sneakers” in it. This will output the age of the customer.

Finally, the ArrayFormula will ensure the formula is applied to all cells in the range and output the result in one cell. 

This is how you combine multiple formulas in one cell in google sheets.

Google sheets multiple functions in one cell using concatenate function  

You can also combine multiple functions in one cell using the CONCATENATE function. This works the same as the binary concatenate operator.

Here is an example showing this in action.

Find Mean, Median, and Standard deviation in one cell

Let’s say we have a list of numbers in column A. We want to know their Mean, Median, and Standard deviation. All in one cell.

So we have below 3 formulas that we are combining:

AVERAGE()

MEDIAN()

STDEV()

And here is the formula

=CONCATENATE("Mean: ",AVERAGE(A1:A5),CHAR(10),"Median: ", MEDIAN(A1:A5),CHAR(10),"SD: ",ROUND(STDEV(A1:A5),3))

Note: CHAR(10) will add line breaks and ROUND() will round up to 3 decimal.

Here is the data set and the result:

As you see here, you have all 3 formulas in one cell.

Google sheets multiple functions in one cell using Logical expressions 

Let’s say you have multiple conditions to evaluate and on successful evaluation one function will execute.

For example, if the average of all numbers is less than 10 then multiply each number by 5.

Another example is, to go through a list of numbers and if it is odd, then convert it to an even number.

Here multiple functions will execute in a series to result in an output.

Let’s demo these examples in google sheets.

Example-1:

In the below example, you have a list of numbers in cell range A1:A10. If the average of the range of numbers is less than 10 then you will multiply each number by 5

The formula is

=ARRAYFORMULA(IF(AVERAGE(A1:A10)<10,MULTIPLY(A1:A10,5)))

Here is the result:

First, we will write the IF formula which takes 2 parameters. First is the logical condition. If it’s TRUE then the second parameter will execute.

AVERAGE(A1:A10)<10 – checks if the average is less than 10

MULTIPLY(A1:A10,5) – multiply cell value by 5

Finally the Arrayformula will apply the function in all cells.

Example-2:

In the same data set let’s check if any number is odd then make it even.

The formula is:

=ARRAYFORMULA(IF(ISODD(A1:A10),A1:A10+1))

Here is the result:

ISODD() function checks if the value is odd. If yes, then the IF statement will execute the second parameter to add +1 to the number.

The array formula will apply the function in all the cells in the range. Wherever the number is even, the IF statement will fail and the output will be FALSE.

You can write a custom message instead of printing False like this:

=ARRAYFORMULA(IF(ISODD(A1:A10),A1:A10+1,"Even number"))

This will output “Even number” instead of “FALSE”.

So this is how you combine multiple functions in one cell using logical statements.

You can also use nested if or IFS statements to combine multiple conditions. And each condition can execute one function.

So this is how we approach Google sheets multiple functions in one cell problems.

FAQ

Wrapping up

In this guide, we learned about the concept of google sheets multiple functions in one cell. Combining multiple formulas is complex but many times you need them to solve specific problems.

I am confident that with this learning you will be able to write multiple formulas in one cell in google sheets. So go ahead and practice this in your next assignment.

Appendix

[1] CONCATENATE formula in google sheets – Link

Further Reading

New to google sheets ? Start here

More related to Formulas:

Learn more about Google sheets Formulas.
Logical functions in google sheets
Error handling in google sheets