Synopses & Reviews
The world of mathematical formulas and jargon in Microsoft Excel is complex and can be intimidating for even advanced computer users. Perhaps like you, a majority of users find it difficult to get the most they can out of this very valuable program. Formulas and Functions with Microsoft Excel 2003 strives to break down the complexities by focusing on the four primary technologies to master: ranges, formulas, data analysis tools and lists. Chapters such as “Getting the Most Out of Ranges,” “Building Your Own Functions Using VBA,” and “Solving Complex Problems with Solver” will teach you practical skills and solutions that you can put to work immediately. Frustration will become a thing of the past as you begin to master the complex world of Excel!
About the Author
Paul McFedries is the president of Logophilia Limited, a technical writing company. While now primarily a writer, Paul has worked as a programmer, consultant, spreadsheet developer and website developer. Paul has written more than 40 books that have sold nearly three million copies worldwide. These books include Access 2003 Forms, Reports and Queries, Absolute Beginner's Guide to VBA and The Complete Idiot's Guide to Windows XP.
Table of Contents
Introduction.
What's in the Book. This Book's Special Features.
I. MASTERING EXCEL RANGES AND FORMULAS.
1. Getting the Most Out of Ranges.
A Review of Excel's Range-Selection Techniques. Advanced Range-Selection Techniques. Data Entry in a Range. Filling a Range. Using the Fill Handle. Creating a Series. Copying a Range. Moving a Range. Inserting and Deleting a Range. Clearing a Range. Using Excel's Reference Operators.
2. Using Range Names.
Defining a Range Name. Working with Range Names. Range Names and the Reference Operators.
3. Building Basic Formulas.
Understanding Formula Basics. Understanding Operator Precedence. Controlling Worksheet Calculation. Copying and Moving Formulas. Displaying Worksheet Formulas. Converting a Formula to a Value. Working with Range Names in Formulas. Working with Links in Formulas. Formatting Numbers, Dates, and Times.
4. Creating Advanced Formulas.
Working with Arrays. Using Iteration and Circular References. Consolidating Multisheet Data. Applying Data-Validation Rules to Cells. Using Dialog Box Controls on a Worksheet.
5. Troubleshooting Formulas.
Understanding Excel's Error Values. Case Study. Fixing Other Formula Errors. Using the Formula Error Checker. Auditing a Worksheet.
II. HARNESSING THE POWER OF FUNCTIONS.
6. Using Functions.
About Excel's Functions. The Structure of a Function. Typing a Function into a Formula. Using the Insert Function Feature. Loading the Analysis ToolPak Functions.
7. Working with Text Functions.
Working with Characters and Codes. Converting Text. Manipulating Text. Case Study. Case Study.
8. Working with Logical and Information Functions.
Adding Intelligence with Logical Functions. Case Study. Getting Data with Information Functions.
9. Working with Lookup Functions.
Understanding Lookup Tables. The CHOOSE() Function. Looking Up Values in Tables.
10. Working with Date and Time Functions.
How Excel Deals with Dates and Times. Using Excel's Date Functions. Using Excel's Time Functions. Case Study.
11. Working with Math Functions.
Understanding Excel's Rounding Functions. Case Study. Summing Values. The MOD() Function. Generating Random Numbers.
12. Working with Statistical Functions.
Understanding Descriptive Statistics. Counting Items with the COUNT() Function. Calculating Averages. Calculating Extreme Values. Calculating Measures of Variation. Working with Frequency Distributions. Using the Analysis ToolPak Statistical Tools.
III. BUILDING BUSINESS MODELS.
13. Analyzing Data with Lists.
Converting a Range to a List. Basic List Operations. Sorting a List. Filtering List Data. Summarizing List Data. Creating Automatic Subtotals. Excel's List Functions. Case Study.
14. Using Excel's Business-Modeling Tools.
Using What-If Analysis. Working with Goal Seek. Working with Scenarios.
15. Using Regression to Track Trends and Make Forecasts.
Choosing a Regression Method. Using Simple Regression on Linear Data. Case Study. Using Simple Regression on Nonlinear Data. Using Multiple Regression Analysis.
16. Solving Complex Problems with Solver.
Some Background on Solver. Loading Solver. Using Solver. Adding Constraints. Saving a Solution as a Scenario. Setting Other Solver Options. Making Sense of Solver's Messages. Case Study. Displaying Solver's Reports.
IV. BUILDING FINANCIAL FORMULAS.
17. Building Loan Formulas.
Understanding the Time Value of Money. Calculating the Loan Payment. Building a Loan Amortization Schedule. Calculating the Term of the Loan. Calculating the Interest Rate Required for a Loan. Calculating How Much You Can Borrow. Case Study.
18. Building Investment Formulas.
Working with Interest Rates. Calculating the Future Value. Working Toward an Investment Goal. Case Study.
19. Working with Bonds.
Glossary of Bond Terms. Calculating Bond Yields. Calculating Bond Prices. Calculating Bond Duration. Calculating Bond Principal at Maturity. Working with Coupons. Working with Zero-Coupon Bonds. Working with Treasury Bills.
20. Building Discount Formulas.
Calculating the Present Value. Discounting Cash Flows. Calculating the Payback Period. Calculating the Internal Rate of Return. Case Study.
Index.