Table Of Content

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 [email protected] or (800) 382-3419. For government sales inquiries, please contact [email protected]. For questions about sales outside the U.S., please contact [email protected]. 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