CHAPTER 6 AUTOMATING CALCULATIONS WITH FORMULAS - Entering Formulas

Nov 1, 2026

Uploaded by Ivy

This chapter explains how Microsoft Excel performs calculations and how to correctly construct formulas. It covers operator precedence in the order of operations, including using parentheses to control calculation results, and demonstrates common pitfalls that lead to wrong averages. The chapter then shows two formula entry methods—point-and-click and manual typing—plus guidance for accepting formulas, viewing formulas during error checking, and interpreting formula errors.

LanguageEnglish

CategoryTechnology

FormatPDF

Pages20

Reading Time50 min

AccessFree to download

Learning the Order of Operations

Entering Formulas

Using Point-and-Click Entry

Typing Formulas Manually

Displaying Formulas

In this chapter • Understanding the order in which Excel performs calculations. • Entering and editing formulas using the point-and-click technique. • Determining totals quickly with AutoSum. • Inserting functions to perform complex calculations. • Troubleshooting calculations in formulas. • Playing “What If?” with formulas and functions by experimenting with values. 6 Automating Calculations with Formulas and Functions Although an Excel worksheet is ideal for arranging entries in columns and rows, that’s not its main purpose. You can do that with a table in a word processing application. What makes a worksheet so powerful is that it can perform calculations using various values from the worksheet and values you supply. In addition, you can set up unlimited scenarios for your worksheet that supply different numbers in the calculations, enabling you to play “What if…?” with various sets of numbers! In this chapter, you learn how to unleash the power of Excel’s formulas, functions, and other calculation tools. 108 ABSOLUTE BEGINNER’S GUIDE TO MICROSOFT EXCEL 2003 Learning the Order of Operations One of your grade school math teachers probably introduced you to the order of operations, which governs the order in which a series of mathematical equations is performed. Excel follows this standard order of operations, as well. In every formula, Excel performs the equations from left to right in the following order, which gives some operators precedence over others: 1st All operations in parentheses 2nd Exponential equations or operations 3rd Multiplication and division 4th Addition and subtraction Keeping precedence in mind is important when you are entering formulas because the order of operations determines the result. For example, if you wanted to determine the average of the values in cells A1, B1, and C1, and you entered =A1+B1+C1/3, Excel would probably generate the wrong answer. Rather than total the three values and then divide by three, as you might expect, Excel first divides the value in C1 by three and then adds that result to A1+B1 . It calculates this way because division takes precedence over addition. So, how do you correctly determine this average? You must enclose in parentheses the operations you want performed first. In this example, you need to total A1 to C1 first. To do that, enclose the cell addresses in parentheses: =(A1+B1+C1)/3 . This tells Excel to total the values first and then divide the total by three. Entering Formulas As you learned in Chapter 1,“Understanding Excel Worksheets (Spreadsheets),” every formula begins with an equal sign (=) and can include cell references, mathematical symbols (such as + and –), and actual numbers. You enter a formula in the cells in which you want the formula’s results displayed. Excel gives you two options for entering formulas: You can use the point-and-click technique or type formulas. These techniques are described in the following sections. Using Point-and-Click Entry The easiest and most foolproof way to enter a formula is to use the point-and-click approach. You simply start your formula with an equal sign, click cells to add their CHAPTER 6 AUTOMATING CALCULATIONS WITH FORMULAS 109 AND FUNCTIONS addresses to the equation, and type the required mathematical sym- bols. The following instructions lead you step-by-step through the process: 1. Select the cell in which you want the formula’s result to appear. 2. Type the equal sign (=) . 3. Click the cell whose address you want to appear first in the formula. The cell address appears in the formula bar. 4. Type a mathematical operator after the value to indicate the next operation you want to perform. For example, type + to add the next entry,– to subtract, * to multiply, or / to divide by. 5. Continue clicking cells and typing operators until you finish entering the formula, as shown in Figure 6.1. (Remember to group operations using parentheses, if

FAQs

How does Excel determine the order of calculations in a formula?

Excel evaluates operations in a standard precedence order: parentheses first, then exponential operations, then multiplication/division, and finally addition/subtraction.

Why would =A1+B1+C1/3 produce an incorrect average?

Because division has higher precedence than addition, Excel divides C1 by 3 first and then adds the result to A1 and B1 instead of totaling A1 to C1 before dividing.

What are the two main ways to enter formulas in Excel?

You can enter formulas using the point-and-click technique or by typing the formula manually starting with an equal sign, using cell addresses and operators as needed.

Nov 1, 2026

Uploaded by Ivy