Excel 2010 Creating Complex Formulas

Author: mety Labels::


Excel 2010

Creating Complex Formulas

Creating Complex Formulas

Lesson 17
Excel is a spreadsheet application that can help you calculate and analyze numerical information for household budgets, company finances, inventory, and more. To do this, you need to understand complex formulas.
In this lesson, you will learn how to write complex formulas in Excel following the order of operations. You will also learn about relative and absolute cell references and how to copy and fill formulas containing cell references.


Complex Formulas

Launch video!Watch the video (4:56). Need help?
Simple formulas have one mathematical operation, such as 5+5.Complex formulas have more than one mathematical operation, such as 5+5-2. When there is more than one operation in a formula, the order of operations tells us which operation to calculate first. In order to use Excel to calculate complex formulas, you will need to understand the order of operations.
Watch the video to learn about the order of operations in complex formulas.

Order of Operations

Excel calculates formulas based on the following order of operations:
  1. Operations enclosed in parentheses
  2. Exponential calculations (to the power of)
  3. Multiplication and division, whichever comes first
  4. Addition and subtraction, whichever comes first
A mnemonic that can help you remember the order is Please Excuse MDear Aunt Sally.
Example 1
The following example demonstrates how to use the order of operations to calculate a formula:
Order of Operations exampleOrder of Operations example
Example 2
In this example, we will review how Excel will calculate a complex formula using the order of operations. The selected cell will display the percent of total Pete Lily seeds sold that were white.
Order of Operations Excel exampleOrder of Operations Excel example
  1. First, Excel will calculate the amount sold in parentheses: (19*1.99)=37.81 White Lily seeds and(33*1.99)=65.67 Total Lily seeds.
  2. Second, it will divide the White Lily seeds amount by the Total Lily seeds amount: 37.81/65.67=.5758.
  3. Last, it will multiply the result by 100 to obtain the value as a percent: .5758*100=57.58.

Based on this complex formula, the result will show that 57.58% of the total Pete Lily seeds sold were white. You can see from this example, that it is important to enter complex formulas with the correct order of operations. Otherwise, Excel will not calculate the results accurately.

To Create a Complex Formula Using the Order of Operations:

In this example, we will use cell references in addition to actual values, to create a complex formula that will add tax to the nursery order.
  1. Click the cell where you want the formula result to appear (for example, F11).
  2. Type the equal sign (=).
  3. Type an open parenthesis, then click on the cell that contains the first value you want in the formula (for example, F4).
  4. Type the first mathematical operator (for example, the addition sign).
  5. Click on the cell that contains the second value you want in the formula (for example, F5), and then type aclosed parenthesis.
  6. Type the next mathematical operator (for example, the multiplication sign).
  7. Type the next value in the formula (for example, 0.055 for 5.5% tax).
    Row 4, Column DRow 4, Column D
  8. Click Enter to calculate your formula. The results show that $2.12 is the tax for the nursery order.
    Result in F11Result in F11

    Working with Cell References

    Launch video!Watch the video (4:38). Need help?
    In order to maintain accurate formulas, it is necessary to understand how cell references respond when you copy or fill them to new cells in the worksheet.
    Excel will interpret cell references as either relative or absolute. By default, cell references are relative references. When copied or filled, they change based on the relative position of rows and columns. If you copy a formula (=A1+B1) into row 2, the formula will change to become (=A2+B2).
    Absolute references, on the other hand, do not change when they are copied or filled and are used when you want the values to stay the same.
    Watch the video to learn how to copy and fill relative and absolute references.

    Relative References

    Relative references can save you time when you are repeating the same kind of calculation across multiple rows or columns.
    In the following example, we are creating a formula with cell references in row 4 to calculate the total cost of the electric bill and water bill for each month (B4=B2+B3). For the upcoming months we want to use the same formula with relative references (C2+C3, D2+D3, E2+E3, etc.) For convenience, we can copy the formula in B4 into the rest of row 4 and Excel will calculate the value of the bills for those months using relative references.

    To Create and Copy a Formula Using Relative References:

    1. Select the first cell where you want to enter the formula (for example, B4).
      Selecting cell B4Selecting cell B4
    2. Enter the formula to calculate the value you want (for example, add B2+B3).
      Entering formula into B4Entering formula into B4
    3. Press Enter. The formula will be calculated.
      Result in B4Result in B4
    4. Select the cell you want to copy (for example, B4) and click on the Copy command from the Home tab.
    5. Select the cells where you want to paste the formula and click on the Paste command from the Home tab. (You may also drag the fill handle to fill cells.)
      Values calculated in C4:M4Values calculated in C4:M4
  9. Your formula is copied to the selected cells as a relative reference (C4=C2+C3, D4=D2+D3, E4=E2+E3, etc.) and the values are calculated.


    Absolute References

    There may be times when you do not want a cell reference to change when copying or filling cells. You can use anabsolute reference to keep a row and/or column constant in the formula.
    An absolute reference is designated in the formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both.
    Absolute Reference Chart

    In the below example, we want to calculate the sales tax for a list of products with varying prices. We will use an absolute reference for the sales tax ($B$1) because we do not want it to change as we are copying the formula down the column of varying prices.

    To Create and Copy a Formula Using an Absolute Reference:

    1. Select the first cell where you want to enter the formula (for example, C4)
      Selecting cell C4Selecting cell C4
    2. Click on the cell that contains the first value you want in the formula (for example, B4).
    3. Type the first mathematical operator (for example, the multiplication sign).
    4. Type the dollar sign ($) and enter the column letter of the cell you are making an absolute reference to (for example, B).
      Entering formulaEntering formula
    5. Type the dollar sign ($) and enter the row number of the same cell you are making an absolute reference to (for example, 1).
      Entering formulaEntering formula
    6. Press Enter to calculate the formula.
      Result in C4Result in C4
    7. Select the cell you want to copy (for example, C4) and click on the Copy command from the Home tab.
    8. Select the cells where you want to paste the formula and click on the Paste command from the Home tab. (You may also drag the fill handle to fill cells.)
      Values calculated in C5:C8Values calculated in C5:C8
    9. Your formula is copied to the selected cells using the absolute reference (C5=B5*$B$1, C6=B6*$B$1, etc.) and your values are calculated.

0 comments |

Labels

Blog Archive

Powered by Blogger.

I made these pages for me and my friends to help solving the problem we face regarding Computer & internet, if anyone wants me to answer a question or find out about some information please send me email and I will try to reply.*P.S. some of the article I wrote and the other I found on the internet I posted them in sprit of learning and shearing, please forgive me if you found something you don’t want to be in my blog, email me and I will delete them. Thank you for your interest in my pages.امل نجم Amal Nagm

banner 1 banner 2