Excel Formulas and Functions For Dummies (For Dummies (Computer/Tech))

389 Pages·2010·4.26 MB·english

by  Ken Bluttman

Making Everything Easier!™ 2nd Edition

mulas
E xcel ® For

and Func tions

Learn to:

(cid:129) Use Excel’s built-in formulas to make
everyday calculations

(cid:129) Determine what functions to use in
your formulas
(cid:129) Create amortization tables, calculate
classroom grades, or evaluate
investments

(cid:129) Design a database

Ken Bluttman

Peter Aitken
Get More and Do More at Dummies.com
®
Start with FREE Cheat Sheets
Cheat Sheets include

(cid:129) Checklists
(cid:129) Charts
(cid:129) Common Instructions
(cid:129) And Other Good Stuff!
To access the Cheat Sheet created specifically for this book, go to
www.dummies.com/cheatsheet/excelformulasfunctions
Get Smart at Dummies.com
Dummies.com makes your life easier with 1,000s
of answers on everything from removing wallpaper
to using the latest version of Windows.
Check out our
(cid:129) Videos
(cid:129) Illustrated Articles
(cid:129) Step-by-Step Instructions

Plus, each month you can win valuable prizes by entering

our Dummies.com sweepstakes. *

Want a weekly dose of Dummies? Sign up for Newsletters on
(cid:129) Digital Photography
(cid:129) Microsoft Windows & Office
(cid:129) Personal Finance & Investing
(cid:129) Health & Wellness

(cid:129) Computing, iPods & Cell Phones

(cid:129) eBay
(cid:129) Internet
(cid:129) Food, Home & Garden
Find out “HOW” at Dummies.com
*Sweepstakes not currently available in all countries; visit Dummies.com for official rules.
Excel® Formulas
and Functions
FOR
DUMmIES
‰
2
ND EDITION
Ken Bluttman and Peter Aitken
Excel® Formulas and Functions For Dummies®, 2nd Edition
Published by
Wiley Publishing, Inc.
111 River Street
Hoboken, NJ 07030-5774
www.wiley.com
Copyright © 2010 by Wiley Publishing, Inc., Indianapolis, Indiana
Published by Wiley Publishing, Inc., Indianapolis, Indiana
Published simultaneously in Canada
No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or
by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permit-
ted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written
permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the
Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone].
Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley
& Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://
www.wiley.com/go/permissions.
Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the
Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, Making Everything
Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/
or its affi liates in the United States and other countries, and may not be used without written permission.
Excel is a registered trademark of Microsoft Corporation in the United States and/or other countries. All
other trademarks are the property of their respective owners. Wiley Publishing, Inc. is not associated with
any product or vendor mentioned in this book.
LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: THE PUBLISHER AND THE AUTHOR MAKE NO
REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF
THE CONTENTS OF THIS WORK AND SPECIFICALLY DISCLAIM ALL WARRANTIES, INCLUDING WITH-
OUT LIMITATION WARRANTIES OF FITNESS FOR A PARTICULAR PURPOSE. NO WARRANTY MAY BE
CREATED OR EXTENDED BY SALES OR PROMOTIONAL MATERIALS. THE ADVICE AND STRATEGIES
CONTAINED HEREIN MAY NOT BE SUITABLE FOR EVERY SITUATION. THIS WORK IS SOLD WITH THE
UNDERSTANDING THAT THE PUBLISHER IS NOT ENGAGED IN RENDERING LEGAL, ACCOUNTING, OR
OTHER PROFESSIONAL SERVICES. IF PROFESSIONAL ASSISTANCE IS REQUIRED, THE SERVICES OF
A COMPETENT PROFESSIONAL PERSON SHOULD BE SOUGHT. NEITHER THE PUBLISHER NOR THE
AUTHOR SHALL BE LIABLE FOR DAMAGES ARISING HEREFROM. THE FACT THAT AN ORGANIZA-
TION OR WEBSITE IS REFERRED TO IN THIS WORK AS A CITATION AND/OR A POTENTIAL SOURCE OF
FURTHER INFORMATION DOES NOT MEAN THAT THE AUTHOR OR THE PUBLISHER ENDORSES THE
INFORMATION THE ORGANIZATION OR WEBSITE MAY PROVIDE OR RECOMMENDATIONS IT MAY
MAKE. FURTHER, READERS SHOULD BE AWARE THAT INTERNET WEBSITES LISTED IN THIS WORK
MAY HAVE CHANGED OR DISAPPEARED BETWEEN WHEN THIS WORK WAS WRITTEN AND WHEN IT
IS READ. FULFILLMENT OF EACH COUPON OFFER IS THE SOLE RESPONSIBILITY OF THE OFFEROR.
For general information on our other products and services, please contact our Customer Care
Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax [phone].
For technical support, please visit www.wiley.com/techsupport.
Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may
not be available in electronic books.
Library of Congress Control Number: 2010925695
ISBN: 978-0-470-56816-3
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
About the Authors
Ken Bluttman has been working as a software developer for nearly two
decades. Ken specializes in VB.Net/VBA/database/web applications. He has
written several articles on various computer topics including Offi ce/VBA
development, XML, SQL Server, JavaScript, PHP and creating Google Map
applications. He has a number of books out on Excel and Access, JavaScript,
Photoshop, and even a book on photography. Ken lives in Pennsylvania with
his wife, son, two cats, a rather large white tree frog, and a couple of geckos.
Peter Aitken has been writing about computers and programming for over
20 years, with over 45 books to his credit and over 1.5 million copies in
print. His recent book titles include Outlook 2007 Bible and Excel Pivot Tables
and Charts. Peter has also contributed hundreds of articles and product
reviews to magazines and Web sites such as Visual Developer, PC Magazine,
Microsoft Offi ce Pro, DevX, Builder.com, and DevSource.
Dedication
Dedicated to all the Excel users in the world! Keep up the good work. Also, I
wish to put a special note here for my son who has reached the age where he
thinks his dad is nerd (sad but true).
— Ken Bluttman
To my wife Maxine, for her unfl agging support and devotion.
— Peter Aitken
Authors’ Acknowledgments
Much activity goes on behind the scenes in bringing a book from idea to
reality. Many people are involved. We wish to thank the great Wiley staff —
Stephanie McComb, Beth Taylor, and everyone else on the Wiley team — for
all their hard work! Special thanks to Ed Lavieri.
— Peter G. Aitken and Ken Bluttman
Special thanks to my family for understanding that, at times, sitting in front
of a computer is a priority, even when it means I have to miss something spe-
cial. But darn I did miss watching some fun movies and shows (sigh). Special
thanks to Mom for her support.
— Ken Bluttman
Publisher’s Acknowledgments
We’re proud of this book; please send us your comments at http://dummies.custhelp.com. For
other comments, please contact our Customer Care Department within the U.S. at 877-762-2974, out-
side the U.S. at 317-572-3993, or fax [phone].
Some of the people who helped bring this book to market include the following:
Acquisitions, Editorial, and Composition Services
Media Development
Project Coordinator: Sheree Montgomery
Senior Acquisitions Editor: Stephanie McComb
Layout and Graphics: Christine Williams
Executive Editor: Jody Lefevere
Proofreader: Toni Settle
Project Editor: Beth Taylor
Indexer: BIM Indexing & Proofreading Services
Technical Editor: Ed Lavieri
Copy Editor: Beth Taylor
Editorial Director: Robyn Siesky
Business Manager: Amy Knies
Senior Marketing Manager: Sandy Smith
Vice President and Executive Group
Publisher: Richard Swadley
Vice President and Executive Publisher:
Barry Pruett
Cartoons: Rich Tennant
(www.the5thwave.com)
Publishing and Editorial for Technology Dummies
Richard Swadley, Vice President and Executive Group Publisher
Andy Cummings, Vice President and Publisher
Mary Bednarek, Executive Acquisitions Director
Mary C. Corder, Editorial Director
Publishing for Consumer Dummies
Diane Graves Steele, Vice President and Publisher
Composition Services
Debbie Stailey, Director of Composition Services
Table of Contents
Introduction ................................................................. 1
About This Book ..............................................................................................1
How to Use This Book .....................................................................................1
What You Can Safely Ignore ...........................................................................2
Foolish Assumptions .......................................................................................2
How This Book Is Organized ..........................................................................2
Part I: Putting the Fun in Functions .....................................................3
Part II: Doing the Math ..........................................................................3
Part III: Solving with Statistics ..............................................................3
Part IV: Working with Data ...................................................................3
Part V: The Part of Tens ........................................................................4
Icons Used in This Book .................................................................................4
Where to Go from Here ...................................................................................4
Part I: Putting the Fun in Functions ............................... 5
Chapter 1: Tapping into Formula and Function Fundamentals. . . . . . . .7
Working with Excel Fundamentals ................................................................8
Understanding workbooks and worksheets .......................................8
Introducing the Formulas Ribbon ......................................................11
Working with rows, column, cells, ranges, and tables ....................13
Formatting your data ...........................................................................17
Getting help ..........................................................................................18
Gaining the Upper Hand on Formulas .........................................................19
Entering your fi rst formula .................................................................20
Understanding references ..................................................................22
Copying formulas with the fi ll handle ...............................................24
Assembling formulas the right way ...................................................25
Using Functions in Formulas ........................................................................28
Looking at what goes into a function ................................................29
Arguing with a function .......................................................................30
Nesting functions .................................................................................34
Chapter 2: Saving Time with Function Tools. . . . . . . . . . . . . . . . . . . . . .37
Getting Familiar with the Insert Function Dialog Box ...............................37
Finding the Correct Function .......................................................................39
Entering Functions Using the Insert Function Dialog Box ........................40
Selecting a function that takes no arguments ..................................41
Selecting a function that uses arguments .........................................43
Entering cells, ranges, named areas, and
tables as function arguments .........................................................44
vi
Excel Formulas and Functions For Dummies, 2nd Edition
Getting help in the Insert Function dialog box.................................48
Using the Function Arguments dialog box to edit functions ..........49
Directly Entering Formulas and Functions .................................................49
Entering formulas and functions in the Formula Bar ......................49
Entering formulas and functions directly in worksheet cells ........51
Chapter 3: Saying “Array!” for Formulas and Functions. . . . . . . . . . . .55
Discovering Arrays ........................................................................................55
Using Arrays in Formulas .............................................................................57
Working with Functions That Return Arrays .............................................61
Chapter 4: Fixing Formula Boo-Boos . . . . . . . . . . . . . . . . . . . . . . . . . . . .65
Catching Errors as You Enter Them ...........................................................65
Getting parentheses to match ............................................................66
Avoiding circular references ..............................................................68
Mending broken links ..........................................................................70
Using the Formula Error Checker ......................................................72
Auditing Formulas .........................................................................................74
Watching the Watch Window .......................................................................77
Evaluating and Checking Errors ..................................................................78
Making an Error Behave the Way You Want ..............................................80
Part II: Doing the Math ............................................... 83
Chapter 5: Calculating Loan Payments and Interest Rates. . . . . . . . . .85
Understanding How Excel Handles Money ................................................86
Going with the cash fl ow .....................................................................86
Formatting for currency ......................................................................86
Choosing separators ...........................................................................88
Figuring Loan Calculations ...........................................................................90
Calculating the payment amount .......................................................91
Calculating interest payments ...........................................................93
Calculating payments toward principal ............................................94
Calculating the number of payments ................................................96
Calculating the interest rate ...............................................................98
Calculating the principal ...................................................................100
Chapter 6: Appreciating What You’ll Get,
Depreciating What You’ve Got . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .103
Looking into the Future ..............................................................................103
Depreciating the Finer Things in Life ........................................................106
Calculating straight line depreciation .............................................108
Creating an accelerated depreciation schedule ............................109
Creating an even faster accelerated depreciation schedule ........111
Calculating a mid-year depreciation schedule ...............................113
Measuring Your Internals ...........................................................................115
vii
Table of Contents
Chapter 7: Using Basic Math Functions. . . . . . . . . . . . . . . . . . . . . . . . .119
Adding It All Together with the SUM Function ........................................119
Rounding Out Your Knowledge ................................................................124
Just plain old rounding .....................................................................124
Rounding in one direction ................................................................126
Leaving All Decimals Behind with INT .....................................................131
Leaving Some Decimals Behind with TRUNC ..........................................133
Looking for a Sign ........................................................................................134
Ignoring Signs ...............................................................................................136
Chapter 8: Advancing Your Math. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .137
Using PI to Calculate Circumference and Diameter ................................137
Generating and Using Random Numbers .................................................138
Ordering Items .............................................................................................142
Combining ....................................................................................................143
Raising Numbers to New Heights ..............................................................144
Multiplying Multiple Numbers ...................................................................145
Using What Remains with the MOD Function ..........................................147
Summing Things Up ....................................................................................148
Using SUBTOTAL ...............................................................................148
Using SUMPRODUCT .........................................................................151
Using SUMIF and SUMIFS ..................................................................153
Getting an Angle on Trigonometry ............................................................155
Three basic trigonometry functions................................................155
Degrees and radians ..........................................................................156
Part III: Solving with Statistics ................................. 159
Chapter 9: Throwing Statistics a Curve . . . . . . . . . . . . . . . . . . . . . . . . .161
Stuck in the Middle with AVERAGE, MEDIAN, and MODE ......................162
Deviating from the Middle ..........................................................................167
Measuring variance ...........................................................................167
Analyzing deviations .........................................................................170
Looking for normal distribution.......................................................172
Skewed from the norm ......................................................................176
Comparing data sets ..........................................................................179
Analyzing Data with Percentiles and Bins ................................................183
QUARTILE.INC and QUARTILE.EXC .................................................183
PERCENTILE.INC and PERCENTILE.EXC .........................................185
RANK ...................................................................................................186
PERCENTRANK ...................................................................................188
FREQUENCY ........................................................................................189
MIN and MAX ......................................................................................192
LARGE and SMALL .............................................................................193
Going for the Count .....................................................................................195
COUNT .................................................................................................195
COUNTIF..............................................................................................196
viii
Excel Formulas and Functions For Dummies, 2nd Edition
Chapter 10: Using Signifi cance Tests. . . . . . . . . . . . . . . . . . . . . . . . . . .199
Testing to the T ............................................................................................200
Comparing Results to an Estimate ............................................................204
Chapter 11: Rolling the Dice on Predictions and Probability. . . . . . .209
Modeling .......................................................................................................209
Linear model .......................................................................................210
Exponential model .............................................................................210
Getting It Straight: Using SLOPE and INTERCEPT
to Describe Linear Data ...........................................................................211
What’s in the Future: Using FORECAST, TREND, and
GROWTH to Make Predictions ...............................................................214
FORECAST ...........................................................................................215
TREND .................................................................................................216
GROWTH .............................................................................................218
Using NORM.DIST and POISSON.DIST
to Determine Probabilities ......................................................................220
NORMDIST ..........................................................................................220
POISSON.DIST .....................................................................................222
Part IV: Working with Data ....................................... 227
Chapter 12: Dressing Up for Date Functions . . . . . . . . . . . . . . . . . . . . .229
Understanding How Excel Handles Dates ................................................229
Formatting Dates .........................................................................................231
Making a Date with DATE ...........................................................................232
Breaking a Date with DAY, MONTH, and YEAR .......................................234
Isolating the day .................................................................................234
Isolating the month ............................................................................236
Isolating the year ...............................................................................237
Converting a Date from Text ......................................................................237
Finding Out What TODAY Is .......................................................................239
Counting the days until your birthday ............................................239
Counting your age, in days ...............................................................240
Determining the Day of the Week ..............................................................240
Working with Workdays .............................................................................242
Determining workdays in a range of dates .....................................242
Workdays in the future .....................................................................244
Calculating Time between Two Dates with the DATEDIF Function ......244
Chapter 13: Keeping Well-Timed Functions . . . . . . . . . . . . . . . . . . . . .247
Understanding How Excel Handles Time .................................................247
Formatting Time ..........................................................................................248
Keeping TIME ...............................................................................................249
Text to Time with TIMEVALUE ..................................................................250