How To Put Multiple Formulas In One Cell Google Sheets

Lesson 13: Creating Complex Formulas

/en/googlespreadsheets/creating-simple-formulas/content/

Introduction

You lot may have feel working with formulas that incorporate simply ane operator, such as 7+nine. More circuitous formulas tin can comprise several mathematical operators, such equally 5+2*viii. When there's more than one functioning in a formula, the order of operations tells Google Sheets which performance to calculate beginning. To write formulas that will give you lot the correct reply, you'll need to empathize the gild of operations.

Lookout man the video below to learn how to create circuitous formulas.

Order of operations

Google Sheets calculates formulas based on the following gild of operations:

Operations enclosed in parentheses
E xponential calculations (3^2, for instance)
M ultiplication and division, whichever comes first
A ddition and subtraction, whichever comes beginning

A mnemonic that can assistance you lot think the club is Please Due eastxcuse Chiliady Dear Aunt Sally.

Click the arrows in the slideshow below to acquire how the order of operations is used to calculate formulas in Google Sheets.

While this formula may wait complicated, we tin can apply the order of operations step past step to find the right reply.
Starting time, we'll start by computing anything inside parentheses. In this instance, there'southward only 1 affair we need to summate: half-dozen-iii=3.
As you tin see, the formula already looks simpler. Next, we'll look to see if in that location are any exponents. At that place is 1: ii^2=4.
Side by side, we'll solve any multiplication and division, working from left to right. Because the division functioning comes earlier the multiplication, it's calculated first: 3/four=0.75.
Now, we'll solve our remaining multiplication operation: 0.75*iv=3.
Next, we'll calculate any addition or subtraction, again working from left to right. Improver comes first: 10+3=13.
Finally, we have ane remaining subtraction operation: xiii-i=12.
Now we have our respond: 12. And this is the exact same upshot you would get if you entered the formula into Excel.

Creating complex formulas

In the example beneath, we'll demonstrate how Google Sheets solves a complex formula using the society of operations. The complex formula in prison cell D6 calculates the sales tax by adding the prices together and multiplying by the 5.5% tax rate (which is written as 0.055).

Entering a complex formula

Google Sheets follows the gild of operations and offset adds the values inside the parentheses: (D3+D4+D5) = $274.10. Then it multiplies by the tax charge per unit: $274.10*0.055. The result will show that the tax is $15.08.

The result in D6

It's particularly important to follow the social club of operations when creating a formula. Otherwise, Google Sheets won't calculate the results accurately. In our example, if the parentheses are not included, the multiplication is calculated first and the effect is incorrect. Parentheses are oft the all-time way to define which calculations will exist performed first in Google Sheets.

Result of an incorrect formula

To create a complex formula using the social club of operations:

In the example beneath, we'll use cell references forth with numerical values to create a complex formula that will calculate the subtotal for a catering invoice. The formula will calculate the cost of each carte du jour item showtime, then add these values.

Select the prison cell that will incorporate the formula. In our instance, we'll select cell C 5.
Enter your formula. In our example, we'll type =B3*C3+B4*C4. This formula will follow the guild of operations, first performing the multiplication: 2.79* 35 = 97.65 and 2.29*20 = 45.fourscore. It and then will add these values to calculate the total: 97.65+45.lxxx.
Double-bank check your formula for accuracy, so press Enter on your keyboard. The formula will summate and display the outcome. In our example, the result shows that the subtotal for the order is $143.45.

Google Sheets will not always tell you lot if your formula contains an error, so it's upwardly to you lot to check all of your formulas. To learn how to do this, read our article on why you should Double-Bank check Your Formulas.

Challenge!

Open up our case file. Make sure yous're signed in to Google, then click File > Make a re-create.
Select the Claiming sheet. Let's say we desire to compare two discounts. The beginning discount takes xx% off the total, and the 2d disbelieve takes $30 off the total.
In cell D6, create a formula that calculates the total using the twenty% off discount.
Hint: Because we're taking 20% off, lxxx% of the full will remain. To summate this, multiply 0.eighty by the sum of the line totals.
In cell D7, create a formula that subtracts xxx from the total.
When you're finished, your spreadsheet should await like this: