Changing the Order of Operations in Excel Formulas

01
of 02

Changing the Order of Operations in Excel Formulas

Changing the Order of Operations in Excel Formulas
Changing the Order of Operations in Excel Formulas. © Ted French

The Order of Operations in Excel Formulas

Spreadsheet programs such as Excel and Google Spreadsheets have a number of ​arithmetic operators that are used in formulas to carry out basic mathematical operations such as addition and subtraction.

If more than one operator is used in a formula, there is a specific order of operations that  Excel and Google Spreadsheets follow in calculating the formula's result.

The Order of Operations is:

  • Parentheses "( ) "
    Exponents
    Division
    Multiplication
    Addition
    Subtraction

An easy way to remember this is to use the acronym formed from the first letter of each word in the order of operations:

 P E D M A S

How the Order of Operations Works

  • Any operation(s) contained in parentheses or round brackets will be carried out first.
  • Second, any calculations involving exponents will occur.
  • After that, Excel considers division or multiplication operations to be of equal importance and carries out these operations in the order they occur left to right in the formula.
  • The same goes for the next two operations – addition and subtraction. They are considered equal in the order of operations. Whichever one appears first in an equation, either addition or subtraction is the operation carried out first.

Changing the Order of Operations in Excel Formulas

Since parentheses are first in the list, it is quite easy to change the order in which mathematical operations are carried out simply by adding parentheses around those operations we want to occur first.

The step by step examples on the next page cover how to change the order of operations using brackets.

02
of 02

Changing the Order of Operations Examples

Changing the Order of Operations in Excel Formulas
Changing the Order of Operations in Excel Formulas. © Ted French

Changing the Order of Operations Examples

These examples include step by step instructions for creating the two​ formulas seen in the image above.

Example 1 - The Normal Order of Operations

  1. Enter the data seen in the image above into cells C1 to C3 in an Excel worksheet.
  2. Click on cell B1 to make it the active cell. This is where the first formula will be located.
  3. Type the equal sign ( = ) in cell B1 to begin the formula.
  4. Click on cell C1 to add that cell reference to the formula after the equal sign.
  5. Type a plus sign ( +) since we want to add the data in the two cells.
  6. Click on cell C2 to add that cell reference to the formula after the plus sign.
  7. Type a forward slash ( /) which is the mathematical operator for division in Excel.
  8. Click on cell C3 to add that cell reference to the formula after the forward slash.
  9. Press the ENTER key on the keyboard to complete the formula.
  10. The answer 10.6 should appear in cell B1.
  11. When you click on cell B1 the complete formula = C1 + C2 / C3 appears in the formula bar above the worksheet.

Formula 1 Breakdown

The formula in cell B1 uses Excel's normal order of operations so the division operation
C2 / C3 will take place before the addition operation C1 + C2, even though the addition of the two cell references occurs first when reading the formula from left to right.

This first operation in the formula evaluates to 15 / 25 = 0.6

The second operation is the addition of the data in cell C1 with the results of the division operation above. This operation evaluates to 10 + 0.6 which gives the answer of 10.6 in cell B1.

Example 2 - Changing the Order of Operations using Parentheses 

  1. Click on cell B2 to make it the active cell. This is where the second formula will be located.
  2. Type the equal sign ( = ) in cell B2 to begin the formula.
  3. Type a left parenthesis   " ( " in cell B2.
  4. Click on cell C1 to add that cell reference to the formula after the left bracket.
  5. Type a plus sign ( +) to add the data.
  6. Click on cell C2 to add that cell reference to the formula after the plus sign.
  7. Type a right parenthesis  " ) " in cell B2 to complete the addition operation.
  8. Type a forward slash ( /) for division.
  9. Click on cell C3 to add that cell reference to the formula after the forward slash.
  10. Press the ENTER key on the keyboard to complete the formula.
  11. The answer 1 should appear in cell B2.
  12. When you click on cell B2 the complete formula = (C1 + C2) / C3 appears in the formula bar above the worksheet.

Formula 2 Breakdown

The formula in cell B2 uses brackets to change the order of operations. By placing parentheses around the addition operation (C1 + C2) we force Excel to evaluate this operation first.

This first operation in the formula evaluates to 10 + 15 = 25

This number is then divided by the data in cell C3 which is also the number 25. The second operation is therefore 25 / 25 which gives the answer of 1 in cell B2.

Format
mla apa chicago
Your Citation
French, Ted. "Changing the Order of Operations in Excel Formulas." ThoughtCo, May. 29, 2017, thoughtco.com/changing-the-order-of-operations-excel-3123735. French, Ted. (2017, May 29). Changing the Order of Operations in Excel Formulas. Retrieved from https://www.thoughtco.com/changing-the-order-of-operations-excel-3123735 French, Ted. "Changing the Order of Operations in Excel Formulas." ThoughtCo. https://www.thoughtco.com/changing-the-order-of-operations-excel-3123735 (accessed January 18, 2018).