Creating Complex Formulas

In Google Sheets complex formulas can help you calculate lots of data. Learn how to create complex formulas in Google Sheets.

To create a complex formula using the order of operations:

In the example below, we'll use cell references along 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 menu item first, then add these values.

  1. Select the cell that will contain the formula. In our example, we'll select cell C5.
    Selecting a cell
  2. Enter your formula. In our example, we'll type =B3*C3+B4*C4. This formula will follow the order of operations, first performing the multiplication: 2.79*35 = 97.65 and 2.29*20 = 45.80. It then will add these values to calculate the total: 97.65+45.80.
    Entering the formula
  3. Double-check your formula for accuracy, then press Enter on your keyboard. The formula will calculate and display the result. In our example, the result shows that the subtotal for the order is $143.45.
    The displayed result

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

Challenge!

  1. Open our example file. Make sure you're signed in to Google, then click File > Make a copy.
  2. Select the Challenge sheet. Let's say we want to compare two discounts. The first discount takes 20% off the total, and the second discount takes $30 off the total.
  3. In cell D6, create a formula that calculates the total using the 20% off discount.
    Hint: Because we're taking 20% off, 80% of the total will remain. To calculate this, multiply 0.80 by the sum of the line totals.
  4. In cell D7, create a formula that subtracts 30 from the total.
  5. When you're finished, your spreadsheet should look like this:
    complex formulas challenge example