Microsoft Excel Version 2002 Step by Stepby Curtis Frye D.
Experience learning made easy—and quickly teach yourself how to use the data-presentation and number-crunching power in Microsoft® Excel Version 2002. With STEP BY STEP, you can take just the lessons you need or work from cover to cover. Either way, you drive the instruction—building and practicing the skills you need, just when you need
Experience learning made easy—and quickly teach yourself how to use the data-presentation and number-crunching power in Microsoft® Excel Version 2002. With STEP BY STEP, you can take just the lessons you need or work from cover to cover. Either way, you drive the instruction—building and practicing the skills you need, just when you need them!
- Master the tools for organizing, processing, and presenting data in Excel
- Make data come alive with PivotTable® and PivotChart® reports
- Use data-analysis tools and techniques for better decision-making
- Tap external data sources—including other Microsoft Office applications—and add hyperlinks
- Create your own macros
- Help keep data secure as you share—and collaborate on—Excel documents over the Web
- Prepare for the Microsoft Office User Specialist (MOS) exam
This book is approved courseware for the Microsoft Office Specialist Program.
Go to: http://www.microsoft.com/learning/mcp/officespecialist/default.mspx
Read an Excerpt
Chapter 3: Performing Calculations on Data
- Naming Groups of Data
- Creating Formulas to Calculate Values
- Finding and Correcting Errors in Calculations
After completing this chapter, you will be able to:
- Name groups of cells.
- Create formulas to calculate values.
- Find and correct errors in calculations.
Microsoft Excel workbooks give you a handy place to store and organize your data, but you can do a lot more with your data in Excel. One important task you can perform in Excel is to calculate totals for the values in a series of related cells. You can also have Excel find out other things about the data you select, such as the maximum or minimum value in a group of cells. Finding the maximum or minimum value in a group can let you identify your best salesperson, product categories you might need to pay more attention to, or suppliers that consistently give you the best deal. Regardless of your bookkeeping needs, Excel gives you the ability to find the information you want. And if you should make an error, you can find the cause and correct it quickly.
Many times you can't access the information you want without referencing more than one cell, and it's also often true that you'll use the data in the same group of cells for more than one calculation. Excel makes it easy to reference a number of cells at once, letting you define your calculations quickly.
In this chapter, you'll learn how to streamline references to groups of data on your worksheets and how to create and correct formulas that summarize the sales and product data from The Garden Company.
This chapter uses the practice files NameRange, Formula, and FindErrors that you installed from this book's CD-ROM. For details about installing the practice files, see "Using the Book's CD-ROM" at the beginning of this book.
Naming Groups of DataWhen you work with large amounts of data, it's easier to identify groups of cells that contain related data. In the following graphic, for example, cells C2 through C6 hold the prices of items from a customer's order.
Rather than specify the cells individually every time you want to use the data they contain, you can define those cells as a range (also called a named range). For instance, you could group the items from the previous graphic into a range named OrderItems1. Whenever you wanted to use the contents of that range in a calculation, you could just use the name of the range instead of specifying each cell individually.
There are a number of ways to create a named range, two of which you can access through the Insert menu. The first method works well if you have a column of data with a label at the head of the column, as in the following graphic.
In this case, you access the Create Name dialog box by pointing to Name on the Insert menu and clicking Create. In the Create Name dialog box, you can define a named range by having Excel use the label in the top cell as the range's name. You can also create and delete named ranges through the Define Name dialog box, which you access by pointing to Define on the Insert menu and clicking Name.
A final way to create a named range is to select the cells you want in the range, click in the Name box next to the formula bar, and then type the name for the range. You can display the ranges available in a workbook by clicking the Name box's down arrow.
Every range in a workbook must have a unique name. Assigning the name of an existing range to a new range removes the original reference, likely affecting how your worksheet behaves.
In this exercise, you will create named ranges to streamline references to groups of cells.
- On the Standard toolbar, click the Open button.
- Navigate to the PerformingCalculations folder, and double-click the NameRange.xls file.
- If necessary, click the Tools sheet tab.
- Click cell C3 and drag to cell C18.
- On the Insert menu, point to Name, and then click Create.
- If necessary, select the Top row check box.
- Click OK.
- In the lower left corner of the workbook window, click the Supplies sheet tab.
- Click cell C4 and drag to cell C29.
- On the Insert menu, point to Name, and then click Define.
- In the Names in workbook box, type SuppliesPrice and then click OK.
- In the lower left corner of the workbook window, click the Furniture sheet tab.
- Click cell C4 and drag to cell C18.
- Click in the Name box.
- Type FurniturePrice, and press Enter.
- On the Insert menu, point to Name, and then click Define.
- In the lower pane of the Define Name dialog box, click Price.
- In the Names in workbook box, delete Price, type ToolsPrice, and then click OK.
- On the Standard toolbar, click the Save button.
- Click the Close button.
The Open dialog box appears.
The selected cells are highlighted.
The Create Names dialog box appears.
Excel assigns the name Price to the cell range.
The Supplies worksheet appears.
The Define Name dialog box appears.
Excel assigns the name SuppliesPrice to the cell range, and the Define Name dialog box disappears.
The Furniture worksheet appears.
The contents of the Name box are highlighted.
Excel assigns the name FurniturePrice to the cell range.
The Define Name dialog box appears.
Price appears in the Names in workbook box.
The Define Name dialog box disappears.
Creating Formulas to Calculate ValuesOnce you've added your data to a worksheet and defined ranges to simplify data references, you can create a formula, or an expression that performs calculations on your data. For example, you can calculate the total cost of a customer's order, figure the average sales for all Wednesdays in the month of January, or find the highest and lowest daily sales for a week, month, or year.
To write an Excel formula, you begin the cell's contents with an equal signwhen Excel sees it, it knows that the expression following it should be interpreted as a calculation and not text. After the equal sign, you type the formula. For instance, you can find the sum of the numbers in cells C2 and C3 with the formula =C2+C3. After you have entered a formula into a cell, you can revise it by clicking the cell and then editing the formula on the formula bar. For example, you can change the preceding formula to =C3-C2, which calculates the difference of the contents of cells C2 and C3.
If Excel treats your formula as text, make sure you haven't accidentally put a space before the equal sign. Remember, the equal sign must be the first character!
Typing the cell references for 15 or 20 cells in a calculation would be tedious, but Excel makes it easy to handle complex calculations. To create a new calculation, you click Function on the Insert menu. The Insert Function dialog box appears, with a list of functions, or predefined formulas, from which you can choose.
The most useful functions in the list are described in the following table.
|SUM||Returns the sum of the numbers in the specified cells|
|AVERAGE||Finds the average of the numbers in the specified cells|
|COUNT||Finds the number of entries in the specified cells|
|MAX||Finds the largest value in the specified cells|
|MIN||Finds the smallest value in the specified cells|
Two other functions you might use are the NOW() and PMT() functions. The NOW() function returns the time the workbook was last opened, so the value will change every time the workbook is opened. The proper form for this function is =NOW(); to update the value to the current date and time, just save your work, close the workbook, and then reopen it. The PMT() function is a bit more complex. It calculates payments due on a loan, assuming a constant interest rate and constant payments. To perform its calculations, the PMT() function requires an interest rate, the number of months of payments, and the starting balance. The elements to be entered into the function are called arguments and must be entered in a certain order. That order is written PMT(rate, nper, pv, fv, type). The following table summarizes the arguments in the PMT() function.
|rate||The interest rate, to be divided by 12 for a loan with monthly payments|
|nper||The total number of payments for the loan|
|pv||The amount loaned (pv is short for present value, or principal)|
|fv||The amount to be left over at the end of the payment cycle (usually left blank, which indicates 0)|
|type||0 or 1, indicating whether payments are made at the beginning or at the end of the month (usually left blank, which indicates 0, or the end of the month)|
If you wanted to borrow $20,000 at an 8 percent interest rate and pay the loan back over 24 months, you could use the PMT() function to figure out the monthly payments. In this case, the function would be written =PMT(8%/12, 24, 20000), which calculates a monthly payment of $904.55.
You can also add the names of any ranges you've defined to a formula. For example, if the named range Order1 refers to cells C2 through C6, you can calculate the average of cells C2 through C6 with the formula =AVERAGE(Order1). If you want to include a series of contiguous cells in a formula but you haven't defined the cells as a named range, you can click the first cell in the range and drag to the last cell. If the cells aren't contiguous, hold down the Ctrl key and click the cells to be included. In both cases, when you release the mouse button, the references of the cells you selected appear in the formula.
Another use for formulas is to display messages when certain conditions are met. For instance, Catherine Turner, the owner of The Garden Company, might provide a free copy of a gardening magazine to customers making purchases worth more than $150. This kind of formula is called a conditional formula and uses the IF function. To create a conditional formula, you click the cell to hold the formula and open the Insert Function dialog box. From within the dialog box, you select IF from the list of available functions and then click OK. The Function Arguments dialog box appears.
When you work with an IF function, the Function Arguments dialog box will have three boxes: Logical_test, Value_if_true, and Value_if_false. The Logical_test box holds the condition you want to check. To check whether the total for an order is greater than $150, the expression would be SUM(Order1)>150.
Now you need to have Excel display messages indicating whether the customer should receive a free magazine. To have Excel print a message from an IF function, you enclose the message in quotes in the Value_if_true or Value_if_false box. In this case, you would type "Qualifies for a free magazine!" in the Value_if_true box and "Thanks for your order!" in the Value_if_false box.
Once you've created a formula, you can copy it and paste it into another cell. When you do, Excel will try to change the formula so it works in the new cells. For instance, in the following graphic, cell D8 contains the formula =SUM(C2:C6).
Clicking cell D8, copying the cell's contents, and then pasting the result into cell D16 writes =SUM(C10:C14) into cell D16. Excel has reinterpreted the formula so that it fits the surrounding cells! Excel knows it can reinterpret the cells used in the formula because the formula uses a relative reference, or a reference that can change if the formula is copied to another cell. Relative references are written with just the cell row and column (for example, C14). If you want a cell reference to remain constant when the formula using it is copied to another cell, you can use an absolute reference. To write a cell reference as an absolute reference, you type $ before the row name and the column number. If you wanted the formula in cell D16 to show the sum of values in cells C10 through C14 regardless of the cell into which it is pasted, you would write the formula as =SUM($C$10:$C$14).
If you copy a formula from the formula bar, use absolute references, or use only named ranges in your formula, Excel won't change the cell references when you copy your formula to another cell.
In this exercise, you create a formula to find the total cost of an order, copy that formula to another cell, and then create a formula to find the average cost of items in the order. The cells with the cost of products in this order are stored in the named range OrderItems....
Meet the Author
Curtis Frye is the author of numerous books on Excel and other Office products, most recently the Excel 2007 Pocket Guide. He graduated from Syracuse in 1990 with a degree in political science, and then moved to Washington, DC, where he worked as a defense trade analyst for four years and as the director of sales and marketing for an ISP for one year. He moved to Portland, Oregon, in 1995 to launch his freelance writing career. When Curt's not writing, and often while he is writing, he is a keynote speaker, mentalist, and professional improvisational comedian.
Most Helpful Customer Reviews
See all customer reviews