"If you've never quite grasped formulas and functions, Paul McFedries will radically expand your understanding and use of Excel. And if you're already an expert and you're moving up to Excel 2007, this book will quickly show you features you've only dreamed of until now..."
-Thomas `Duffbert' Duff, Duffbert's Random Musings, http://www.twduff.com
Develop your Microsoft Excel expertise instantly with proven techniques
Master Excel Ranges
Create Powerful Arrays
Troubleshoot Formula Problems
Validate Worksheet Data
Perform What-If Analysis
Model Your Business
Track Trends and Make
Forecasts
Analyze Data
Find Optimal Solutions
Build Dynamic Loan
Schedules
Most Microsoft (R) Excel users learn only a small percentage of the program's features. They know they could get more out of Excel if they could just get a leg up on building formulas and using functions. Unfortunately, this side of Excel appears complex and intimidating to the uninitiated-shrouded in the mysteries of mathematics, finance, and impenetrable spreadsheet jargon.
Sound familiar? If you're a businessperson who needs to use Excel as an everyday part of your job, then you've come to the right book. Formulas and Functions with Microsoft (R) Office Excel 2007 demystifies worksheet formulas and presents the most useful Excel functions in an accessible, jargon-free way. This book not only takes you through Excel's intermediate and advanced formula-building features, it also tells you why these features are useful to you and shows you how to use them in everyday situations. Throughout the book you'll find no-nonsense, step-by-step tutorials and lots of practical examples aimed directly at business users.
* Focuses like a laser on the four technologies that you must master to get the most out of Excel: ranges, formulas, functions, and data analysis tools.
* Shuns spreadsheet theory in favor of practical know-how that you can put to use right away.
* Provides numerous real-world examples and techniques to help you learn and understand the importance of each section.
Introduction
1 Getting the Most Out of Ranges
2 Using Range Names
3 Building Basic Formulas
4 Creating Advanced Formulas
5 Troubleshooting Formulas
II Harnessing the Power of Functions
6 Understanding Functions
7 Working with Text Functions
8 Working with Logical and Information Functions
9 Working with Lookup Functions
10 Working with Date and Time Functions
11 Working with Math Functions
12 Working with Statistical Functions
III Building Business Models
13 Analyzing Data with Tables
14 Analyzing Data with PivotTables
15 Using Excel's Business-Modeling Tools
16 Using Regression to Track Trends and Make Forecasts
17 Solving Complex Problems with Solver
IV Building Financial Formulas
18 Building Loan Formulas
19 Building Investment Formulas
20 Building Discount Formulas
Paul McFedries is well-known as a teacher of Windows and Office, particularly Excel, and is the president of Logophilia Limited, a technical writing company. Paul has been working with spreadsheets for more than 20 years and has been developing Excel solutions since the late 1980s. Now primarily a writer, Paul has written more than 50 books that have sold more than three million copies worldwide. These books include Microsoft Office Access 2007 Forms, Reports, and Queries; Tricks of the Microsoft Office 2007 Gurus (all from Que); and Microsoft Windows Vista Unleashed (Sams).
Category Office Productivity Suite
Covers Microsoft Office Excel 2007
User Level Intermediate - Advanced
Sprache
Verlagsort
Verlagsgruppe
Zielgruppe
Für höhere Schule und Studium
Maße
Höhe: 237 mm
Breite: 174 mm
Dicke: 31 mm
Gewicht
ISBN-13
978-0-7897-3668-0 (9780789736680)
Copyright in bibliographic data and cover images is held by Nielsen Book Services Limited or by the publishers or by their respective licensors: all rights reserved.
Schweitzer Klassifikation
Paul McFedries is the president of Logophilia Limited, a technical writing company. Now primarily a writer, Paul has worked as a programmer, consultant, spreadsheet developer, and website developer. He has written more than 50 books that have sold more than three million copies worldwide. These books include Access 2007 Forms, Reports, and Queries (Que, 2007), Tricks of the Microsoft Office 2007 Gurus (Que, 2007), VBA for the 2007 Microsoft Office System (Que, 2007), and Windows Vista Unleashed (Sams, 2006).
Introduction
What's in the Book
This Book's Special Features
I Mastering Excel Ranges and Formulas
1 Getting the Most Out of Ranges
Advanced Range-Selection Techniques
Mouse Range-Selection Tricks
Keyboard Range-Selection Tricks
Working with 3D Ranges
Selecting a Range Using Go To
Using the Go To Special Dialog Box
Data Entry in a Range
Filling a Range
Using the Fill Handle
Using AutoFill to Create Text and Numeric Series
Creating a Custom AutoFill List
Filling a Range
Creating a Series
Advanced Range Copying
Copying Selected Cell Attributes
Combining the Source and Destination Arithmetically
Transposing Rows and Columns
Clearing a Range
Applying Conditional Formatting to a Range
Creating Highlight Cells Rules
Creating Top/Bottom Rules
Adding Data Bars
Adding Color Scales
Adding Icon Sets
From Here
2 Using Range Names
Defining a Range Name
Working with the Name Box
Using the New Name Dialog Box
Changing the Scope to Define Sheet-Level Names
Using Worksheet Text to Define Names
Naming Constants
Working with Range Names
Referring to a Range Name
Working with Name AutoComplete
Navigating Using Range Names
Pasting a List of Range Names in a Worksheet
Displaying the Name Manager
Filtering Names
Editing a Range Name's Coordinates
Adjusting Range Name Coordinates Automatically
Changing a Range Name
Deleting a Range Name
Using Names with the Intersection Operator
From Here
3 Building Basic Formulas
Understanding Formula Basics
Formula Limits in Excel 2007
Entering and Editing Formulas
Using Arithmetic Formulas
Using Comparison Formulas
Using Text Formulas
Using Reference Formulas
Understanding Operator Precedence
The Order of Precedence
Controlling the Order of Precedence
Controlling Worksheet Calculation
Copying and Moving Formulas
Understanding Relative Reference Format
Understanding Absolute Reference Format
Copying a Formula Without Adjusting Relative References
Displaying Worksheet Formulas
Converting a Formula to a Value
Working with Range Names in Formulas
Pasting a Name into a Formula
Applying Names to Formulas
Naming Formulas
Working with Links in Formulas
Understanding External References
Updating Links
Changing the Link Source
Formatting Numbers, Dates, and Times
Numeric Display Formats
Date and Time Display Formats
Deleting Custom Formats
From Here
4 Creating Advanced Formulas
Working with Arrays
Using Array Formulas
Using Array Constants
Functions That Use or Return Arrays
Using Iteration and Circular References
Consolidating Multisheet Data
Consolidating by Position
Consolidating by Category
Applying Data-Validation Rules to Cells
Using Dialog Box Controls on a Worksheet
Using the Form Controls
Adding a Control to a Worksheet
Linking a Control to a Cell Value
Understanding the Worksheet Controls
From Here
5 Troubleshooting Formulas
Understanding Excel's Error Values
#DIV/0!
#N/A
#NAME?
Avoiding #NAME? Errors When Deleting Range Names
#NULL!
#NUM!
#REF!
#VALUE!
Fixing Other Formula Errors
Missing or Mismatched Parentheses
Erroneous Formula Results
Fixing Circular References
Handling Formula Errors with IFERROR()
Using the Formula Error Checker
Choosing an Error Action
Setting Error Checker Options
Auditing a Worksheet
Understanding Auditing
Tracing Cell Precedents
Tracing Cell Dependents
Tracing Cell Errors
Removing Tracer Arrows
Evaluating Formulas
Watching Cell Values
From Here
II Harnessing the Power of Functions
6 Understanding 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
From Here
7 Working with Text Functions
Excel's Text Functions
Working with Characters and Codes
The CHAR() Function
The CODE() Function
Converting Text
The LOWER() Function
The UPPER() Function
The PROPER() Function
Formatting Text
The DOLLAR() Function
The FIXED() Function
The TEXT() Function
Displaying When a Workbook Was Last Updated
Manipulating Text
Removing Unwanted Characters from a String
The TRIM() Function
The CLEAN() Function
The REPT() Function: Repeating a Character
Padding a Cell
Building Text Charts
Extracting a Substring
The LEFT() Function
The RIGHT() Function
The MID() Function
Converting Text to Sentence Case
A Date-Conversion Formula
Generating Account Numbers
Searching for Substrings
The FIND() and SEARCH() Functions
Extracting a First Name or Last Name
Extracting First Name, Last Name, and Middle Initial
Determining the Column Letter
Substituting One Substring for Another
The REPLACE() Function
The SUBSTITUTE() Function
Removing a Character from a String
Removing Two Different Characters from a String
Removing Line Feeds
Generating Account Numbers, Part 2
From Here
8 Working with Logical and Information Functions
Adding Intelligence with Logical Functions
Using the IF() Function
Performing Multiple Logical Tests
Combining Logical Functions with Arrays
Building an Accounts Receivable Aging Worksheet
Calculating a Smarter Due Date
Aging Overdue Invoices
Getting Data with Information Functions
The CELL() Function
The ERROR.TYPE() Function
The INFO() Function
The IS Functions
From Here
9 Working with Lookup Functions
Understanding Lookup Tables
The CHOOSE() Function
Determining the Name of the Day of the Week
Determining the Month of the Fiscal Year
Calculating Weighted Questionnaire Results
Integrating CHOOSE() and Worksheet Option Buttons
Looking Up Values in Tables
The VLOOKUP() Function
The HLOOKUP() Function
Returning a Customer Discount Rate with a Range Lookup
Returning a Tax Rate with a Range Lookup
Finding Exact Matches
Advanced Lookup Operations
From Here
10 Working with Date and Time Functions
How Excel Deals with Dates and Times
Entering Dates and Times
Excel and Two-Digit Years
Using Excel's Date Functions
Returning a Date
Returning Parts of a Date
Calculating the Difference Between Two Dates
Using Excel's Time Functions
Returning a Time
Returning Parts of a Time
Calculating the Difference Between Two Times
Building an Employee Time Sheet
From Here
11 Working with Math Functions
Understanding Excel's Rounding Functions
The ROUND() Function
The MROUND() Function
The ROUNDDOWN() and ROUNDUP() Functions
The CEILING() and FLOOR() Functions
Determining the Fiscal Quarter in Which a Date Falls
Calculating Easter Dates
The EVEN() and ODD() Functions
The INT() and TRUNC() Functions
Using Rounding to Prevent Calculation Errors
Setting Price Points
Rounding Billable Time
Summing Values
The SUM() Function
Calculating Cumulative Totals
Summing Only the Positive or Negative Values in a Range
The MOD() Function
A Better Formula for Time Differences
Summing Every nth Row
Determining Whether a Year Is a Leap Year
Creating Ledger Shading
Generating Random Numbers
The RAND() Function
The RANDBETWEEN() Function
From Here
12 Working with Statistical Functions
Understanding Descriptive Statistics
Counting Items with the COUNT() Function
Calculating Averages
The AVERAGE() Function
The MEDIAN() Function
The MODE() Function
Calculating the Weighted Mean
Calculating Extreme Values
The MAX() and MIN() Functions
The LARGE() and SMALL() Functions
Performing Calculations on the Top k Values
Performing Calculations on the Bottom k Values
Calculating Measures of Variation
Calculating the Range
Calculating the Variance with the VAR() Function
Calculating the Standard Deviation with the STDEVP() and STDEV() Functions
Working with Frequency Distributions
The FREQUENCY() Function
Understanding the Normal Distribution and the NORMDIST() Function
The Shape of the Curve I: The SKEW() Function
The Shape of the Curve II: The KURT() Function
Using the Analysis ToolPak Statistical Tools
Using the Descriptive Statistics Tool
Determining the Correlation Between Data
Working with Histograms
Using the Random Number Generation Tool
Working with Rank and Percentile
From Here
III Building Business Models
13 Analyzing Data with Tables
Converting a Range to a Table
Basic Table Operations
Sorting a Table
Sorting a Table in Natural Order
Sorting on Part of a Field
Sorting Without Articles
Filtering Table Data
Using Filter Lists to Filter a Table
Using Complex Criteria to Filter a Table
Entering Computed Criteria
Copying Filtered Data to a Different Range
Referencing Tables in Formulas
Using Table Specifiers
Entering Table Formulas
Excel's Table Functions
About Table Functions
Table Functions That Don't Require a Criteria Range
Table Functions That Accept Multiple Criteria
Table Functions That Require a Criteria Range
Applying Statistical Table Functions to a Defects Database
From Here
14 Analyzing Data with PivotTables
What Are PivotTables?
How PivotTables Work
Some PivotTable Terms
Building PivotTables
Building a PivotTable from a Table or Range
Building a PivotTable from an External Database
Working with and Customizing a PivotTable
Working with PivotTable Subtotals
Hiding PivotTable Grand Totals
Hiding PivotTable Subtotals
Customizing the Subtotal Calculation
Changing the Data Field Summary Calculation
Using a Difference Summary Calculation
Using a Percentage Summary Calculation
Using a Running Total Summary Calculation
Using an Index Summary Calculation
Creating Custom PivotTable Calculations
Creating a Calculated Field
Creating a Calculated Item
Budgeting with Calculated Items
Using PivotTable Results in a Worksheet Formula
From Here
15 Using Excel's Business-Modeling Tools
Using What-If Analysis
Setting Up a One-Input Data Table
Adding More Formulas to the Input Table
Setting Up a Two-Input Table
Editing a Data Table
Working with Goal Seek
How Does Goal Seek Work?
Running Goal Seek
Optimizing Product Margin
A Note About Goal Seek's Approximations
Performing a Break-Even Analysis
Solving Algebraic Equations
Working with Scenarios
Understanding Scenarios
Setting Up Your Worksheet for Scenarios
Adding a Scenario
Displaying a Scenario
Editing a Scenario
Merging Scenarios
Generating a Summary Report
Deleting a Scenario
From Here
16 Using Regression to Track Trends and Make Forecasts
Choosing a Regression Method
Using Simple Regression on Linear Data
Analyzing Trends Using Best-Fit Lines
Making Forecasts
Trend Analysis and Forecasting for a Seasonal Sales Model
Using Simple Regression on Nonlinear Data
Working with an Exponential Trend
Working with a Logarithmic Trend
Working with a Power Trend
Using Polynomial Regression Analysis
Using Multiple Regression Analysis
From Here
17 Solving Complex Problems with Solver
Some Background on Solver
The Advantages of Solver
When Do You Use Solver?
Loading Solver
Using Solver
Adding Constraints
Saving a Solution as a Scenario
Setting Other Solver Options
Controlling Solver
Selecting the Method Solver Uses
Working with Solver Models
Making Sense of Solver's Messages
Solving the Transportation Problem
Displaying Solver's Reports
The Answer Report
The Sensitivity Report
The Limits Report
From Here
IV Building Financial Formulas
18 Building Loan Formulas
Understanding the Time Value of Money
Calculating the Loan Payment
Loan Payment Analysis
Working with a Balloon Loan
Calculating Interest Costs, Part I
Calculating the Principal and Interest
Calculating Interest Costs, Part 2
Calculating Cumulative Principal and Interest
Building a Loan Amortization Schedule
Building a Fixed-Rate Amortization Schedule
Building a Dynamic Amortization Schedule
Calculating the Term of the Loan
Calculating the Interest Rate Required for a Loan
Calculating How Much You Can Borrow
Working with Mortgages
Building a Variable-Rate Mortgage Amortization Schedule
Allowing for Mortgage Principal Paydowns
From Here
19 Building Investment Formulas
Working with Interest Rates
Understanding Compound Interest
Nominal Versus Effective Interest
Converting Between the Nominal Rate and the Effective Rate
Calculating the Future Value
The Future Value of a Lump Sum
The Future Value of a Series of Deposits
The Future Value of a Lump Sum Plus Deposits
Working Toward an Investment Goal
Calculating the Required Interest Rate
Calculating the Required Number of Periods
Calculating the Required Regular Deposit
Calculating the Required Initial Deposit
Calculating the Future Value with Varying Interest Rates
Building an Investment Schedule
From Here
20 Building Discount Formulas
Calculating the Present Value
Taking Inflation into Account
Calculating Present Value Using PV()
Income Investing Versus Purchasing a Rental Property
Buying Versus Leasing
Discounting Cash Flows
Calculating the Net Present Value
Calculating Net Present Value Using NPV()
Net Present Value with Varying Cash Flows
Net Present Value with Nonperiodic Cash Flows
Calculating the Payback Period
Simple Undiscounted Payback Period
Exact Undiscounted Payback Point
Discounted Payback Period
Calculating the Internal Rate of Return
Using the IRR() Function
Calculating the Internal Rate of Return for Nonperiodic Cash Flows
Calculating Multiple Internal Rates of Return
Publishing a Book
From Here
0789736683, TOC, 2/19/2007
