### Available:*

Library | Call Number | Material Type | Home Location | Status |
---|---|---|---|---|

Central Library | HF5548.4.M523 W34472 2004 | Book and Software Set | Central Closed Stacks | Searching... |

### On Order

### Summary

### Summary

Everything you need to know about

* Mastering operators, error values, naming techniques, and absolute versus relative references

* Debugging formulas and using the auditing tools

* Importing and exporting XML files and mapping the data to specific cells

* Using Excel 2003's rights management feature

* Working magic with array formulas

* Developing custom formulas to produce the results you need

Here's the formula for Excel excellence

Formulas are the lifeblood of spreadsheets, and no one can bring a spreadsheet to life like John Walkenbach. In this detailed reference guide, he delves deeply into understanding, creating, and applying formulas in everything from basic workbooks to charts, pivot tables, and more advanced Excel applications. He examines financial formulas, explores the many options made possible with array formulas, teaches you to develop custom worksheet functions with VBA, and much more. Once again, "Mr. Spreadsheet" will astound you with the breadth and depth of Excel's capacity.

CD-ROM Includes

* Trial version of the author's award-winning Power Utility Pak 5

* More than 90 sample workbooks illustrating key formula concepts

### Author Notes

John Walkenbach, principal of JWalk and Associates Inc., is a leading authority on spreadsheet software and creator of the award-winning Power Utility Pak

### Table of Contents

Preface | p. vii |

Acknowledgments | p. xv |

Part I Basic Information | |

Chapter 1 Excel in a Nutshell | p. 3 |

The History of Excel | p. 4 |

The Object Model Concept | p. 7 |

The Workings of Workbooks | p. 8 |

Excel's User Interface | p. 10 |

Excel's Help System | p. 16 |

Cell Formatting | p. 17 |

Worksheet Formulas and Functions | p. 18 |

Objects on the Drawing Layer | p. 19 |

Customization in Excel | p. 21 |

Analysis Tools | p. 22 |

Protection Options | p. 25 |

Chapter 2 Basic Facts about Formulas | p. 29 |

Entering and Editing Formulas | p. 29 |

Using Operators in Formulas | p. 35 |

Calculating Formulas | p. 40 |

Cell and Range References | p. 41 |

Making an Exact Copy of a Formula | p. 45 |

Converting Formulas to Values | p. 46 |

Hiding Formulas | p. 48 |

Errors in Formulas | p. 49 |

Dealing with Circular References | p. 50 |

Goal Seeking | p. 52 |

Chapter 3 Working with Names | p. 55 |

What's in a Name? | p. 55 |

Methods for Creating Cell and Range Names | p. 56 |

Creating Multisheet Names | p. 63 |

A Name's Scope | p. 64 |

Working with Range and Cell Names | p. 66 |

How Excel Maintains Cell and Range Names | p. 75 |

Potential Problems with Names | p. 75 |

The Secret to Understanding Names | p. 78 |

Advanced Techniques That Use Names | p. 86 |

Part II Using Functions in Your Formulas | |

Chapter 4 Introducing Worksheet Functions | p. 95 |

What Is a Function? | p. 95 |

Function Argument Types | p. 98 |

Ways to Enter a Function into a Formula | p. 102 |

Function Categories | p. 107 |

Chapter 5 Manipulating Text | p. 111 |

A Few Words about Text | p. 111 |

Text Functions | p. 113 |

Advanced Text Formulas | p. 127 |

Custom VBA Text Functions | p. 134 |

Chapter 6 Working with Dates and Times | p. 135 |

How Excel Handles Dates and Times | p. 135 |

Date-Related Functions | p. 145 |

Time-Related Functions | p. 164 |

Chapter 7 Counting and Summing Techniques | p. 175 |

Counting and Summing Worksheet Cells | p. 175 |

Counting or Summing Records in Databases and Pivot Tables | p. 178 |

Basic Counting Formulas | p. 178 |

Advanced Counting Formulas | p. 182 |

Summing Formulas | p. 196 |

Conditional Sums Using a Single Criterion | p. 199 |

Conditional Sums Using Multiple Criteria | p. 202 |

Using VBA Functions to Count and Sum | p. 205 |

Chapter 8 Using Lookup Functions | p. 207 |

What Is a Lookup Formula? | p. 207 |

Functions Relevant to Lookups | p. 208 |

Basic Lookup Formulas | p. 209 |

Specialized Lookup Formulas | p. 216 |

Chapter 9 Databases and Lists | p. 233 |

Worksheet Lists or Databases | p. 233 |

Working with a Designated List | p. 235 |

Using AutoFiltering | p. 238 |

Using Advanced Filtering | p. 245 |

Specifying Advanced Filter Criteria | p. 249 |

Using Database Functions with Lists | p. 258 |

Summarizing a List with a Data Table | p. 261 |

Creating Subtotals | p. 264 |

Chapter 10 Miscellaneous Calculations | p. 267 |

Unit Conversions | p. 267 |

Solving Right Triangles | p. 278 |

Area, Surface, Circumference, and Volume Calculations | p. 280 |

Solving Simultaneous Equations | p. 283 |

Rounding Numbers | p. 284 |

Part III Financial Formulas | |

Chapter 11 Introducing Financial Formulas | p. 293 |

Excel's Basic Financial Functions | p. 293 |

Signing of Money Flows Convention | p. 295 |

Accumulation, Discounting, and Amortization Functions | p. 297 |

Converting Interest Rates | p. 315 |

Effective Cost of Loans | p. 321 |

Calculating the Interest and Principal Components | p. 324 |

Matching Different Interest and Payment Frequencies | p. 327 |

Limitations of Excel's Financial Functions | p. 328 |

Chapter 12 Discounting and Depreciation Financial Functions | p. 333 |

Using the NPV Function | p. 333 |

Using the IRR Function | p. 343 |

Multiple Rates of IRR and the MIRR Function | p. 347 |

Using the FVSCHEDULE Function | p. 350 |

Depreciation Calculations | p. 352 |

Chapter 13 Advanced Uses of Financial Functions and Formulas | p. 357 |

Creating Dynamic Financial Schedules | p. 357 |

Creating Amortization Schedules | p. 358 |

Summarizing Loan Options Using a Data Table | p. 364 |

Accumulation Schedules | p. 368 |

Discounted Cash Flow Schedules | p. 370 |

Credit Card Calculations | p. 371 |

XIRR and XNPV Functions | p. 373 |

Variable Rate Analysis | p. 376 |

Creating Indices | p. 377 |

Part IV Array Formulas | |

Chapter 14 Introducing Arrays | p. 383 |

Introducing Array Formulas | p. 383 |

Understanding the Dimensions of an Array | p. 387 |

Naming Array Constants | p. 390 |

Working with Array Formulas | p. 391 |

Using Multicell Array Formulas | p. 394 |

Using Single-Cell Array Formulas | p. 399 |

Chapter 15 Performing Magic with Array Formulas | p. 405 |

Working with Single-Cell Array Formulas | p. 405 |

Working with Multicell Array Formulas | p. 423 |

Returning an Array from a Custom VBA Function | p. 427 |

Part V Miscellaneous Formula Techniques | |

Chapter 16 Intentional Circular References | p. 433 |

What Are Circular References? | p. 433 |

Intentional Circular References | p. 435 |

How Excel Determines Calculation and Iteration Settings | p. 438 |

Circular Reference Examples | p. 439 |

Potential Problems with Intentional Circular References | p. 446 |

Chapter 17 Charting Techniques | p. 449 |

Representing Data in Charts | p. 449 |

Plotting Data Interactively | p. 469 |

Creating Awesome Designs | p. 477 |

Working with Trendlines | p. 478 |

Useful Chart Tricks | p. 488 |

Chapter 18 Pivot Tables | p. 497 |

About Pivot Tables | p. 497 |

Creating a Pivot Table | p. 502 |

Grouping Pivot Table Items | p. 511 |

Creating a Calculated Field or Calculated Item | p. 514 |

Chapter 19 Conditional Formatting and Data Validation | p. 521 |

Conditional Formatting | p. 521 |

Data Validation | p. 542 |

Chapter 20 Creating Megaformulas | p. 551 |

What Is a Megaformula? | p. 551 |

Creating a Megaformula: A Simple Example | p. 552 |

Megaformula Examples | p. 554 |

The Pros and Cons of Megaformulas | p. 567 |

Chapter 21 Tools and Methods for Debugging Formulas | p. 569 |

Formula Debugging? | p. 569 |

Formula Problems and Solutions | p. 570 |

Excel's Auditing Tools | p. 582 |

Third-Party Auditing Tools | p. 591 |

Part VI Developing Custom Worksheet Functions | |

Chapter 22 Introducing VBA | p. 597 |

About VBA | p. 597 |

Introducing the Visual Basic Editor | p. 598 |

Chapter 23 Function Procedure Basics | p. 609 |

Why Create Custom Functions? | p. 609 |

An Introductory VBA Function Example | p. 610 |

About Function Procedures | p. 612 |

Using the Insert Function Dialog Box | p. 615 |

Testing and Debugging Your Functions | p. 619 |

Creating Add-Ins | p. 626 |

Chapter 24 VBA Programming Concepts | p. 629 |

An Introductory Example Function Procedure | p. 629 |

Using Comments in Your Code | p. 632 |

Using Variables, Data Types, and Constants | p. 632 |

Using Assignment Expressions | p. 638 |

Using Arrays | p. 640 |

Using VBA's Built-in Functions | p. 641 |

Controlling Execution | p. 643 |

Using Ranges | p. 653 |

Chapter 25 VBA Custom Function Examples | p. 663 |

Simple Functions | p. 663 |

Determining a Cell's Data Type | p. 669 |

A Multifunctional Function | p. 670 |

Generating Random Numbers | p. 672 |

Calculating Sales Commissions | p. 674 |

Text Manipulation Functions | p. 678 |

Counting and Summing Functions | p. 684 |

Date Functions | p. 687 |

Returning the Last Nonempty Cell in a Column or Row | p. 690 |

Multisheet Functions | p. 692 |

Advanced Function Techniques | p. 695 |

Appendix A Working with Imported 1-2-3 Files | p. 709 |

Appendix B Excel Function Reference | p. 717 |

Appendix C Using Custom Number Formats | p. 735 |

Appendix D Additional Excel Resources | p. 761 |

Appendix E What's on the CD-ROM | p. 769 |

Index | p. 783 |

End-User License Agreement | p. 829 |