Excel 2013 Formulas And Functions

C o n t e n t sa taG l a n c eIntroductionPart I Mastering Excel Ranges and Formulas1 Getting the Most Out of Ranges .52 Using Range Names. 373 Building Basic Formulas. 53MrExcelLIBRARY4 Creating Advanced Formulas . 875 Troubleshooting Formulas.111Part II Harnessing the Power of FunctionsExcel 2013Formulas andFunctions6 Understanding Functions .1297 Working with Text Functions.1398 Working with Logical and Information Functions.1639 Working with Lookup Functions .19110 Working with Date and Time Functions.20911 Working with Math Functions.23712 Working with Statistical Functions .257Part III Building Business Models13 Analyzing Data with Tables.28914 Analyzing Data with PivotTables .32115 Using Excel’s Business-Modeling Tools.34516 Using Regression to Track Trends and Make Forecasts .36717 Solving Complex Problems with Solver.405Part IV Building Financial FormulasPaul McFedries18 Building Loan Formulas .42719 Building Investment Formulas.44520 Building Discount Formulas .459Index .479800 East 96th Street,Indianapolis, Indiana46240USA

Excel 2013 Formulas and FunctionsCopyright 2013 by Que PublishingAll rights reserved. No part of this book shall be reproduced, stored in aretrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from thepublisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in thepreparation of this book, the publisher and author assume no responsibilityfor errors or omissions. Nor is any liability assumed for damages resultingfrom the use of the information contained herein.ISBN-13: 978-0-7897-4867-6ISBN-10: 0-7897-4867-3Library of Congress Cataloging-in-Publication data is on file.Printed in the United States of AmericaFirst Printing: February 2013Editor-in-ChiefGreg WiegandExecutive EditorLoretta YatesDevelopment EditorCharlotte KughenManaging EditorSandra SchroederSenior Project EditorTonya SimpsonCopy EditorBart ReedIndexerCheryl LenserProofreaderMegan WadeTechnical EditorTrademarksBob UmlasAll terms mentioned in this book that are known to be trademarks or servicemarks have been appropriately capitalized. Que Publishing cannot attest tothe accuracy of this information. Use of a term in this book should not beregarded as affecting the validity of any trademark or service mark.Editorial AssistantExcel is a registered trademark of Microsoft Corporation.CompositorCindy TeetersBook DesignerAnne JonesBronkella PublishingWarning and DisclaimerEvery effort has been made to make this book as complete and as accurate aspossible, but no warranty or fitness is implied. The information provided is onan “as is” basis. The author and the publisher shall have neither liability norresponsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.Bulk SalesQue Publishing offers excellent discounts on this book when ordered in quantity for bulk purchases or special sales. For more information, please contactU.S. Corporate and Government or sales outside of the U.S., please contactInternational Salesinternational@pearsoned.com

ContentsIntroductionWhat’s in the Book . 2This Book’s Special Features . 2Part I: Mastering Excel Ranges and Formulas1 Getting the Most Out of Ranges . 5Advanced Range-Selection Techniques . 5Mouse Range-Selection Tricks. 6Keyboard Range-Selection Tricks . 7Working with 3D Ranges . 7Selecting a Range Using Go To. 8Using the Go To Special Dialog Box. 9Data Entry in a Range . 14Filling a Range . 14Using the Fill Handle . 15Flash-Filling a Range. 18Creating a Series . 20Advanced Range Copying . 22Copying Selected Cell Attributes. 22Combining Two Ranges Arithmetically . 23Transposing Rows and Columns . 24Clearing a Range. 25Applying Conditional Formatting to a Range . 25Creating Highlight Cells Rules . 26Creating Top/Bottom Rules. 28Adding Data Bars. 29Adding Color Scales. 32Adding Icon Sets . 33From Here . 352 Using Range Names . 37Defining a Range Name . 38Working with the Name Box . 38Using the New Name Dialog Box. 39Changing the Scope to Define Sheet-Level Names . 41Using Worksheet Text to Define Names . 41Naming Constants . 43Working with Range Names . 44Referring to a Range Name . 44Working with Name AutoComplete . 46Navigating Using Range Names. 47Pasting a List of Range Names in a Worksheet. 47

ivExcel 2013 Formulas and FunctionsDisplaying the Name Manager . 47Filtering Names . 48Editing a Range Name’s Coordinates . 48Adjusting Range Name Coordinates Automatically . 48Changing a Range Name . 50Deleting a Range Name. 50Using Names with the Intersection Operator. 50From Here . 513 Building Basic Formulas . 53Understanding Formula Basics . 53Formula Limits in Excel 2013 . 54Entering and Editing Formulas . 54Using Arithmetic Formulas. 55Using Comparison Formulas . 56Using Text Formulas . 57Using Reference Formulas . 57Understanding Operator Precedence. 57The Order of Precedence . 58Controlling the Order of Precedence . 58Controlling Worksheet Calculation. 60Copying and Moving Formulas . 62Understanding Relative Reference Format . 62Understanding Absolute Reference Format. 64Copying a Formula Without Adjusting Relative References . 64Displaying Worksheet Formulas . 65Displaying All Worksheet Formulas . 65Displaying a Cell’s Formula Using FORMULATEXT() . 65Converting a Formula to a Value .

Excel 2013 Formulas and Functions Contents at a Glance Introduction Part I Mastering Excel Ranges and Formulas 1 Getting the Most Out of Ranges . 4 Creating Advanced Formulas