Microsoft Office Excel 2007 Formulas and

Make Excel do the math and make sense of your data Use the Insert Function dialog box, array formulas and functions, and more Excel 2007 has more than 500 built-in functions. This book looks at the top 150, so you can find out which ones will make your life easier. Want to compare a 15-year mortgage to a 30-year mortgage? Forecast expenses for your college freshman? See how your online business is doing? Here's the fun and easy way! Discover how to * Create worksheets to track costs and revenue * Tell the difference among average, median, and mode * Work with statistical functions * Develop forecasts and track trends * Manipulate strings and work with database functions

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, and InfoPath. He has a number of books out on Excel and Access. Ken lives in New York with his wife, son, dog, hamster, some frogs, and a couple of geckos.

Peter Aitken has been writing about computers and programming for over 15 years. He has more than 45 books to his credit with over 1.5 million copies in print, and also has extensive experience writing software documentation, online help, and magazine and trade-publication articles. Some recent book titles are Managing Your Money and Investment with Excel, Powering Office XP with XML, Excel PivotTables and Charts, and Visual Basic.NET Programming with Peter Aitken. He is the proprietor of PGA Consulting, providing custom application development and technical writing services since 1994.

Introduction	p. 1
About This Book	p. 1
How to Use This Book	p. 1
What You Can Safely Ignore	p. 2
Foolish Assumptions	p. 2
How This Book Is Organized	p. 2
Putting the Fun in Functions	p. 3
Counting On Your Money	p. 3
Doing the Math	p. 3
Working with Data	p. 3
The Part of Tens	p. 4
Icons Used In This Book	p. 4
Where to Go from Here	p. 4
Putting the Fun in Functions	p. 5
Tapping into Formula and Function Fundamentals	p. 7
Working with Excel Fundamentals	p. 8
Understanding workbooks and worksheets	p. 8
Introducing the Formulas Ribbon	p. 11
Working with rows, column, cells, ranges, and tables	p. 13
Formatting your data	p. 18
Getting help	p. 19
Gaining the Upper Hand on Formulas	p. 19
Entering your first formula	p. 20
Understanding references	p. 23
Copying formulas with the fill handle	p. 25
Assembling formulas the right way	p. 25
Using Functions in Formulas	p. 28
Looking at what goes into a function	p. 30
Arguing with a function	p. 30
Nesting functions	p. 33
Saving Time with Function Tools	p. 37
Getting Familiar with the Insert Function Dialog Box	p. 37
Finding the Correct Function	p. 39
Entering Functions Using the Insert Function Dialog Box	p. 40
Selecting a function that takes no arguments	p. 40
Selecting a function that uses arguments	p. 42
Entering cells, ranges, named areas, and tables as function arguments	p. 45
Getting help in the Insert Function dialog box	p. 48
Using the Function Arguments dialog box to edit functions	p. 48
Directly Entering Formulas and Functions	p. 49
Entering formulas and functions in the Formula Bar	p. 49
Entering formulas and functions directly in worksheet cells	p. 50
Saying "Array!" for Formulas and Functions	p. 53
Discovering Arrays	p. 53
Using Arrays in Formulas	p. 55
Working with Functions That Return Arrays	p. 58
Fixing Formula Boo-Boos	p. 63
Catching Errors as You Enter Them	p. 63
Getting parentheses to match	p. 64
Avoiding circular references	p. 66
Mending broken links	p. 68
Using the Formula Error Checker	p. 70
Auditing Formulas	p. 72
Watching the Watch Window	p. 75
Evaluating and Checking Errors	p. 76
Making an Error Behave the Way You Want	p. 78
Counting on your Money	p. 79
Calculating Loan Payments and Interest Rates	p. 81
Understanding How Excel Handles Money	p. 82
Going with the cash flow	p. 82
Formatting for currency	p. 82
Choosing separators	p. 84
Figuring Loan Calculations	p. 86
Calculating the payment amount	p. 87
Calculating interest payments	p. 88
Calculating payments toward principal	p. 90
Calculating the number of payments	p. 91
Calculating the interest rate	p. 93
Calculating the principal	p. 95
Appreciating What You'll Get, Depreciating What You Got	p. 97
Looking into the Future	p. 97
Depreciating the Finer Things in Life	p. 100
Calculating straight line depreciation	p. 102
Creating an accelerated depreciation schedule	p. 103
Creating an even faster accelerated deprecation schedule	p. 104
Calculating a mid-year depreciation schedule	p. 106
Measuring Your Internals	p. 108
Using Basic Math Functions	p. 113
Adding It All Together with the SUM Function	p. 113
Rounding Out Your Knowledge	p. 118
Just plain old rounding	p. 118
Rounding in one direction	p. 120
Leaving All Decimals Behind with Int	p. 125
Leaving Some Decimals Behind with Trunc	p. 127
Looking for a Sign	p. 128
Ignoring Signs	p. 129
Advancing Your Math	p. 131
Using PI to Calculate Circumference and Diameter	p. 131
Generating and Using Random Numbers	p. 132
Ordering Items	p. 136
Combining	p. 137
Raising Numbers to New Heights	p. 138
Multiplying Multiple Numbers	p. 139
Using What Remains with the Mod Function	p. 141
Summing Things Up	p. 142
Using Subtotal	p. 142
Using Sumproduct	p. 144
Using Sumif	p. 146
Throwing Statistics a Curve	p. 149
Stuck in the Middle with Average, Median, and Mode	p. 150
Deviating from the Middle	p. 154
Measuring variance	p. 155
Analyzing deviations	p. 157
Looking for normal distribution	p. 159
Skewed from the norm	p. 164
Comparing data sets	p. 166
Analyzing Data with Percentiles and Bins	p. 170
Quartile	p. 170
Percentile	p. 171
Rank	p. 173
Percentrank	p. 174
Frequency	p. 175
Min and Max	p. 178
Large and Small	p. 179
Going for the Count	p. 181
Count	p. 181
Countif	p. 182
Using Significance Tests	p. 185
Testing to the T	p. 186
Comparing Results to an Estimate	p. 190
Doing the Math	p. 195
Rolling the Dice on Predictions and Probability	p. 197
Modeling	p. 197
Linear model	p. 198
Exponential model	p. 198
Getting It Straight: Using Slope and Intercept to Describe Linear Data	p. 199
What's in the Future: Using Forecast, Trend, and Growth to Make Predictions	p. 202
Forecast	p. 203
Trend	p. 204
Growth	p. 206
Using Normdist and Poisson to Determine Probabilities	p. 208
Normdist	p. 208
Poisson	p. 210
Dressing Up for Date Functions	p. 215
Understanding How Excel Handles Dates	p. 215
Formatting Dates	p. 217
Making a Date with Date	p. 218
Breaking a Date with Day, Month, and Year	p. 219
Isolating the day	p. 219
Isolating the month	p. 221
Isolating the year	p. 222
Converting a Date from Text	p. 223
Finding Out What Today Is	p. 224
Counting the days until your birthday	p. 225
Counting your age, in days	p. 225
Determining the Day of the Week	p. 226
Working with Workdays	p. 227
Determining workdays in a range of dates	p. 228
Workdays in the future	p. 229
Calculating Time between Two Dates with the Datedif Function	p. 230
Keeping Well-Timed Functions	p. 233
Understanding How Excel Handles Time	p. 233
Formatting Time	p. 234
Keeping Time	p. 235
Text to Time with TimeValue	p. 236
Deconstructing Time with Hour, Minute, and Second	p. 236
Isolating the hour	p. 237
Isolating the minute	p. 238
Isolating the second	p. 239
Finding the Time Now	p. 239
Calculating Elapsed Time over Days	p. 240
Using Lookup, Logical, and Reference Functions	p. 241
Testing on One Condition	p. 242
Choosing the Right Value	p. 247
Let's Be Logical	p. 248
Not	p. 249
And and Or	p. 250
Finding Where It Is	p. 252
Address	p. 252
Row, Rows, Column, and Columns	p. 256
Offset	p. 258
Looking It Up	p. 259
Hlookup and Vlookup	p. 260
Match	p. 263
Digging Up the Facts	p. 269
Getting Informed with the Cell Function	p. 269
Getting Information about Excel and Your Computer System	p. 274
Finding What Is and What Is Not	p. 276
Iserr, Iserror, and Isna	p. 277
Isblank, Isnontext, Istext, and Isnumber	p. 278
Getting to Know Your Type	p. 280
Working with Data	p. 283
Writing Home about Text Functions	p. 285
Breaking Apart Text	p. 285
Bearing to the Left	p. 286
Swinging to the Right	p. 287
Staying in the Middle	p. 288
Finding the long of it with Len	p. 289
Putting Text Together with Concatenate	p. 290
Changing Text	p. 292
Making money	p. 292
Turning numbers into text	p. 294
Repeating text	p. 296
Swapping text	p. 297
Giving text a trim	p. 301
Making a case	p. 302
Comparing, Finding, and Measuring Text	p. 304
Going for perfection with Exact	p. 304
Finding and searching	p. 305
Playing Records with Database Functions	p. 311
Putting Your Data into a Database Structure	p. 311
Working with Database Functions	p. 312
Establishing your database	p. 313
Establishing the criteria area	p. 314
Fine-tuning Criteria with And and Or	p. 316
Adding Only What Matters with Dsum	p. 318
Going for the Middle with Daverage	p. 319
Counting Only What Matters with Dcount	p. 320
Finding Highest and Lowest with Dmin and Dmax	p. 321
Finding Duplicate Values with Dget	p. 322
The Part of Tens	p. 323
Ten-Plus Tips for Working with Formulas	p. 325
Operator Precedence	p. 325
Display Formulas	p. 326
Fixing Formulas	p. 327
Use Absolute References	p. 328
Turn Calc On/Turn Calc Off	p. 329
Use Named Areas	p. 330
Use Formula Auditing	p. 331
Use Conditional Formatting	p. 332
Use the Conditional Sum Wizard	p. 333
Use the Lookup Wizard	p. 334
Create Your Own Functions	p. 335
Ten-Plus Functions You Really Should Know	p. 339
Sum	p. 339
Average	p. 340
Count	p. 340
Int and Round	p. 341
Int	p. 341
Round	p. 341
If	p. 342
Now and Today	p. 342
Hlookup and Vlookup	p. 343
Isnumber	p. 343
Min and Max	p. 344
Sumif and Countif	p. 344
Index	p. 347
Table of Contents provided by Ingram. All Rights Reserved.

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Microsoft Office Excel 2007 Formulas and Functions For Dummies

Summary

Author Biography

Table of Contents

Supplemental Materials