---

Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

ISBN: 0470044020
EAN: 2147483647

Year: 2007
Pages: 212

Authors: John Walkenbach

BUY ON AMAZON

• Table of Contents
• Back Cover
• Excel 2007 Formulas
• Introduction
• What You Should Know
• What You Should Have
• Conventions in This Book
• How This Book Is Organized
• How to Use This Book
• About the Companion CD-ROM
• About the Power Utility Pak Offer
• Reach Out
• Part I: Basic Information
• Chapter 1: Excel in a Nutshell
• The Object Model Concept
• The Workings of Workbooks
• The Excel User Interface
• The Excel Help System
• Cell Formatting
• Worksheet Formulas and Functions
• Objects on the Drawing Layer
• Customization in Excel
• Internet Features
• Analysis Tools
• Protection Options
• Chapter 2: Basic Facts about Formulas
• Using Operators in Formulas
• Calculating Formulas
• Cell and Range References
• Making an Exact Copy of a Formula
• Converting Formulas to Values
• Hiding Formulas
• Errors in Formulas
• Dealing with Circular References
• Goal Seeking
• Chapter 3: Working with Names
• A Name s Scope
• The Name Manager
• Shortcuts for Creating Cell and Range Names
• Creating Multisheet Names
• Working with Range and Cell Names
• How Excel Maintains Cell and Range Names
• Potential Problems with Names
• The Secret to Understanding Names
• Advanced Techniques That Use Names
• Part II: Using Functions in Your Formulas
• Chapter 4: Introducing Worksheet Functions
• Function Argument Types
• Ways to Enter a Function into a Formula
• Function Categories
• Chapter 5: Manipulating Text
• Text Functions
• Advanced Text Formulas
• Custom VBA Text Functions
• Chapter 6: Working with Dates and Times
• Date-Related Functions
• Time-Related Functions
• Chapter 7: Counting and Summing Techniques
• Counting or Summing Records in Databases and Pivot Tables
• Basic Counting Formulas
• Advanced Counting Formulas
• Summing Formulas
• Conditional Sums Using a Single Criterion
• Conditional Sums Using Multiple Criteria
• Chapter 8: Using Lookup Functions
• Functions Relevant to Lookups
• Basic Lookup Formulas
• Specialized Lookup Formulas
• Chapter 9: Tables and Worksheet Databases
• Working with Tables
• Using Advanced Filtering
• Specifying Advanced Filter Criteria
• Using Database Functions
• Inserting Subtotals
• Chapter 10: Miscellaneous Calculations
• Solving Right Triangles
• Area, Surface, Circumference, and Volume Calculations
• Solving Simultaneous Equations
• Rounding Numbers
• Part III: Financial Formulas
• Chapter 11: Borrowing and Investing Formulas
• The Basic Excel Financial Functions
• Calculating the Interest and Principal Components
• Converting Interest Rates
• Limitations of Excel s Financial Functions
• Bond Calculations
• Chapter 12: Discounting and Depreciation Formulas
• Using the IRR Function
• Multiple Rates of IRR and the MIRR Function
• Irregular Cash Flows
• Using the FVSCHEDULE Function
• Depreciation Calculations
• Chapter 13: Financial Schedules
• Creating Amortization Schedules
• Summarizing Loan Options Using a Data Table
• Financial Statements and Ratios
• Creating Indices
• Part IV: Array Formulas
• Chapter 14: Introducing Arrays
• Understanding the Dimensions of an Array
• Naming Array Constants
• Working with Array Formulas
• Using Multicell Array Formulas
• Using Single-Cell Array Formulas
• Chapter 15: Performing Magic with Array Formulas
• Working with Multicell Array Formulas
• Part V: Miscellaneous Formula Techniques
• Chapter 16: Intentional Circular References
• Intentional Circular References
• How Excel Determines Calculation and Iteration Settings
• Circular Reference Examples
• Potential Problems with Intentional Circular References
• Chapter 17: Charting Techniques
• Creating Links to Cells
• Chart Examples
• Chapter 18: Pivot Tables
• A Pivot Table Example
• Data Appropriate for a Pivot Table
• Creating a Pivot Table
• More Pivot Table Examples
• Grouping Pivot Table Items
• Creating a Frequency Distribution
• Creating a Calculated Field or Calculated Item
• Referencing Cells within a Pivot Table
• Another Pivot Table Example
• Producing a Report with a Pivot Table
• Chapter 19: Conditional Formatting and Data Validation
• Data Validation
• Chapter 20: Creating Megaformulas
• Creating a Megaformula: A Simple Example
• Megaformula Examples
• The Pros and Cons of Megaformulas
• Chapter 21: Tools and Methods for Debugging Formulas
• Formula Problems and Solutions
• Excel s Auditing Tools
• Part VI: Developing Custom Worksheet Functions
• Chapter 22: Introducing VBA
• Displaying the Developer Tab
• About Macro Security
• Saving Workbooks That Contain Macros
• Introducing the Visual Basic Editor
• Chapter 23: Function Procedure Basics
• An Introductory VBA Function Example
• About Function Procedures
• Using the Insert Function Dialog Box
• Testing and Debugging Your Functions
• Creating Add-Ins
• Chapter 24: VBA Programming Concepts
• Using Comments in Your Code
• Using Variables, Data Types, and Constants
• Using Assignment Expressions
• Using Arrays
• Using Built-In VBA Functions
• Controlling Execution
• Using Ranges
• Chapter 25: VBA Custom Function Examples
• Determining a Cell s Data Type
• A Multifunctional Function
• Generating Random Numbers
• Calculating Sales Commissions
• Text Manipulation Functions
• Counting Functions
• Date Functions
• Returning the Last Nonempty Cell in a Column or Row
• Multisheet Functions
• Advanced Function Techniques
• Part VII: Appendixes
• Appendix A: Excel Function Reference
• Appendix B: Using Custom Number Formats
• Creating a Custom Number Format
• Custom Number Format Examples
• Appendix C: Additional Excel Resources
• Microsoft Technical Support
• Internet Newsgroups
• Web Sites
• Appendix D: What s on the CD-ROM
• System Requirements
• Using the CD
• Files and Software on the CD
• Troubleshooting
• Index
• A
• B
• C
• D
• E
• F
• G
• H
• I
• J
• K
• L
• M
• N
• O
• P
• Q
• R
• S
• T
• U
• V
• W
• X
• Y
• Z
• List of Figures
• List of Tables
• List of Sidebars
• CD Content