- ISBN: 9780470046555 | 0470046554
- Cover: Paperback
- Copyright: 1/30/2007
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. |
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.