Book description
“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 WhatIf Analysis
Model Your Business
Track Trends and Make
Forecasts
Analyze Data
Find Optimal Solutions
Build Dynamic Loan
Schedules
Most Microsoft® 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® Office Excel 2007 demystifies worksheet formulas and presents the most useful Excel functions in an accessible, jargonfree way. This book not only takes you through Excel’s intermediate and advanced formulabuilding 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 nononsense, stepbystep 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 knowhow that you can put to use right away.
• Provides numerous realworld 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 BusinessModeling 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 wellknown 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
Table of contents
 Copyright
 About the Author
 Acknowledgments
 We Want to Hear from You!
 Reader Services
 Introduction

I. Mastering Excel Ranges and Formulas

1. Getting the Most Out of Ranges
 Advanced RangeSelection Techniques
 Data Entry in a Range
 Filling a Range
 Using the Fill Handle
 Creating a Series
 Advanced Range Copying
 Clearing a Range
 Applying Conditional Formatting to a Range

2. Using Range Names
 Defining a Range Name

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
 4. Creating Advanced Formulas
 5. Troubleshooting Formulas

1. Getting the Most Out of Ranges

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
 How Excel Deals with Dates and Times

Using Excel’s Date Functions
 Returning a Date

Returning Parts of a Date
 The YEAR() Function
 The MONTH() Function
 The DAY() Function
 The WEEKDAY() Function
 The WEEKNUM() Function
 Returning a Date X Years, Months, or Days from Now
 A Workday Alternative: The WORKDAY() Function
 Adding X Months: A Problem
 The EDATE() Function
 The EOMONTH() Function
 Returning the Last Day of Any Month
 Determining a Person’s Birthday Given the Birth Date
 Returning the Date of the Nth Occurrence of a Weekday in a Month
 Calculating Holiday Dates
 Calculating the Julian Date
 Calculating the Difference Between Two Dates
 Using Excel’s Time Functions

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
 Summing Values
 The MOD() Function
 Generating Random Numbers

Understanding Excel’s Rounding Functions
 12. Working with Statistical Functions

III. Building Business Models

13. Analyzing Data with Tables
 Converting a Range to a Table
 Basic Table Operations
 Sorting a Table
 Filtering Table Data
 Referencing Tables in Formulas
 Excel’s Table Functions
 14. Analyzing Data with PivotTables
 15. Using Excel’s BusinessModeling Tools

16. Using Regression to Track Trends and Make Forecasts
 Choosing a Regression Method
 Using Simple Regression on Linear Data
 Using Simple Regression on Nonlinear Data
 Using Multiple Regression Analysis
 17. Solving Complex Problems with Solver

13. Analyzing Data with Tables

IV. Building Financial Formulas
 18. Building Loan Formulas
 19. Building Investment Formulas
 20. Building Discount Formulas
Product information
 Title: Business Solutions Formulas and Functions with Microsoft® Office Excel 2007
 Author(s):
 Release date: March 2007
 Publisher(s): Que
 ISBN: 0789736683
You might also like
book
Microsoft Excel Functions & Formulas
Easy to use and equipped with a variety of functions, Microsoft Excel is the tool of …
book
Handbook of the Economics of Finance SET:Volumes 2A & 2B
This twovolume set of 23 articles authoritatively describes recent scholarship in corporate finance and asset pricing. …
book
HANDBOOK OF LABOR ECONOMICS, VOL 4B
What factors affect the ways individuals participate in labor markets? "New Developments and Research on Labor …
book
Fixed Income Securities: Valuation, Risk, and Risk Management
The deep understanding of the forces that affect the valuation, risk and return of fixed income …