Microsoft Office Excel 2007 Formulas and Functions For Dummies
by Bluttman, Ken; Aitken, Peter G.Edition: 1st
Format: Paperback
Pub. Date: 2007-01-30
Publisher(s): For Dummies
Other versions by this Author
List Price: $24.99
Buy New
Usually Ships in 8 - 10 Business Days.
$24.97
Rent Book
Select for Price
Used Book
We're Sorry
Sold Out
eBook
We're Sorry
Not Available
How Marketplace Works:
- This item is offered by an independent seller and not shipped from our warehouse
- Item details like edition and cover design may differ from our description; see seller's comments before ordering.
- Sellers much confirm and ship within two business days; otherwise, the order will be cancelled and refunded.
- Marketplace purchases cannot be returned to eCampus.com. Contact the seller directly for inquiries; if no response within two days, contact customer service.
- Additional shipping costs apply to Marketplace purchases. Review shipping costs at checkout.
Summary
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
Author Biography
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.
Table of Contents
| 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. |
An electronic version of this book is available through VitalSource.
This book is viewable on PC, Mac, iPhone, iPad, iPod Touch, and most smartphones.
By purchasing, you will be able to view this book online, as well as download it, for the chosen number of days.
Digital License
You are licensing a digital product for a set duration. Durations are set forth in the product description, with "Lifetime" typically meaning five (5) years of online access and permanent download to a supported device. All licenses are non-transferable.
More details can be found here.
A downloadable version of this book is available through the eCampus Reader or compatible Adobe readers.
Applications are available on iOS, Android, PC, Mac, and Windows Mobile platforms.
Please view the compatibility matrix prior to purchase.