Excel Formulas and Functions For Dummies E-Book

Ken Bluttman

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch
• Veröffentlichungsjahr: 2010

Seitenzahl: 464

Beschreibung

Put the power of Excel formulas and functions to work foryou! Excel is a complex program. Mastering the use of formulas andfunctions lets you use Excel to compute useful day-to-dayinformation, such as calculating the true cost of credit cardpurchases or comparing 15-year and 30-year mortgage costs. This funand friendly book demystifies Excel's built-in functions so you canput them to work. You'll find step-by-step instructions on 150 of Excel's mostuseful functions, how they work within formulas, and how to usethem to make your life easier. * See how to use 150 of Excel's most useful functions, withreal-world examples showing how each function is used within aformula * Learn to calculate the costs of leasing versus buying a car,compute classroom grades, create an amortization table, or evaluateinvestment performance * Fully updated for Excel 2010, but the principles will work withearlier versions of Excel as well * Includes essential coverage of an additional 85 functions In the ever-popular, non-threatening For Dummies style, ExcelFormulas and Functions For Dummies, 2nd Edition makes Excel'spower accessible to you.

Leseprobe

Excel® Formulas and Functions For Dummies®, 2nd Edition

Table of Contents

Introduction

About This Book

How to Use This Book

What You Can Safely Ignore

Foolish Assumptions

How This Book Is Organized

Part I: Putting the Fun in Functions

Part II: Doing the Math

Part III: Solving with Statistics

Part IV: Working with Data

Part V: The Part of Tens

Icons Used in This Book

Where to Go from Here

Part I: Putting the Fun in Functions

Chapter 1: Tapping into Formula and Function Fundamentals

Working with Excel Fundamentals

Understanding workbooks and worksheets

Introducing the Formulas Ribbon

Working with rows, column, cells, ranges, and tables

Formatting your data

Getting help

Gaining the Upper Hand on Formulas

Entering your first formula

Understanding references

Copying formulas with the fill handle

Assembling formulas the right way

Using Functions in Formulas

Looking at what goes into a function

Arguing with a function

Nesting functions

Chapter 2: Saving Time with Function Tools

Getting Familiar with the Insert Function Dialog Box

Finding the Correct Function

Entering Functions Using the Insert Function Dialog Box

Selecting a function that takes no arguments

Selecting a function that uses arguments

Entering cells, ranges, named areas, and tables as function arguments

Getting help in the Insert Function dialog box

Using the Function Arguments dialog box to edit functions

Directly Entering Formulas and Functions

Entering formulas and functions in the Formula Bar

Entering formulas and functions directly in worksheet cells

Chapter 3: Saying “Array!” for Formulas and Functions

Discovering Arrays

Using Arrays in Formulas

Chapter 4: Fixing Formula Boo-Boos

Catching Errors as You Enter Them

Getting parentheses to match

Avoiding circular references

Mending broken links

Using the Formula Error Checker

Auditing Formulas

Watching the Watch Window

Evaluating and Checking Errors

Making an Error Behave the Way You Want

Part II: Doing the Math

Chapter 5: Calculating Loan Payments and Interest Rates

Understanding How Excel Handles Money

Going with the cash flow

Formatting for currency

Choosing separators

Figuring Loan Calculations

Calculating the payment amount

Calculating interest payments

Calculating payments toward principal

Calculating the number of payments

Calculating the interest rate

Calculating the principal

Chapter 6: Appreciating What You’ll Get, Depreciating What You’ve Got

Looking into the Future

Depreciating the Finer Things in Life

Calculating straight line depreciation

Creating an accelerated depreciation schedule

Creating an even faster accelerated depreciation schedule

Calculating a mid-year depreciation schedule

Measuring Your Internals

Chapter 7: Using Basic Math Functions

Adding It All Together with the SUM Function

Rounding Out Your Knowledge

Just plain old rounding

Rounding in one direction

Leaving All Decimals Behind with INT

Leaving Some Decimals Behind with TRUNC

Looking for a Sign

Ignoring Signs

Chapter 8: Advancing Your Math

Using PI to Calculate Circumference and Diameter

Generating and Using Random Numbers

Ordering Items

Combining

Raising Numbers to New Heights

Multiplying Multiple Numbers

Using What Remains with the MOD Function

Summing Things Up

Using SUBTOTAL

Using SUMPRODUCT

Using SUMIF and SUMIFS

Getting an Angle on Trigonometry

Three basic trigonometry functions

Degrees and radians

Part III: Solving withStatistics

Chapter 9: Throwing Statistics a Curve

Stuck in the Middle with AVERAGE, MEDIAN, and MODE

Deviating from the Middle

Measuring variance

Analyzing deviations

Looking for normal distribution

Skewed from the norm

Comparing data sets

Analyzing Data with Percentiles and Bins

QUARTILE.INC and QUARTILE.EXC

PERCENTILE.INC and PERCENTILE.EXC

RANK

PERCENTRANK

FREQUENCY

MIN and MAX

LARGE and SMALL

Going for the Count

COUNT

COUNTIF

Chapter 10: Using Significance Tests

Testing to the T

Comparing Results to an Estimate

Chapter 11: Rolling the Dice on Predictions and Probability

Modeling

Linear model

Exponential model

Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data

What’s in the Future: Using FORECAST, TREND, and GROWTH to Make Predictions

FORECAST

TREND

GROWTH

Using NORM.DIST and POISSON.DIST to Determine Probabilities

NORMDIST

POISSON.DIST

Part IV: Working with Data

Chapter 12: Dressing Up for Date Functions

Understanding How Excel Handles Dates

Formatting Dates

Making a Date with DATE

Breaking a Date with DAY, MONTH, and YEAR

Isolating the day

Isolating the month

Isolating the year

Converting a Date from Text

Finding Out What TODAY Is

Counting the days until your birthday

Counting your age, in days

Determining the Day of the Week

Working with Workdays

Determining workdays in a range of dates

Workdays in the future

Calculating Time between Two Dates with the DATEDIF Function

Chapter 13: Keeping Well-Timed Functions

Understanding How Excel Handles Time

Formatting Time

Keeping TIME

Text to Time with TIMEVALUE

Deconstructing Time with HOUR, MINUTE, and SECOND

Isolating the hour

Isolating the minute

Isolating the second

Finding the Time NOW

Calculating Elapsed Time over Days

Chapter 14: Using Lookup, Logical, and Reference Functions

Testing on One Condition

Choosing the Right Value

Let’s Be Logical

NOT

AND and OR

Finding Where It Is

ADDRESS

ROW, ROWS, COLUMN, and COLUMNS

OFFSET

Looking It Up

HLOOKUP and VLOOKUP

MATCH

Chapter 15: Digging Up the Facts

Getting Informed with the CELL Function

Getting Information about Excel and Your Computer System

Finding What IS and What IS Not

ISERR, ISERROR, and ISNA

ISBLANK, ISNONTEXT, ISTEXT, and ISNUMBER

Getting to Know Your Type

Chapter 16: Writing Home about Text Functions

Breaking Apart Text

Bearing to the LEFT

Swinging to the RIGHT

Staying in the MIDdle

Finding the long of it with LEN

Putting Text Together with CONCATENATE

Changing Text

Making money

Turning numbers into text

Repeating text

Swapping text

Giving text a trim

Making a case

Comparing, Finding, and Measuring Text

Going for perfection with EXACT

Finding and searching

Chapter 17: Playing Records with Database Functions

Putting Your Data into a Database Structure

Working with Database Functions

Establishing your database

Establishing the criteria area

Fine-Tuning Criteria with AND and OR

Adding Only What Matters with DSUM

Going for the Middle with DAVERAGE

Counting Only What Matters with DCOUNT

Finding Highest and Lowest with DMIN and DMAX

Finding Duplicate Values with DGET

Part V: The Part of Tens

Chapter 18: Ten Tips for Working with Formulas

Operator Precedence

Display Formulas

Fixing Formulas

Use Absolute References

Turn Calc On/Turn Calc Off

Use Named Areas

Use Formula Auditing

Use Conditional Formatting

Use Data Validation

Create Your Own Functions

Chapter 19: Ten Functions You Really Should Know

SUM

AVERAGE

COUNT

INT and ROUND

INT

ROUND

IF

NOW and TODAY

HLOOKUP and VLOOKUP

ISNUMBER

MIN and MAX

SUMIF and COUNTIF

Chapter 20: Some Really Cool Functions

Work with Hexadecimal, Octal, Decimal, and Binary Numbers

Convert Units of Measurement

Find the Greatest Common Divisor and the Least Common Multiple

Easy Random Number Generation

Converting to Roman Numerals

Factoring in a Factorial

Determining Part of a Year with YEARFRAC

Excel® Formulas and Functions For Dummies®, 2nd Edition

by Ken Bluttman and Peter Aitken

Excel® Formulas and Functions For Dummies®, 2nd Edition

Published byWiley Publishing, Inc.111 River St.Hoboken, NJ 07030-5774www.wiley.com

Copyright © 2010 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 permitted 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 affiliates 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 without 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 organization 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.

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 Office/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 Office 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 unflagging 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 special. 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 through our online registration form located at http://dummies.custhelp.com.

Some of the people who helped bring this book to market include the following:

Acquisitions, Editorial, and Media Development

Senior Project Editor: Stephanie McComb

Executive Editor: Jody Lefevere

Project Editor: Beth Taylor

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)

Composition Services

Project Coordinator: Sheree Montgomery

Layout and Graphics: Christine Williams

Proofreaders: Dwight Ramsey, Toni Settle

Indexer: BIM Indexing & Proofreading Services

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

Introduction

Spreadsheets are such a mainstay in today’s world; the term is almost a household word. Certainly in our homes it is. And we don’t just mean because we write books on Excel. Spouses use Excel to track household expenditures and insurance claims. A neighbor analyzes his eBay transactions in Excel. Our local merchant keeps tabs on his store’s inventory on a worksheet.

In the workplace, Excel is one of the most commonly used analysis and reporting tools. Financial statements, sales reports, inventory, project scheduling, customer activity — so much of this stuff is kept in Excel. The program’s ability to manipulate and give feedback about the data makes it attractive.

About This Book

This book is about the number-crunching side of Excel. Formulas are the keystone to analyzing data — that is, digging out nuggets of important information. What is the average sale? How many times did we do better than average? How many days are left on the project? How much progress have we made? That sort of thing.

Formulas calculate answers, straight and to the point. But that’s not all. Excel has dozens of built-in functions that calculate everything from a simple average to a useful analysis of your investments to complex inferential statistics. But you don’t have to know it all or use it all; just use the parts that are relevant to your work.

This book discusses more than 150 of these functions. But rather than just show their syntax and list them alphabetically, we have assembled them by category and provided real examples of how to use them alone, and in formulas, along with step-by-step instructions and illustrations of the results.

How to Use This Book

You do not have to read the book sequentially from start to finish, although you certainly can. Each chapter deals with a specific category of functions — financial in one chapter, statistical in another, and so on. Some categories are split over two or more chapters. We suggest two ways for you to use this book:

Use the Table of Contents to find the chapters that are of interest to you.

Use the Index to look up specific functions you are interested in.

What You Can Safely Ignore

If you already know what function you want to use and need a bit of guidance on it, you can find it in the index and ignore any other discussion that is in the same chapter (although it makes us sad to think of that). Just kidding!

You can ignore any info in the Technical Stuff icons. You can also ignore Chapter 1 if you are already a fairly competent Excel user — especially if you have used formulas and functions.

Foolish Assumptions

We assume you have a PC with Excel 2010 loaded on it. That’s a no brainer! We also assume you know how to navigate with a keyboard and mouse. Lastly, we assume you have used Excel before, even just once. We do discuss basics in Chapter 1, but not all of them. If you really need to start from scratch, we suggest you read the excellent Excel 2010 For Dummies by Greg Harvey (Wiley).

Other than that, this book is written for Excel 2010 but just between you and us — it works just fine with older versions of Excel. There could be a function or two that isn’t in an older version or works slightly differently. But Microsoft has done an excellent job of maintaining compatibility between versions of Excel, so when it comes to formulas and functions, you can be confident that what works in one version works in another.

How This Book Is Organized

This book is organized into five parts. Each part’s subject matter indicates what type of functions it covers. Use the index to find the page numbers for particular functions. Do read chapters that pertain to your interest. For all you know, other functions are even better suited to your needs. With that said, dig in to what is inside each part of the book.

Part I: Putting the Fun in Functions

Part I is introductory, of course, but not all of it is so basic. Chapter 1 is the de facto intro chapter. That’s where you can brush up on how Excel works, or read about it for the first time. We discuss the Formulas tab of the Ribbon in Chapter 1. Chapters 2, 3, and 4 cover what is likely to be new ground to many readers — specifically, using the Insert Function dialog box, using array functions, and correcting formulas. Looking through these chapters can help you down the road.

Part II: Doing the Math

As the name implies, Part II is all about math. The first chapters cover finance-related functions and last two are plain old math – simple to sophisticated. Several functions in Excel work with loan factors, interest rates, and returns on investments. This is the place to go when creating worksheets that track costs, revenue, and the like. Part of the discussion in Part II is about currency formatting.

Part III: Solving with Statistics

Part III is rather large because it covers a rather sizeable topic. Chapters 9, 10, and 11 show you how to work with statistical functions. Each of these chapters focuses on a specific discipline. Chapter 9 covers the functions used in descriptive statistics, such as the ever-popular AVERAGE function, along with many related functions that give details about your data. Chapter 10 focuses on significance tests. Chapter 11 rounds out the statistical functions with those used in predicting factors. This is where you read about forecasting and looking for trends.

Part IV: Working with Data

Part IV is a biggie. Here is where you read about working with dates and times; how to work with strings of text; and how to pluck out pieces of data from a database (an area of rows and columns, that is). Chapters 12 and 13 are the date and time chapters. Chapter 14 covers a number of cool functions, such as the amazing IF and the workhorses HLOOKUP and VLOOKUP. Chapter 15 explains functions that provide information about your data and computer; it also explains working with errors. Chapter 16 is all about strings. No, I don’t mean the kind to fly kites. A string is a text value, and there is so much you can do to manipulate them. Lastly, Chapter 17 explains all the database functions.

Part V: The Part of Tens

And then there’s the Part of Tens — a For Dummies tradition if ever there was one. In Part V, we have included three chapters: tips for working with formulas; the most popular functions; and finally, really cool functions to go to town with.

Icons Used in This Book

A Tip gives you a little extra piece of info on the subject at hand. It may offer an alternate method. It may lead you to a conclusion. It may, well, give you a tip (just no stock tips — sorry).

The Remember icon holds some basic concept that is good to keep tucked somewhere in your brain.

As it implies, a Warning is serious stuff. These icons tell you to be careful — usually because you can accidentally erase your data or some such horrible event.

Once in a while, some tidbit is interesting to the tech-head types, but not to anyone else. You can read these or ignore them as you see fit.

Where to Go from Here

Roll up your sleeves, take a deep breath, and then forget all that preparing-for-a-hard-task stuff. Using Excel is easy. You can hardly make a mistake without Excel catching it. If you need to brush up on the basics, go to Chapter 1. This chapter is also the best place to get your first taste of formulas and functions. After that, it’s up to you. The book is organized more by area of focus than anything else. If finance is what you do, go to Part II. If working with dates is what you do, go to Part IV. Seek and you will find.

Part I

Putting the Fun in Functions

In this part . . .

We cover the basics, as any good Part I of a book should. Here is the place to get familiar with the fundamentals of Excel. Chapter 1 is a quick study in workbooks, worksheets, formulas, and functions — everything you need to get going! If you’re a beginner or need to brush up on Excel, then Chapter 1 is the place to start. But that’s not all, folks. Chapter 2 tells you about a key dialog box that makes working with functions much easier. That enables you to concentrate on more important details, such as where you are going on your lunch break. Part I finishes with a one-two punch of using arrays and correcting formulas.

Chapter 1

Tapping into Formula and Function Fundamentals

In This Chapter

Getting the skinny on workbooks and worksheets

Understanding the parts of a worksheet

Working with cells, ranges, named areas, and tables

Applying formatting

Figuring out how to use the Help system

Writing formulas

Using functions in formulas

Using nested functions

Excel is to computer programs what a Ferrari is to cars: sleek on the outside and a lot of power under the hood. Excel is also like a truck — it can handle all your data, lots of it. In fact, in Excel 2010, a single worksheet has 17,179,869,184 places to hold data. Yes, that’s what we said — more than 17 billion data placeholders. And that’s on just one worksheet!

Opening files created in earlier versions of Excel may show just the number of worksheet rows and columns available in the version the workbook was created with.

Excel is used in all types of businesses. And you know how that’s possible? By being able to store and work with any kind of data. It doesn’t matter whether you’re in finance or sales, whether you run an online video store or organize wilderness trips, or whether you’re charting party RSVPs or tracking the scores of your favorite sports teams — Excel can handle all of it. Its number-crunching ability is just awesome! And so easy to use!

Just putting a bunch of information on worksheets doesn’t crunch the data or give you sums, results, or analyses. If you want to just store your data somewhere, you can use Excel or get a database program instead. In this book, we show you how to build formulas and how to use the dozens of built-in functions that Excel provides. That’s where the real power of Excel is — making sense of your data.

Don’t fret that this is a challenge and that you may make mistakes. We did when we were ramping up. Besides, Excel is very forgiving. It won’t crash on you. Excel usually tells you when you made a mistake, and sometimes it even helps you to correct it. How many programs do that!? But first the basics. This first chapter gives you the springboard you need to use the rest of the book. We wish books like this were around when we were introduced to computers. We had to stumble through a lot of this.

Working with Excel Fundamentals

Before you can write any formulas or crunch any numbers, you have to know where the data goes. And how to find it again. We wouldn’t want your data to get lost! Knowing how worksheets store your data and present it is critical to your analysis efforts.