Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
ISBN: 0470044020
EAN: 2147483647
EAN: 2147483647
Year: 2007
Pages: 212
Pages: 212
Authors: John Walkenbach
- 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
Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)
ISBN: 0470044020
EAN: 2147483647
EAN: 2147483647
Year: 2007
Pages: 212
Pages: 212
Authors: John Walkenbach
- Challenging the Unpredictable: Changeable Order Management Systems
- Distributed Data Warehouse for Geo-spatial Services
- A Hybrid Clustering Technique to Improve Patient Data Quality
- Relevance and Micro-Relevance for the Professional as Determinants of IT-Diffusion and IT-Use in Healthcare
- Development of Interactive Web Sites to Enhance Police/Community Relations