Using Order of Operations

If a formula has more than one type of calculation to be performed, this is done by order of operations or precedence. First, exponents (^) are applied. Second, multiplication (*) or division (/) is performed. Third, addition (+) or subtraction (-) follows. For example, since multiplication takes precedence over addition, the formula =3+4*5 has the value 23. If the formula contains two or more calculations with the same level of precedence to be performed, Excel applies them going from left to right. In the formula =4*10/8, first 4 is multiplied by 10 and then the product is divided by 8 to return the value 5. If parentheses are added, the order of operations is changed. Any calculation contained within parentheses is performed before any other part of the formula. In the formula =(3+4)*5, the addition in parentheses is calculated first and then this total is multiplied by 5 to return the value of 35. Without the parentheses, the total of this formula would be 23.

Download the file Ernie.xls.
Cell D4 has a formula that calculates the sticker price by using dealer and preparation costs multiplied by the markup: =b4+c4*$d$2 (this cell reference is an absolute reference as indicated by the dollar signs; it must be an absolute reference rather than relative—an absolute reference is a cell reference that does not change when you copy the formula to a new location on the workbook).

Order of operations image

To create an absolute reference, you preface the column and row designations with a dollar sign—you can manually enter these or after you type the cell reference hit the F4 function key and it will insert the dollar signs. You can see this formula in the formula bar located right above the spreadsheet.
Check the calculation result yourself by adding $500 to $2500 and add 10% ($300) of that to the result. Compare your results to the sticker price on the worksheet. Something is wrong with the worksheet formula (it should be $3300).
Excel follows the mathematical order of operations when it performs calculations so that multiplication and division are performed first and then addition and subtraction. Here dealer cost in B4 is added to the result of the preparation cost after it is multiplied by the markup first when the dealer and preparation costs are supposed to be added first and then multiplied. Therefore, you need to put parentheses around (B4+C4) so that these cell contents are added before performing the multiplication. The results should be $3,300.

Order of operations image

Now replicate this formula in cells D5 through D11 by going to the right edge of the cell until the cursor changes to a + and then drag down across the range—the results should be $3,245, $2,530, $3,850, $4,400, $385, $1870, $605.

Order of operations image

Then complete the formula for range E4:E11. ($3450, $3,392.50, $2645, $4025, $4600, $402.50, $1955, $632.50).
Go to the next section on the PMT Function.

Click here to return to Course Topics.

		Search:

	E-mail Internet Access Software Assistance Computer Labs Software Licensing Web Publishing Calendaring Computer Security Unix Training

	Using Order of Operations If a formula has more than one type of calculation to be performed, this is done by order of operations or precedence. First, exponents (^) are applied. Second, multiplication () or division (/) is performed. Third, addition (+) or subtraction (-) follows. For example, since multiplication takes precedence over addition, the formula =3+45 has the value 23. If the formula contains two or more calculations with the same level of precedence to be performed, Excel applies them going from left to right. In the formula =410/8, first 4 is multiplied by 10 and then the product is divided by 8 to return the value 5. If parentheses are added, the order of operations is changed. Any calculation contained within parentheses is performed before any other part of the formula. In the formula =(3+4)5, the addition in parentheses is calculated first and then this total is multiplied by 5 to return the value of 35. Without the parentheses, the total of this formula would be 23. Download the file Ernie.xls. Cell D4 has a formula that calculates the sticker price by using dealer and preparation costs multiplied by the markup: =b4+c4$d$2 (this cell reference is an absolute reference as indicated by the dollar signs; it must be an absolute reference rather than relative—an absolute reference is a cell reference that does not change when you copy the formula to a new location on the workbook). To create an absolute reference, you preface the column and row designations with a dollar sign—you can manually enter these or after you type the cell reference hit the F4 function key and it will insert the dollar signs. You can see this formula in the formula bar located right above the spreadsheet. Check the calculation result yourself by adding $500 to $2500 and add 10% ($300) of that to the result. Compare your results to the sticker price on the worksheet. Something is wrong with the worksheet formula (it should be $3300). Excel follows the mathematical order of operations when it performs calculations so that multiplication and division are performed first and then addition and subtraction. Here dealer cost in B4 is added to the result of the preparation cost after it is multiplied by the markup first when the dealer and preparation costs are supposed to be added first and then multiplied. Therefore, you need to put parentheses around (B4+C4) so that these cell contents are added before performing the multiplication. The results should be $3,300. Now replicate this formula in cells D5 through D11 by going to the right edge of the cell until the cursor changes to a + and then drag down across the range—the results should be $3,245, $2,530, $3,850, $4,400, $385, $1870, $605. Then complete the formula for range E4:E11. ($3450, $3,392.50, $2645, $4025, $4600, $402.50, $1955, $632.50). Go to the next section on the PMT Function*. Click here to return to Course Topics.
Copyright © 1998-2004 The University of Alabama \| Disclaimer \| Contact: Help.Desk@ua.edu