Excel 2011 For Mac Manual Formula Calculations

  1. Excel 2011 For Mac Manual Formula Calculations Calculator
  2. Excel 2011 For Mac Manual Formula Calculations Free
  3. Excel 2011 For Mac Manual Formula Calculations Pdf

September 1, 2012 PV VALUE® USER MANUAL V. SUMMARY OF VERSION 1.1 UPDATES Mac Excel® 2011 The main update for version 1.1 was to re-do the spreadsheet and code to allow for use on a Mac running Excel® 2011. Because of these changes, this version can be used interchangeably between a PC with Excel® 2007 and 2010. Enter a formula. Applies To: Excel for Office 365 for Mac Excel 2016 for Mac Excel for Mac 2011 Excel 2019 for Mac More. Formulas are equations that perform calculations on values in your sheet. All formulas begin with an equal sign (=). You can create a simple formula by using constant and calculation operator.

When a formula is entered correctly and the data used in the formula changes, Excel automatically recalculates and updates the answer.

This tutorial covers in detail how to create and use formulas and includes a step-by-step example of a basic Excel formula. It also includes a complex formula example that relies on Excel's order of operations to calculate the correct answer.

Note The instructions in this article apply to Excel 2019, 2016, 2013 and 2010; Excel Online; Excel 2019 for Mac, Excel 2016 for Mac, and Excel 2011 for Mac.

Excel Formula Basics

Writing a spreadsheet formula is a little different than writing one in math class.

Always Start With the Equal Sign

The most notable difference is that Excel formulas start with the equal sign instead of ending with it.

Excel formulas look like this:
=3 + 2
instead of:
3 + 2 =

Excel 2011 For Mac Manual Formula Calculations

The equal sign (=) always goes in the cell where you want the formula's answer to appear. The equal sign indicates that what follows is part of a formula and not just a name or a number.

Once a formula is entered, the cell containing the formula shows the answer, rather than the formula. To see the formula, select the cell that contains the answer and the formula appears in the formula bar located above the worksheet.

While this simple example works, it has one major drawback. If you need to change the data used in the formula, you need to edit or rewrite the formula.

Improve Formulas with Cell References

It's better to write a formula so that the data can be changed without having to change the formula itself. This is done by entering the data in worksheet cells and then selecting the cells that contain the data to be used in the formula.

This way, if the formula's data needs to change, the change is made by altering the data in the worksheet cells, rather than altering the formula itself.

To tell Excel which cells contain the data you want to use, each cell has an address or cell reference.

About Cell References

  • A cell is the intersection point between a vertical column and a horizontal row on the worksheet.
  • Each column is identified by a letter at the top of the column, such as A, B, and C.
  • Each row is identified by a number located at the left edge of the row, such as 1, 2, and 3.
  • A cell reference is a combination of the column letter and row number that intersect at a cell's location, such as A1, B2, C3, and W345.
  • When writing cell references, the column letter always comes first.

To find a cell reference, look up to see which column the cell is in and then look to the left to find which row it is in.

The current cell (the reference of the selected cell) is also displayed in the Name Box located above column A in the worksheet.

Enter the data into cells C1 and C2 and write this formula instead:

Excel Basic Formula Example

Before you start building formulas, enter all data into the worksheet first. This makes it easy to tell which cell references need to be included in the formula.

Entering data in a worksheet cell is a two-step process:

  1. Type the data in the cell.
  2. Press Enter or select another cell to complete the entry.
Excel 2011 For Mac Manual Formula Calculations

Tutorial Steps

To enter data as illustrated in the example accompanying this section:

  1. Select cell C1 to make it the active cell.
  2. Type a 3 in the cell and press Enter.
  3. Select cell C2.
  4. Type a 2 in the cell and press Enter.

Enter the Formula

  1. Select cell D1, which is the location where the results of the formula will appear.
  2. Type the following formula into cell D1:
  3. Press Enter to complete the formula.
  4. The answer 5 appears in cell D1.
  5. If you select cell D1 again, the complete formula =C1+C2 appears in the formula bar above the worksheet.

Typing the cell references as part of a formula is a valid way of entering them, as proven by the answer of 5 in cell D1. But, there is another way to do it.

Enter Cell References With Pointing

The best way to enter the cell references in a formula is to use pointing. Pointing involves selecting cells to enter their cell reference in the formula. The main advantage of using pointing is that it helps eliminate possible errors caused by typing in the wrong cell reference.

In this step of the tutorial, you'll select cells to enter the cell references for the formula in cell D2.

  1. Select cell D2 to make it the active cell.
  2. Type the equal sign ( = ) in cell D2 to start the formula.
  3. Select cell C1 to enter the cell reference in the formula.
  4. Type a plus sign ( + ).
  5. Select cell C2 to enter the second cell reference into the formula.
  6. Press Enter to complete the formula.

Update the Formula

To test the value of using cell references in an Excel formula, change the data in cell C1 from 3 to 6 and press Enter.

The answers in both cells D1 and D2 automatically change from 5 to 8, but the formulas in both remain unchanged.

Excel 2011 for mac manual formula calculations pdf

Mathematical Operators and the Order of Operations

Creating formulas in Microsoft Excel is not difficult. It is just a matter of combining, in the right order, the cell references of your data with the correct mathematical operator.

Mathematical Operators

The mathematical operators used in Excel formulas are similar to the ones used in math class.

  • Subtraction – minus sign ( - )
  • Addition – plus sign ( + )
  • Division – forward slash ( / )
  • Multiplication – asterisk ( * )
  • Exponentiation – caret ( ^ )

Order of Operations

If more than one operator is used in a formula, Excel follows a specific order to perform the mathematical operations.

This order of operations can be changed by adding brackets to the equation. An easy way to remember the order of operations is to use the acronym BEDMAS.

  • Brackets
  • Exponents
  • Division
  • Multiplication
  • Addition
  • Subtraction

How the Order of Operations Works

Any operation(s) contained in brackets is carried out first, followed by any exponents.

After that, Excel considers division or multiplication operations to be of equal importance. These operations are carried out in the order they occur, from left to right in the equation.

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 carried out first.

Use Multiple Operators in Excel Formulas

This second formula example requires Excel to use its order of operations to calculate the answer.

Enter the Data

Open a blank worksheet and enter the data shown in cells C1 to C5 in the image above.

A More Complex Excel Formula

Use pointing along with the correct brackets and mathematical operators to enter the following formula into cell D1:

Press Enter when you're finished and the answer -4 appears in cell D1.

Detailed Steps for Entering the Formula

Excel 2011 For Mac Manual Formula Calculations Calculator

If you need help, use these steps to enter the formula:

  1. Select cell D1 to make it the active cell.
  2. Type the equal sign ( = ) into cell D1.
  3. Type a round open bracket ( ( ) after the equal sign.
  4. Select cell C2 to enter the cell reference in the formula.
  5. Type the minus sign ( - ) after C2.
  6. Select cell C4 to enter this cell reference into the formula.
  7. Type a round closing bracket ( ) ) after C4.
  8. Type the multiplication sign ( * ) after the closing round bracket.
  9. Select cell C1 to enter this cell reference into the formula.
  10. Type the plus sign ( + ) after C1.
  11. Select cell C3 to enter this cell reference into the formula.
  12. Type the division sign ( / ) after C3.
  13. Select cell C5 to enter this cell reference into the formula.
  14. Press Enter to complete the formula.
  15. The answer -4 appears in cell D1.
  16. If you select cell D1 again, the complete function =(C2-C4)*C1+C3/C5 appears in the formula bar above the worksheet.

How Excel Calculates the Formula Answer

Excel arrives at the answer of -4 for the formula above using the BEDMAS rules to carry out the various mathematical operations in the following order:

  1. Excel first carries out the subtraction operation (C2-C4) or (5-6), since it is surrounded by brackets and gets the result of -1.
  2. Excel multiplies the -1 by 7 (the content of cell C1) to get an answer of -7.
  3. Excel skips ahead to divide 9/3 (the content of cells C3 and C5) because division comes before addition in BEDMAS to get a result of 3.
  4. The last operation that needs to be carried out is to add -7 + 3 to get an answer for the entire formula of -4.

If you want to add up a column or row of numbers, Excel has a built-in formula called the SUM function that makes the job quick and easy.

Instead of reaching for your calculator, use Excel to do the math!

On a sheet, you can enter simple formulas to add, subtract, multiply, and divide two or more numeric values. Once you have created a formula, you can fill it into adjacent cells — no need to create the same formula over and over again. You can also enter a formula that uses the SUM function to quickly total a series of values without having to enter any of them manually in a formula.

Do any of the following:

Use a simple formula to add, subtract, multiply, or divide numeric values

To create a simple formula, you enter values and math operators into a cell, or the formula bar, to receive a result. Instead of entering values directly into the formula, you can also refer to the cells that contain the values that you want to calculate. Using a cell reference in a formula ensures that the result is updated if the values change.

  1. Type a couple of values into cells.

    For example, in cell A1, type 5, and in cell B1, type 20.

  2. Click any blank cell, and then type an equal sign (=) to start a formula.

  3. After the equal sign (=), you can type two numbers and a math operator to create a simple formula.

    For example, you could simply type =5+20, or =5*20. But to create a formula that you would not have to change, even if you change one of the values, type the cell reference and a math operator. For example, A1 + B1.

  4. After you have tried the formula with a plus sign (+), type a minus sign (-) to subtract values, an asterisk (*) to multiply values, and a forward slash (/) to divide values.

    If you use the example numbers, the results are 25, -15, 100, and 0.25

    Note: You can use variations on this formula for all basic math calculations in Excel.

Add a formula to adjacent cells

Excel 2011 For Mac Manual Formula Calculations Free

After you create a formula, you can easily drag it into adjacent cells, either in a row or column. Once you do, the formula automatically adjusts to calculate the values in the corresponding row or column.

  1. Click a cell that contains a formula.

    The cell outline shows a square in the lower-right corner, called the fill handle.

  2. Drag the fill handle to an adjacent cell.

    The formula is copied there, and automatically adjusts the cell references.

Use the SUM function to total numeric values in a column or row

To calculate the total of a series of numeric values in a row or column, you do not have to enter all those values manually into a formula. Instead you can use a predefined formula that uses the SUM function.

Excel 2011 For Mac Manual Formula Calculations Pdf

  1. On a sheet, type three numbers in a row.

    For example, in cell A1, type 5, in cell B1, type 20 and in cell C1, type 8.

  2. Click the empty cell to the right of the values you typed in.

    For example, click cell D1.

  3. On the Formulas tab, under Function, click AutoSum .

    Excel outlines the cells that will be included in the result.

  4. Press RETURN .

    If you use the numbers that are given in the first step, the result is 33.