BN.com Gift Guide

Microsoft Excel Version 2002 Step by Step

Overview

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 ...
See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (45) from $1.99   
  • New (8) from $1.99   
  • Used (37) from $1.99   
Close
Sort by
Page 1 of 1
Showing All
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$1.99
Seller since 2009

Feedback rating:

(2562)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

New
2001-06-06 Paperback New 073561296X Ships Within 24 Hours. Tracking Number available for all USA orders. Excellent Customer Service. Upto 15 Days 100% Money Back Gurantee. Try ... Our Fast! ! ! ! Shipping With Tracking Number. Read more Show Less

Ships from: Bensalem, PA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$9.50
Seller since 2011

Feedback rating:

(4)

Condition: New
New Condition not used

Ships from: Murphy, TX

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
$12.52
Seller since 2014

Feedback rating:

(490)

Condition: New
PAPERBACK New 073561296X! ! KNOWLEDGE IS POWER! ! ENJOY OUR BEST PRICES! ! ! Ships Fast. All standard orders delivered within 5 to 12 business days.

Ships from: Southampton, PA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$15.00
Seller since 2014

Feedback rating:

(6)

Condition: New
New Clean and crisp.

Ships from: Mount Morris, MI

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$15.11
Seller since 2014

Feedback rating:

(316)

Condition: New
PAPERBACK New 073561296X XCITING PRICES JUST FOR YOU. Ships within 24 hours. Best customer service. 100% money back return policy.

Ships from: Bensalem, PA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$15.11
Seller since 2010

Feedback rating:

(772)

Condition: New
PAPERBACK New 073561296X! ! ! ! BEST PRICES WITH A SERVICE YOU CAN RELY! ! !

Ships from: Philadelphia, PA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$15.19
Seller since 2008

Feedback rating:

(172)

Condition: New
073561296X BRAND NEW NEVER USED IN STOCK 125,000+ HAPPY CUSTOMERS SHIP EVERY DAY WITH FREE TRACKING NUMBER

Ships from: fallbrook, CA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
$29.87
Seller since 2014

Feedback rating:

(321)

Condition: New
Brand New Item.

Ships from: Chatham, NJ

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
Page 1 of 1
Showing All
Close
Sort by
Sending request ...

Overview

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

Quickly teach yourself how to use the data-presentation and number-crunching power in Excel Version 2002. This easy-to-follow book features step-by-step lessons in full color, plus practice files on CD-ROM.

Read More Show Less

Editorial Reviews

Booknews
This instruction book for Microsoft Excel offers help organizing, processing, and presenting data. It also doubles as a study guide for the Microsoft Office User Specialist (MOUS) exam. Sixteen chapters cover topics like setting up a workbook, performing calculations, changing document appearance, data filters, reordering and summarizing data, analyzing alternative data sets, creating charts, printing, automating repetitive tasks, using databases, and publishing information on the Web. Annotation c. Book News, Inc., Portland, OR (booknews.com)
Read More Show Less

Product Details

  • ISBN-13: 9780735612969
  • Publisher: Microsoft Press
  • Publication date: 6/28/2001
  • Series: Step by Step Series
  • Edition description: REV
  • Edition number: 1
  • Pages: 352
  • Product dimensions: 7.40 (w) x 9.26 (h) x 0.92 (d)

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.

Read More Show Less

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 Data

When 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.

(Image unavailable)

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.

(Image unavailable)

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.


IMPORTANT:
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.

  1. On the Standard toolbar, click the Open button.
  2. The Open dialog box appears.

  3. Navigate to the PerformingCalculations folder, and double-click the NameRange.xls file.
  4. NameRange.xls opens.

  5. If necessary, click the Tools sheet tab.
  6. Click cell C3 and drag to cell C18.
  7. The selected cells are highlighted.

  8. On the Insert menu, point to Name, and then click Create.
  9. The Create Names dialog box appears.

    (Image unavailable)

  10. If necessary, select the Top row check box.
  11. Click OK.
  12. Excel assigns the name Price to the cell range.

  13. In the lower left corner of the workbook window, click the Supplies sheet tab.
  14. The Supplies worksheet appears.

  15. Click cell C4 and drag to cell C29.
  16. On the Insert menu, point to Name, and then click Define.
  17. The Define Name dialog box appears.

    (Image unavailable)

  18. In the Names in workbook box, type SuppliesPrice and then click OK.
  19. Excel assigns the name SuppliesPrice to the cell range, and the Define Name dialog box disappears.

  20. In the lower left corner of the workbook window, click the Furniture sheet tab.
  21. The Furniture worksheet appears.

  22. Click cell C4 and drag to cell C18.
  23. Click in the Name box.
  24. The contents of the Name box are highlighted.

  25. Type FurniturePrice, and press Enter.
  26. Excel assigns the name FurniturePrice to the cell range.

  27. On the Insert menu, point to Name, and then click Define.
  28. The Define Name dialog box appears.

  29. In the lower pane of the Define Name dialog box, click Price.
  30. Price appears in the Names in workbook box.

  31. In the Names in workbook box, delete Price, type ToolsPrice, and then click OK.
  32. The Define Name dialog box disappears.

  33. On the Standard toolbar, click the Save button.
  34. Click the Close button.
  35. NameRange.xls closes.

Creating Formulas to Calculate Values

Once 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 sign—when 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.


TROUBLESHOOTING:
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.

(Image unavailable)

The most useful functions in the list are described in the following table.

Item Description
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.

Argument Description
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.

(Image unavailable)

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.

(Image unavailable)

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).

(Image unavailable)

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).


TIP:
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....

Read More Show Less

Table of Contents

What's New in Microsoft Excel 2002
Getting Help
Using the Book's CD-ROM
Conventions and Features
MOUS Objectives
Taking a MOUS Exam
1 Getting to Know Excel
2 Setting Up a Workbook
3 Performing Calculations on Data
4 Changing Document Appearance
5 Focusing on Specific Data Using Filters
6 Combining Data from Multiple Sources
7 Recording and Summarizing Data
8 Analyzing Alternative Data Sets
9 Creating Dynamic Lists with PivotTables
10 Creating Charts
11 Printing
12 Automating Repetitive Tasks with Macros
13 Working with Other Microsoft Office Programs
14 Working with Database Data
15 Publishing Information on the Web
16 Collaborating with Colleagues
Quick Reference
Glossary
Index
Read More Show Less

First Chapter


  • Naming Groups of Data
  • Creating Formulas to Calculate Values
  • Finding and Correcting Errors in Calculations

Chapter 3 Performing Calculations on Data

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 Data

When 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.

(Image unavailable)

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.

(Image unavailable)

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.


IMPORTANT:
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.

  1. On the Standard toolbar, click the Open button.
  2. The Open dialog box appears.

  3. Navigate to the PerformingCalculations folder, and double-click the NameRange.xls file.
  4. NameRange.xls opens.

  5. If necessary, click the Tools sheet tab.
  6. Click cell C3 and drag to cell C18.
  7. The selected cells are highlighted.

  8. On the Insert menu, point to Name, and then click Create.
  9. The Create Names dialog box appears.

    (Image unavailable)

  10. If necessary, select the Top row check box.
  11. Click OK.
  12. Excel assigns the name Price to the cell range.

  13. In the lower left corner of the workbook window, click the Supplies sheet tab.
  14. The Supplies worksheet appears.

  15. Click cell C4 and drag to cell C29.
  16. On the Insert menu, point to Name, and then click Define.
  17. The Define Name dialog box appears.

    (Image unavailable)

  18. In the Names in workbook box, type SuppliesPrice and then click OK.
  19. Excel assigns the name SuppliesPrice to the cell range, and the Define Name dialog box disappears.

  20. In the lower left corner of the workbook window, click the Furniture sheet tab.
  21. The Furniture worksheet appears.

  22. Click cell C4 and drag to cell C18.
  23. Click in the Name box.
  24. The contents of the Name box are highlighted.

  25. Type FurniturePrice, and press Enter.
  26. Excel assigns the name FurniturePrice to the cell range.

  27. On the Insert menu, point to Name, and then click Define.
  28. The Define Name dialog box appears.

  29. In the lower pane of the Define Name dialog box, click Price.
  30. Price appears in the Names in workbook box.

  31. In the Names in workbook box, delete Price, type ToolsPrice, and then click OK.
  32. The Define Name dialog box disappears.

  33. On the Standard toolbar, click the Save button.
  34. Click the Close button.
  35. NameRange.xls closes.

Creating Formulas to Calculate Values

Once 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 sign—when 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.


TROUBLESHOOTING:
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.

(Image unavailable)

The most useful functions in the list are described in the following table.

Item Description
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.

Argument Description
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.

(Image unavailable)

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.

(Image unavailable)

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).

(Image unavailable)

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).


TIP:
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.

  1. On the Standard toolbar, click the Open button.
  2. The Open dialog box appears.

  3. Double-click Formula.xls.
  4. Formula.xls opens.

  5. Click cell D7.
  6. D7 becomes the active cell.

  7. On the formula bar, type =D4+D5 and press Enter.
  8. The value $63.90 appears in cell D7.

    (Image unavailable)

  9. Click cell D7, and then, on the Standard toolbar, click the Copy button.
  10. Excel copies the formula in cell D7 to the Clipboard.

  11. Click cell D8, and then, on the Standard toolbar, click the Paste button.
  12. The value $18.95 appears in cell D8, and =D5+D6 appears in the formula bar.

  13. Press Del.
  14. The formula in cell D8 disappears.

  15. On the Insert menu, click Function.
  16. The Insert Function dialog box appears.

  17. Click AVERAGE, and then click OK.
  18. The Function Arguments dialog box appears, with the contents of the Number 1 box highlighted.

  19. Type OrderItems, and then click OK.
  20. The Function Arguments dialog box disappears, and $31.95 appears in cell D8.

    (Image unavailable)

  21. Click cell C10.
  22. On the Insert menu, click Function.
  23. The Insert Function dialog box appears.

  24. In the Select a function list, click IF and then click OK.
  25. The Function Arguments dialog box appears.

  26. In the Logical_test box, type D7>50.
  27. In the Value_if_true box, type "5% discount".
  28. In the Value_if_false box, type "No discount" and then click OK.
  29. The Function Arguments dialog box disappears, and 5% discount appears in cell C10.

    (Image unavailable)

  30. On the Standard toolbar, click the Save button.
  31. Excel saves your changes.

  32. Click the Close button.
  33. Formula.xls closes.

Finding and Correcting Errors in Calculations

Including calculations in a worksheet gives you valuable answers to questions about your data. As is always true, however, it is possible for errors to creep into your formulas. Excel makes it easy to find the source of errors in your formulas by identifying the cells used in a given calculation and describing any errors that have occurred. The process of examining a worksheet for errors in formulas is referred to as auditing.

Excel identifies errors in several ways. The first way is to fill the cell holding the formula generating the error with an error code. In the following graphic, cell D8 has the error code #NAME?.

(Image unavailable)

When a cell with an erroneous formula is the active cell, an Error button appears next to it. You can click the button’s down arrow to display a menu with options that provide information about the error and offer to help you fix it. The following table lists the most common error codes and what they mean.

Error Code Description
##### The column isn’t wide enough to display the value.
#VALUE! The formula has the wrong type of argument (such as text where a TRUE or FALSE value is required).
#NAME? The formula contains text that Excel doesn’t recognize (such as an unknown named range).
#REF! The formula refers to a cell that doesn’t exist (which can happen whenever cells are deleted).
#DIV/0! The formula attempts to divide by zero.

Another technique you can use to find the source of formula errors is to ensure that the appropriate cells are providing values for the formula. For example, you might want to calculate the total sales for a product category but accidentally create a formula referring to the products’ names, not their prices. You can identify that kind of error by having Excel trace a cell’s precedents, which are the cells with values used in the active cell’s formula. Excel identifies a cell’s precedents by drawing a blue tracer arrow from the precedent to the active cell.

You can also audit your worksheet by identifying cells with formulas that use a value from a given cell. For example, you might have the total cost of a single order used in a formula that calculates the average cost of all orders placed on a given day. Cells that use another cell’s value in their calculations are known as dependents, meaning that they depend on the value in the other cell to derive their own value. As with tracing precedents, you can point to Formula Auditing on the Tools menu, and click Trace Dependents to have Excel draw blue arrows from the active cell to those cells that have calculations based on that value.

If the cells identified by the tracer arrows aren’t the correct cells, you can hide the arrows and correct the formula. To hide the tracer arrows on a worksheet, you point to Formula Auditing on the Tools menu and click Remove All Arrows.

In this exercise, you use the formula auditing capabilities in Excel to identify and correct errors in a formula.

  1. On the Standard toolbar, click the Open button.
  2. The Open dialog box appears.

  3. Double-click the FindErrors.xls file.
  4. FindErrors.xls opens.

  5. Click cell D8.
  6. =SUM(C2:C6) appears in the formula bar.

  7. On the Tools menu, point to Formula Auditing, and then click Trace Precedents.
  8. A blue arrow appears between cell D8 and the group of cells from C2 to C6, indicating that cells in the C2:C6 range are precedents of the value in cell D8.

    (Image unavailable)

  9. On the Tools menu, point to Formula Auditing, and then click Remove All Arrows.
  10. The arrow disappears.

  11. Click cell D20.
  12. =AVERAGE(D7,D15) appears on the formula bar.

  13. On the Tools menu, point to Formula Auditing, and then click Trace Error.
  14. Blue arrows appear, pointing to cell D20 from cells D7 and D15. These arrows indicate that using the values (or lack of values, in this case) in the indicated cells is generating the error in cell D20.

    (Image unavailable)

  15. On the Tools menu, point to Formula Auditing, and then click Remove All Arrows.
  16. The arrows disappear.

  17. In the formula bar, delete the existing formula, type =AVERAGE(D8,D16), and press Enter.
  18. The value $149.08 appears in cell D20.

  19. On the Standard toolbar, click the Save button.
  20. Excel saves your changes.

  21. Click the Close button.
  22. FindErrors.xls closes.

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)