Microsoft Excel: Working with Formulas

CSC 110: Introduction to Computers

Learning Unit 7: Mini-Lecture

Microsoft Excel: Working with Formulas

Understanding formulas can be a little daunting, especially for those who aren't comfortable with math. The good news is that Microsoft Excel does a lot of the work for you. It has a large menu of specific functions which make math calculations easy. However, you do need to understand basic math principles in order to set up the formulas correctly.

Formula Structure: The first thing you need to remember is that all Excel formulas begin with an equal sign (=). For example, if you want to enter a formula for 4 plus 5, you would type this: =4+5

The equal sign tells Excel that this is going to be a mathematical calculation, rather than just a regular text or numeric entry.

Referencing Cells: Although you can type the numbers you want to calculate (we call them "values") into your formula, it is often better to use the cell reference feature instead. For example, if 4 is in cell A2 and 5 is in cell A3, and you want cell A4 to add 4 and 5, then, instead of typing =4+5 in A4, you would use Excel's cell reference feature. So, if you wanted to add cells A2 and A3, and put the sum in cell A4, you would start by clicking on cell A4. Next, type the equal sign, then click cell A2, which contains the value 4. Next, type the mathematical operator (in this case, the plus sign for addition). Then, click cell A3, which contains the value 5. Press the "Enter" key on your keyboard. Then, a 9 will appear in cell A4, but the formula =A2+A3 will appear in the formula bar, when you click cell A4.

Why is this important? Because your formula will automatically recalculate if you change the value within cells A2 or A3. If you merely type the value into the formula, and later that value changes, you will have to recreate the formula. But if you reference the cells, it doesn't matter whether the values change, the formula will stay the same and automatically recalculate based on the new values. So, if you change A2 to 3 and A3 to 7, A4 will automatically recalculate to 10. This works for both simple and complex calculations. Imagine how much time this can save, if you have many calculations to do!

Mathematical Operators: The following table is from Microsoft Office 2010 Simplified, by Kate Shoup.

Let's take a few minutes to review the mathematical operators below.

Operator + * / % ^

Operation Addition Subtraction Multiplication Division Percentage Exponentiation

Operator = < < > >

Operation Equal to Less than Less than or equal to Greater than Greater than or equal to Not equal to

Page 1 of 2

CSC 110: Introduction to Computers

Learning Unit 7: Mini-Lecture

Operator Precedence: When you are creating a complex formula, meaning a formula that contains more than one type of calculation, the order in which Excel performs these calculations determines the result. You may recall from prior math courses the following acronym for remembering the order in which calculations are performed, "Please Excuse My Dear Aunt Sally" which stands for: Parentheses, Exponents, Multiplication, Division, Addition, and Subtraction.

Let's look at an example of how, when creating equations, the order of operations determines the result:

Let's calculate the average of cells A1, B1, and C1, having the result appear in cell D1.

First, you may recall that to calculate the average (or arithmetic mean) of a group of numbers, we add each of the values together and divide by the total number of values. Thus, the average of the values 5, 6, and 10 is 7, because 5+6+10=21 and 21 divided by 3 is 7.

Using the cell reference feature, explained earlier, you can enter the formula into cell D1.

You might thing that the correct formula would be =A1+B1+C1/3

However, that would be incorrect. Using this formula, Excel would first divide the value C1 by 3 and then add the values of A1 and B1 to the result. So, if A1 is 5, B1 is 6, and C1 is 10, then Excel would divide 10 by 3, which would be approximately 3.3, and add 5 and 6, resulting in an answer of 14.3, which we know to be incorrect.

Instead, we need to use parentheses to construct the correct formula. To determine the average, we need the formula to add all of the values together first and then divide the result by the total number of entries.

Correct Formula: =(A1+B1+C1)/3

Using this formula, Excel would first add the values in parentheses, then divide that total by 3. So, using our earlier values, (5+6+10)/3 or 21/3, which equals 7.

Conclusion As you can see, understanding how to reference cells and properly use mathematical operators is essential to constructing effective and powerful time-saving formulas in Microsoft Excel.

? Indian Hills Community College

Page 2 of 2

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download