Excel 2016 Formulas and Functions: Includes Content Update Program

1194 Pages·2016·48.24 MB·English

About This eBook
ePUB is an open, industry-standard format for eBooks. However, support of ePUB and
its many features varies across reading devices and applications. Use your device or app
settings to customize the presentation to your liking. Settings that you can customize often
include font, font size, single or double column, landscape or portrait mode, and figures
that you can click or tap to enlarge. For additional information about the settings and
features on your reading device or app, visit the device manufacturer’s Web site.
Many titles include programming code or configuration examples. To optimize the
presentation of these elements, view the eBook in single-column, landscape mode and
adjust the font size to the smallest setting. In addition to presenting code and
configurations in the reflowable text format, we have included images of the code that
mimic the presentation found in the print book; therefore, where the reflowable format
may compromise the presentation of the code listing, you will see a “Click here to view
code image” link. Click the link to view the print-fidelity code image. To return to the
previous page viewed, click the Back button on your device or app.
®
Excel 2016 Formulas and Functions
Paul McFedries
800 East 96th Street
Indianapolis, Indiana 46240 USA
®
Excel 2016 Formulas and Functions
Copyright © 2016 by Pearson Education, Inc.
All rights reserved. No part of this book shall be reproduced, stored in a retrieval system,
or transmitted by any means, electronic, mechanical, photocopying, recording, or
otherwise, without written permission from the publisher. No patent liability is assumed
with respect to the use of the information contained herein. Although every precaution has
been taken in the preparation of this book, the publisher and author assume no
responsibility for errors or omissions. Nor is any liability assumed for damages resulting
from the use of the information contained herein.
ISBN-13: 978-0-7897-5564-3
ISBN-10: 0-7897-5564-5
Library of Congress Control Number: 2015944776
Printed in the United States of America
First Printing: October 2015
Editor-in-Chief
Greg Wiegand
Acquisitions Editor
Michelle Newcomb
Development Editor
Joyce Neilsen
Managing Editor
Kristy Hart
Senior Project Editor
Lori Lyons
Technical Editor
Bob Umlas
Copy Editor
Kitty Wilson
Indexer
Tim Wright
Proofreader
Gill Editorial Services
Editorial Assistant
Kristen Watterson
Compositor
Nonie Ratcliff
Cover Designer
Chuti Prasertsith
Trademarks
All terms mentioned in this book that are known to be trademarks or service marks have
been appropriately capitalized. Que Publishing cannot attest to the accuracy of this
information. Use of a term in this book should not be regarded as affecting the validity of
any trademark or service mark.
Cover design by Chuti Prasertsith
Cover graphic by ©foxie/ShutterStock
Warning and Disclaimer
Every effort has been made to make this book as complete and as accurate as possible, but
no warranty or fitness is implied. The information provided is on an “as is” basis. The
author and the publisher shall have neither liability nor responsibility to any person or
entity with respect to any loss or damages arising from the information contained in this
book.
Special Sales
For information about buying this title in bulk quantities, or for special sales opportunities
(which may include electronic versions; custom cover designs; and content particular to
your business, training goals, marketing focus, or branding interests), please contact our
corporate sales department at [e-mail] or [phone].
For government sales inquiries, please contact
[e-mail].
For questions about sales outside the U.S., please contact
[e-mail].
Contents at a Glance
Introduction
Part I: Mastering Excel Ranges and Formulas
1 Getting the Most Out of Ranges
2 Using Range Names
3 Building Basic Formulas
4 Creating Advanced Formulas
5 Troubleshooting Formulas
Part 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
Part 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
Part IV: Building Financial Formulas
18 Building Loan Formulas
19 Building Investment Formulas
20 Building Discount Formulas
Index
Contents
Introduction
PART 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
Flash-Filling a Range
Creating a Series
Advanced Range Copying and Pasting
Pasting Selected Cell Attributes
Combining Two Ranges 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
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 AutoComplete for Range Names
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
3 Building Basic Formulas
Understanding Formula Basics
Formula Limits in Excel 2016
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
Displaying All Worksheet Formulas
Displaying a Cell’s Formula by Using FORMULATEXT()
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
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
Displaying the Developer Tab
Using the Form Controls
Adding a Control to a Worksheet
Linking a Control to a Cell Value
Understanding the Worksheet Controls
5 Troubleshooting Formulas
Understanding Excel’s Error Values
#DIV/0!
#N/A
#NAME?
Case Study: 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
PART 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
7 Working with Text Functions