Lesson 3: Numbers and Mathematical Calculations In this lesson you will learn how to work with numbers and how to perform mathematical calculations. To begin, open Microsoft Excel. Setting the Enter Key Direction In Microsoft Excel, you can specify which direction the cursor moves when you press the Enter key. You can have the cursor move up, down, left, right, or not at all. Let’s make sure the cursor is set to move down when you press the Enter key. Making Numeric EntriesIn Microsoft Excel, you can enter numbers and mathematical formulas into cells. When a number is entered into a cell, you can perform mathematical calculations such as addition, subtraction, multiplication, and division. When entering a mathematical formula, precede the formula with an equals sign. Use the following to indicate the type of calculation you wish to perform: + Addition - Subtraction * Multiplication / Division ^ Exponential Moving Quickly Around the Worksheet The following are shortcuts for moving quickly from one cell to a cell in a different part of the worksheet. Go to –F5 The F5 function key is the "Go To" key. If you press the F5 key while in the Ready mode, you will be prompted for the cell you wish to go to. Enter the cell address, and the cursor will jump to that cell.
You can also use Ctrl-G to go to a specific cell.
Performing Mathematical Calculations The following exercises demonstrate how to perform mathematical calculations. Addition
Place the cursor in cell A3 and look at the Formula bar. Subtraction
Place the cursor in cell B3 and look at the Formula bar. Multiplication
Place the cursor in cell C3 and look at the Formula bar. Division
Place the cursor in cell D3 and look at the Formula bar. Automatic Calculation If you have automatic calculation turned on, Microsoft Excel recalculates the worksheet as you change cell entries. Let’s check to make sure automatic calculation is turned on. Setting Automatic Calculation
Make the changes outlined below and note how Microsoft Excel automatically recalculates.
You can format the numbers you enter into Microsoft Excel. You can add commas to separate thousands, specify the number of decimal places, place a dollar sign in front of the number, or display the number as a percent in addition to several other options. Before formatting. After formatting.
Alternate Method – Formatting Numbers by Using the Toolbar
More Advanced Mathematical Calculations When you perform mathematical calculations in Microsoft Excel be careful of precedence. Calculations are performed from left to right, with multiplication and division performed before addition and subtraction.
To change the order of calculation, use parentheses. Microsoft Excel will calculate the information in parentheses first.
Cell Addressing Microsoft Excel records cell addresses in formulas in three different ways, called absolute, relative, and mixed. The way a formula is recorded is important when you copy it. With relative cell addressing, when you copy a formula from one area of the worksheet to another, Microsoft Excel records the position of the cell relative to the cell that originally contained the formula. The following exercises demonstrate: Creating the Formula
You can copy entries from one cell to another cell. To copy the formula you just entered, follow the steps outlined below:
Before proceeding with the next exercise, we must copy the information in cells A7 to B9 to cells C7 to D9. This time we will copy by using the Formatting toolbar. Copying by Using the Formatting Toolbar
An absolute cell address refers to the same cell, no matter where you copy the formula. You make a cell address an absolute cell address by placing a dollar sign in front of both the row and column identifiers. You can do this automatically by using the F4 key. To illustrate:
Now copy the formula from C10 to D10. This time, copy by using the keyboard shortcut.
Mixed Cell Addressing You use mixed cell addressing to reference a cell that is part absolute and part relative. You can use the F4 key.
Reference operators are helpful when referring to a cell or group of cells. Two types of reference operator are range and union. A range reference refers to all the cells between and including the reference. A range reference consists of two cell addresses separated by a colon. The reference A1:A3 includes cells A1, A2, and A3. The reference A1:C3 includes A1, A2, A3, B1, B2, B3, C1, C2, and C3. A union reference includes two or more references. A union reference consists of two or more cell addresses separated by a comma. The reference A7,B8,C9 refers to cells A7, B8, and C9. Functions Microsoft Excel has a set of prewritten formulas called functions. Functions differ from regular formulas in that you supply the value but not the operators, such as +, -, *, or /. The SUM function is used to calculate sums. When using a function, remember the following: Use an equals sign to begin a formulaSpecify the function name Enclose arguments within parentheses Use a comma to separate arguments Here is an example of a function: =SUM(2,13,10,67)In this function: The equals sign begins the functionSUM is the name of the function 2, 13, 10 and 67 are the arguments Parentheses enclose the arguments A comma separates each of the arguments The SUM function adds the arguments together. In the exercises that follow, we will look at various functions.Typing a Function
You can use the AVERAGE function to calculate an average from a series of numbers.
You can use the MIN function to find the lowest number in a series of numbers.
You can use the MAX function to find the highest number in a series of numbers.
This is the end of Lesson Three. Save your file and close Microsoft Excel.
|