Excel Formulas and Functions For Dummies E-Book

Ken Bluttman

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

Seitenzahl: 473

Beschreibung

Grab these Excel formulas and functions to make your life easier! Are you intimidated by major financial choices, like which loan to get or how to grow your savings? Don't worry--we all are! But Excel Formulas & Functions For Dummies, 4th Edition can take some of the pain out of the data organization and analysis processes. This step-by-step reference sheds light on Microsoft Excel's 150 most useful functions, and offers detailed instructions on how to implement them. Additionally, each function is illustrated by helpful, real-world examples that show how they are used within a larger formula. To take your knowledge of Excel's functions a step further, 85 specialized functions are described in abbreviated form so you can use Excel to better support your decision-making process when securing a mortgage, buying a car, computing classroom grades, evaluating investment performance, and more. Functions are predefined formulas that you can use to make data analysis a bit easier within the Microsoft Excel framework. Functions use specific values, called arguments, to calculate a variety of things, from simple sums and averages to more complicated loan payments. * Explore the 150 most useful functions that help Microsoft Excel make your life easier * Access real-world examples of how functions fit into larger formulas * Discover 85 specialized functions, which are described in abbreviated form and take your knowledge of Excel to the next level * Understand how Microsoft Excel can help you make key decisions, such as whether to go with a 15-year or 30-year mortgage Excel Formulas & Functions For Dummies, 4th Edition brings order to chaotic data--and helps you make decisions with confidence!

Leseprobe

Microsoft® Excel® Formulas & Functions For Dummies®, 4th Edition

Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030-5774, www.wiley.com

Copyright © 2016 by John Wiley & Sons, Inc., Hoboken, New Jersey

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 the prior written permission of the Publisher. 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, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be used without written permission. Excel is a registered trademark of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, 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 publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Control Number: 2015955844

ISBN: 978-1-119-07678-0 (pbk); 978-1-119-07680-3 (ebk); 978-1-119-07679-7 (ebk)

Microsoft® Excel® Formulas & Functions For Dummies®

Visit http://www.dummies.com/cheatsheet/excelformulasfunctions to view this book's cheat sheet.

Table of Contents

Cover

Introduction

About This Book

Foolish Assumptions

How to Use This Book

Icons Used in This Book

Beyond the Book

Where to Go from Here

Part I: Getting Started with Formulas and Functions

Chapter 1: Tapping Into Formula and Function Fundamentals

Working with Excel Fundamentals

Gaining the Upper Hand on Formulas

Using Functions in Formulas

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

Directly Entering Formulas and Functions

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

Discovering Arrays

Using Arrays in Formulas

Working with Functions That Return Arrays

Chapter 4: Fixing Formula Boo-Boos

Catching Errors As You Enter Them

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

Figuring Loan Calculations

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

Looking into the Future

Depreciating the Finer Things in Life

Measuring Your Internals

Chapter 7: Using Basic Math Functions

Adding It All Together with the SUM Function

Rounding Out Your Knowledge

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

Getting an Angle on Trigonometry

Part III: Solving with Statistics

Chapter 9: Throwing Statistics a Curve

Getting Stuck in the Middle with AVERAGE, MEDIAN, and MODE

Deviating from the Middle

Analyzing Data with Percentiles and Bins

Going for the Count

Chapter 10: Using Significance Tests

Testing to the T

Comparing Results with an Estimate

Chapter 11: Rolling the Dice on Predictions and Probability

Modeling

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

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

Using NORM.DIST and POISSON.DIST to Determine Probabilities

Part IV: Dancing 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

Converting a Date from Text

Finding Out What TODAY Is

Determining the Day of the Week

Working with Workdays

Calculating Time between Two Dates with the DATEDIF Function

Chapter 13: Keeping Well-Timed Functions

Understanding How Excel Handles Time

Formatting Time

Keeping TIME

Converting Text to Time with TIMEVALUE

Deconstructing Time with HOUR, MINUTE, and 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

Finding Where It Is

Looking It Up

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

Getting to Know Your Type

Chapter 16: Writing Home about Text Functions

Breaking Apart Text

Putting Text Together with CONCATENATE

Changing Text

Comparing, Finding, and Measuring Text

Chapter 17: Playing Records with Database Functions

Putting Your Data into a Database Structure

Working with Database Functions

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

Being Productive with DPRODUCT

Part V: The Part of Tens

Chapter 18: Ten T ips for Working with Formulas

Master Operator Precedence

Display Formulas

Fix 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

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

Easily Generate a Random Number

Convert to Roman Numerals

Factor in a Factorial

Determine Part of a Year with YEARFRAC

Find the Data TYPE

About the Author

Cheat Sheet

Advertisement Page

Connect with Dummies

End User License Agreement

Guide

Cover

Table of Contents

Begin Reading

Pages

iii

iv

vii

viii

ix

x

xi

xii

xiii

1

2

3

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

199

200

201

202

203

204

205

206

207

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

249

250

251

252

253

254

255

256

257

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

387

388

391

392

393

394

Introduction

Excel worksheets are used in many walks of life: business, education, home finances, and even hobbies (keeping track of your baseball-card collection). In my house, we use Excel for a lot, from our taxes (boring!) to our ever-growing recipe collection (yummy!). Often, I use Excel in place of a calculator. After all, Excel is like a calculator on steroids!

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 capability to manipulate and give feedback about the data makes it attractive. Excel’s flexibility in storing and presenting data is like magic.

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, I assemble them by category and provide real examples of how to use them alone, and in formulas, along with step-by-step instructions and illustrations of the results.

Foolish Assumptions

I assume that you have a PC with Excel 2016 loaded. That’s a no-brainer! Nearly all the material is relevant for use with earlier versions of Excel as well. I also assume that you know how to navigate with a keyboard and mouse. Last, I assume that you have used Excel before, even just once. I do discuss basics in Chapter 1, but not all of them. If you really need to start from scratch, I suggest that you read the excellent Excel 2016 For Dummies, by Greg Harvey (John Wiley & Sons, Inc.).

Other than that, this book is written for Excel 2016, but just between you and me, it works 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 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. I 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.

Icons Used in This Book

A Tip gives you a little extra piece of info on the subject at hand. It may offer an alternative 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. This icon tells 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 items or ignore them as you see fit.

Beyond the Book

This section describes where readers can find book content that exists outside the book itself. A For Dummies technical book may include the following, although only rarely does a book include all these items:

Cheat Sheet:

In a rush? The Excel Formulas and Functions Cheat Sheet at

www.dummies.com/cheatsheet/excelformulasfunctions

is the super-duper fast way to the basics. On the Cheat Sheet you will find the top functions, the ever-important order of operations, and what those non-friendly Excel errors mean!

Dummies.com online articles:

Did you think I would leave you hanging without some extra material? Fear not! I have provided a few online articles to give your formulas and functions knowledge an extra lift! Find the articles at

www.dummies.com/extras/excelformulasfunctions

.

Updates:

I pour my heart and soul into my books — and so do the slew of editors working with me — yet still things can go awry. If there are updates or important changes, find them at

www.dummies.com/extras/excelformulasfunctions

.

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's 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

Getting Started with Formulas and Functions

Read more about Excel at www.dummies.com/extras/excelformulasfunctions.

In this part …

Get to know formula and function fundamentals.

Discover the different ways to enter functions.

Understand array-based formulas and functions.

Find out about formula errors and how to fix them.

Chapter 1

Tapping Into Formula and Function Fundamentals

In This Chapter

Getting the skinny on the Excel basics

Writing formulas

Working with functions in formulas

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 2016, a single worksheet has 17,179,869,184 places to hold data. Yes, that’s what I 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!