Spreadsheets- Advanced Calculations

Complicated Formulas

Order of Evaluations

Sometimes you may want to create a formula in which you will use more than one type of operation. To calculate formulas with multiple mathematical operations, Excel follows standard algebraic rules for orders of operation. In Excel these rules are called the order of evaluation. The table below demonstrates Excel's standard order of evaluation.

If more than one operation exists on the same level category, Excel evaluates from left to right. For example, to calculate the formula =(8+3)+9-3*2, Excel use the following steps:

1. Parentheses               =(8+3)+9-3*2
2. Multiplication/Division   =11+9-3*2
3. Addition/Subtraction      =11+9-6
                             =14

As you saw in the table above, parentheses come first in the order of evaluations. You can override Excel's standard order of evaluations by using parentheses to group the operations you want completed first. Notice the difference in the results of the following formulas:

=5+6*0.5    Result: 8
=(5+6)*0.5  Result: 5.5

Parentheses can also be used to enforce the order of evaluation to ensure that the result you get is the one you want. Compare the following formulas. Both have the same result; however, it is easier to decipher the order of evaluations in the first because of the parentheses.

=((5*4)/2+(10/2))  Result: 15
=5*4/2+10/2        Result: 15

You can add as many parentheses, so long as you do so in matching pairs. That is, for ever "(" you must also have a ")" and visa versa. Excel will color code the parentheses to help you determine which are closed and which are not. Also, if you do not close the parentheses, Excel will display an error and propose a correction for it. Notice what happens in the following example where the parentheses are not closed:

1. The equation =(9+(6*2) has been enter in cell D2. Notice that the open parenthesis preceding the 9 does not have a closed parenthesis to match it.

   

2. After pressing enter the following error message appears.
   Notice that Excel proposes a correction in which all the parenthesis in the formula are closed.

   

3. If Yes is selected, Excel will automatically correct the formula.
4. If No is selected, Excel will make you aware that the parentheses in the formula are not closed and that they need to be corrected.

   

You can group formulas with cell references in the same manner that you group standard formulas. The formula will be calculated following Excel's order of evaluations.

1. Follow the same procedure for using cell references in formulas, inserting parenthese as needed to modify the order of evaluations.
2. Make sure that you close all open parentheses in your formula before pressing Enter.
3. Excel will evaluate the formula accordingly.