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 What-If 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, 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
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 Range-Selection 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 Business-Modeling 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
Formulas and Functions: Microsoft® Excel 2010
MASTER CORE EXCEL 2010 TOOLS FOR BUILDING POWERFUL, RELIABLE SPREADSHEETS! Excel expert Paul McFedries shows how …
book
Formulas and Functions with Microsoft® Excel 2003
The world of mathematical formulas and jargon in Microsoft Excel is complex and can be intimidating …
book
Business Solutions VBA for the 2007 Microsoft Office System
“VBA for the 2007 Microsoft Office System is jam-packed with code samples that you’ll be able …
book
Microsoft Office Excel 2007 On Demand
How to use • Create workbooks more efficiently using a new results-oriented interface • Use data-analysis …