Opis: Excel 2016 Formulas and Functions: Includes Content Update Program - Paul McFedries

Book + Content Update Program Master core Excel 2016 tools for building powerful, reliable spreadsheets with Excel 2016 Formulas and Functions. Excel expert Paul McFedries shows how to use Excel 2016's core features to solve problems and get the answers you need. Using real-world examples, McFedries helps you get the absolute most out of features and improvements ranging from AutoFill to Excel's newest functions. Along the way, you discover the fastest, best ways to handle essential day-to-day tasks ranging from generating account numbers to projecting the impact of inflation. Becoming an Excel expert has never been easier! You'll find crystal-clear instructions; insider insights; even complete step-by-step projects for building timesheets, projecting cash flow, aging receivables, analyzing defects, and more. * Quickly create powerful spreadsheets with range names and array formulas * Use conditional formatting to instantly reveal anomalies, problems, or opportunities * Analyze your data with standard tables and PivotTables * Use complex criteria to filter data in lists * Understand correlations between data * Perform sophisticated what-if analyses * Use regression to track trends and make forecasts * Build loan, investment, and discount formulas * Validate data, troubleshoot problems, and build more accurate, trustworthy spreadsheets In addition, this book is part of Que's exciting Content Update Program. As Microsoft updates features of Excel 2016, sections of this book will be updated or new sections will be added to match the updates to the software. The updates will be delivered to you via a FREE Web Edition of this book, which can be accessed with any Internet connection. To learn more, visitIntroduction 1 PART I : MASTERING EXCEL RANGES AND FORMULAS Chapter 1 Getting the Most Out of Ranges 5 Advanced Range-Selection Techniques 5 Mouse Range-Selection Tricks 6 Keyboard Range-Selection Tricks 7 Working with 3D Ranges 7 Selecting a Range Using Go To 8 Using the Go To Special Dialog Box 9 Data Entry in a Range 14 Filling a Range 14 Using the Fill Handle 15 Flash-Filling a Range 18 Creating a Series 20 Advanced Range Copying and Pasting 21 Pasting Selected Cell Attributes 22 Combining Two Ranges Arithmetically 23 Transposing Rows and Columns 24 Clearing a Range 25 Applying Conditional Formatting to a Range 25 Creating Highlight Cells Rules 26 Creating Top/Bottom Rules 27 Adding Data Bars 29 Adding Color Scales 32 Adding Icon Sets 33 Chapter 2 Using Range Names 37 Defining a Range Name 38 Working with the Name Box 39 Using the New Name Dialog Box 40 Changing the Scope to Define Sheet-Level Names 41 Using Worksheet Text to Define Names 41 Naming Constants 44 Working with Range Names 45 Referring to a Range Name 45 Working with AutoComplete for Range Names 47 Navigating Using Range Names 47 Pasting a List of Range Names in a Worksheet 48 Displaying the Name Manager 48 Filtering Names 48 Editing a Range Name's Coordinates 49 Adjusting Range Name Coordinates Automatically 49 Changing a Range Name 51 Deleting a Range Name 51 Using Names with the Intersection Operator 51 Chapter 3 Building Basic Formulas 53 Understanding Formula Basics 53 Formula Limits in Excel 2016 54 Entering and Editing Formulas 54 Using Arithmetic Formulas 55 Using Comparison Formulas 56 Using Text Formulas 57 Using Reference Formulas 57 Understanding Operator Precedence 57 The Order of Precedence 58 Controlling the Order of Precedence 58 Controlling Worksheet Calculation 60 Copying and Moving Formulas 62 Understanding Relative Reference Format 62 Understanding Absolute Reference Format 64 Copying a Formula Without Adjusting Relative References 65 Displaying Worksheet Formulas 65 Displaying All Worksheet Formulas 65 Displaying a Cell's Formula by Using FORMULATEXT() 65 Converting a Formula to a Value 66 Working with Range Names in Formulas 67 Pasting a Name into a Formula 67 Applying Names to Formulas 68 Naming Formulas 70 Working with Links in Formulas 71 Understanding External References 72 Updating Links 73 Changing the Link Source 73 Formatting Numbers, Dates, and Times 74 Numeric Display Formats 74 Date and Time Display Formats 83 Deleting Custom Formats 85 Chapter 4 Creating Advanced Formulas 87 Working with Arrays 87 Using Array Formulas 88 Using Array Constants 91 Functions That Use or Return Arrays 91 Using Iteration and Circular References 93 Consolidating Multisheet Data 95 Consolidating by Position 95 Consolidating by Category 98 Applying Data-Validation Rules to Cells 100 Using Dialog Box Controls on a Worksheet 103 Displaying the Developer Tab 103 Using the Form Controls 103 Adding a Control to a Worksheet 104 Linking a Control to a Cell Value 104 Understanding the Worksheet Controls 105 Chapter 5 Troubleshooting Formulas 111 Understanding Excel's Error Values 112 #DIV/0! 112 #N/A 113 #NAME? 113 Case Study: Avoiding #NAME? Errors When Deleting Range Names 114 #NULL! 115 #NUM! 115 #REF! 115 #VALUE! 115 Fixing Other Formula Errors 116 Missing or Mismatched Parentheses 116 Erroneous Formula Results 117 Fixing Circular References 118 Handling Formula Errors with IFERROR() 118 Using the Formula Error Checker 119 Choosing an Error Action 120 Setting Error Checker Options 121 Auditing a Worksheet 123 Understanding Auditing 124 Tracing Cell Precedents 125 Tracing Cell Dependents 125 Tracing Cell Errors 125 Removing Tracer Arrows 125 Evaluating Formulas 126 Watching Cell Values 126 PART II : HARNESSING THE POWER OF FUNCTIONS Chapter 6 Understanding Functions 129 About Excel's Functions 130 The Structure of a Function 130 Typing a Function into a Formula 132 Using the Insert Function Feature 134 Loading the Analysis ToolPak 136 Chapter 7 Working with Text Functions 139 Excel's Text Functions 139 Working with Characters and Codes 141 The CHAR() Function 141 The CODE() Function 144 Converting Text 144 The LOWER() Function 145 The UPPER() Function 145 The PROPER() Function 145 The NUMBERVALUE() Function 145 Formatting Text 146 The DOLLAR() Function 146 The FIXED() Function 147 The TEXT() Function 147 Displaying When a Workbook Was Last Updated 148 Manipulating Text 149 Removing Unwanted Characters from a String 149 The REPT() Function: Repeating a Character or String 150 Extracting a Substring 152 Converting Text to Sentence Case 153 A Date-Conversion Formula 154 Case Study: Generating Account Numbers, Part I 154 Searching for Substring 155 The FIND() and SEARCH() Functions 155 Extracting a First Name or Last Name 156 Extracting First Name, Last Name, and Middle Initial 157 Determining the Column Letter 157 Substituting One Substring for Another 158 The REPLACE() Function 159 The SUBSTITUTE() Function 159 Removing a Character from a String 160 Removing Two Different Characters from a String 160 Case Study: Generating Account Numbers, Part II 161 Removing Line Feeds 161 Chapter 8 Working with Logical and Information Functions 163 Adding Intelligence with Logical Functions 163 Using the IF() Function 164 Performing Multiple Logical Tests 167 Combining Logical Functions with Arrays 173 Case Study: Building an Accounts Receivable Aging Worksheet 178 Getting Data with Information Functions 181 The CELL() Function 182 The ERROR.TYPE() Function 184 The INFO() Function 186 The SHEET() and SHEETS() Functions 186 The IS Functions 187 Chapter 9 Working with Lookup Functions 191 Excel's Lookup Functions 191 Understanding Lookup Tables 192 The CHOOSE() Function 193 Determining the Name of the Day of the Week 194 Determining the Month of the Fiscal Year 194 Calculating Weighted Questionnaire Results 195 Integrating CHOOSE() and Worksheet Option Buttons 195 Looking Up Values in Tables 196 The VLOOKUP() Function 197 The HLOOKUP() Function 197 Returning a Customer Discount Rate with a Range Lookup 198 Returning a Tax Rate with a Range Lookup 199 Finding Exact Matches 200 Advanced Lookup Operations 201 Chapter 10 Working with Date and Time Functions 207 How Excel Deals with Dates and Times 207 Entering Dates and Times 208 Excel and Two-Digit Years 209 Using Excel's Date Functions 210 Returning a Date 212 Returning Parts of a Date 213 Calculating the Difference Between Two Dates 223 Using Excel's Time Functions 227 Returning a Time 228 Returning Parts of a Time 229 Calculating the Difference Between Two Times 231 Case Study: Building an Employee Time Sheet 231 Chapter 11 Working with Math Functions 237 Excel's Math and Trig Functions 237 Understanding Excel's Rounding Functions 241 The ROUND() Function 241 The MROUND() Function 242 The ROUNDDOWN() and ROUNDUP() Functions 242 The CEILING.MATH() and FLOOR.MATH() Functions 243 The EVEN() and ODD() Functions 245 The INT() and TRUNC() Functions 245 Case Study: Rounding Billable 247 Summing Values 247 The SUM() Function 247 Calculating Cumulative Totals 248 Summing Only the Positive or Negative Values in a Range 249 The MOD() Function 249 A Better Formula for Time Differences 250 Summing Every nth Row 250 Determining Whether a Year Is a Leap Year 251 Creating Ledger Shading 251 Generating Random Numbers 253 The RAND() Function 253 The RANDBETWEEN() Function 255 Chapter 12 Working with Statistical Functions 257 Excel's Statistical Functions 257 Understanding Descriptive Statistics 260 Counting Items with the COUNT() Function 261 Calculating Averages 262 The AVERAGE() Function 262 The MEDIAN() Function 262 The MODE() Function 263 Calculating the Weighted Mean 263 Calculating Extreme Values 264 The MAX() and MIN() Functions 264 The LARGE() and SMALL() Functions 266 Performing Calculations on the Top k Values 266 Performing Calculations on the Bottom k Values 267 Calculating Measures of Variation 267 Calculating the Range 268 Calculating the Variance 268 Calculating the Standard Deviation 269 Working with Frequency Distributions 270 The FREQUENCY() Function 271 Understanding the Normal Distribution and the NORMDIST() Function 272 The Shape of the Curve I: The SKEW() Function 274 The Shape of the Curve II: The KURT() Function 275 Using the Analysis ToolPak Statistical Tools 276 Using the Descriptive Statistics Tool 279 Determining the Correlation Between Data 280 Working with Histograms 283 Using the Random Number Generation Tool 285 Working with Rank and Percentile 288 PART III : BUILDING BUSINESS MODELS Chapter 13 Analyzing Data with Tables 291 Planning an Excel Table 291 Converting a Range to a Table 292 Basic Table Operations 294 Sorting a Table 295 Performing a More Complex Sort 296 Sorting a Table in Natural Order 297 Sorting on Part of a Field 298 Sorting Without Articles 299 Filtering Table Data 300 Using Filter Lists to Filter a Table 300 Using Complex Criteria to Filter a Table 304 Entering Computed Criteria 307 Copying Filtered Data to a Different Range 308 Referencing Tables in Formulas 309 Using Table Specifiers 309 Entering Table Formulas 311 Excel's Table Functions 313 About Table Functions 313 Table Functions That Don't Require a Criteria Range 313 Table Functions That Accept Multiple Criteria 317 Table Functions That Require a Criteria Range 319 Case Study: Applying Statistical Table Functions to a Defects Database 322 Chapter 14 Analyzing Data with PivotTables 325 What Are PivotTables? 325 How PivotTables Work 326 Some PivotTable Terms 328 Building PivotTables 329 Building a PivotTable from a Table or Range 329 Building a PivotTable from an External Database 332 Working with and Customizing a PivotTable 333 Working with PivotTable Subtotals 333 Hiding PivotTable Grand Totals 334 Hiding PivotTable Subtotals 334 Customizing the Subtotal Calculation 334 Changing the Data Field Summary Calculation 335 Using a Difference Summary Calculation 336 Using a Percentage Summary Calculation 337 Using a Running Total Summary Calculation 340 Using an Index Summary Calculation 341 Creating Custom PivotTable Calculations 342 Creating a Calculated Field 344 Creating a Calculated Item 346 Using PivotTable Results in a Worksheet Formula 347 Chapter 15 Using Excel's Business Modeling Tools 349 Using What-If Analysis 349 Setting Up a One-Input Data Table 350 Adding More Formulas to the Input Table 352 Setting Up a Two-Input Data Table 353 Editing a Data Table 355 Working with Goal Seek 355 How Does Goal Seek Work? 355 Running Goal Seek 356 Optimizing Product Margin 358 A Note About Goal Seek's Approximations 358 Performing a Break-Even Analysis 360 Solving Algebraic Equations 360 Working with Scenarios 362 Understanding Scenarios 362 Setting Up Your Worksheet for Scenarios 363 Adding a Scenario 364 Displaying a Scenario 365 Editing a Scenario 366 Merging Scenarios 367 Generating a Summary Report 367 Deleting a Scenario 369 Chapter 16 Using Regression to Track Trends and Make Forecasts 371 Choosing a Regression Method 372 Using Simple Regression on Linear Data 372 Analyzing Trends Using Best-Fit Lines 373 Making Forecasts 380 Case Study: Trend Analysis and Forecasting for a Seasonal Sales Model 386 Using Simple Regression on Nonlinear Data 393 Working with an Exponential Trend 394 Working with a Logarithmic Trend 399 Working with a Power Trend 401 Using Polynomial Regression Analysis 403 Using Multiple Regression Analysis 407 Chapter 17 Solving Complex Problems with Solver 411 Some Background on Solver 411 The Advantages of Solver 412 When Do You Use Solver? 412 Loading Solver 413 Using Solver 413 Adding Constraints 416 Saving a Solution as a Scenario 418 Setting Other Solver Options 418 Selecting the Method Solver Uses 419 Controlling How Solver Works 419 Working with Solver Models 422 Making Sense of Solver's Messages 424 Case Study: Solving the Transportation Problem 425 Displaying Solver's Reports 427 The Answer Report 427 The Sensitivity Report 429 The Limits Report 430 PART IV : BUILDING FINANCIAL FORMULAS Chapter 18 Building Loan Formulas 433 Understanding the Time Value of Money 433 Calculating a Loan Payment 435 Loan Payment Analysis 435 Working with a Balloon Loan 436 Calculating Interest Costs, Part 1 436 Calculating the Principal and Interest 437 Calculating Interest Costs, Part 2 438 Calculating Cumulative Principal and Interest 439 Building a Loan Amortization Schedule 440 Building a Fixed-Rate Amortization Schedule 440 Building a Dynamic Amortization Schedule 441 Calculating the Term of a Loan 443 Calculating the Interest Rate Required for a Loan 445 Calculating How Much You Can Borrow 446 Case Study: Working with Mortgages 447 Chapter 19 Building Investment Formulas 453 Working with Interest Rates 453 Understanding Compound Interest 454 Nominal Versus Effective Interest 454 Converting Between the Nominal Rate and the Effective Rate 455 Calculating the Future Value 456 The Future Value of a Lump Sum 456 The Future Value of a Series of Deposits 457 The Future Value of a Lump Sum Plus Deposits 458 Working Toward an Investment Goal 458 Calculating the Required Interest Rate 458 Calculating the Required Number of Periods 459 Calculating the Required Regular Deposit 460 Calculating the Required Initial Deposit 461 Calculating the Future Value with Varying Interest Rates 461 Case Study: Building an Investment Schedule 462 Chapter 20 Building Discount Formulas 467 Calculating the Present Value 468 Taking Inflation into Account 468 Calculating Present Value Using PV() 469 Income Investing Versus Purchasing a Rental Property 470 Buying Versus Leasing 471 Discounting Cash Flows 472 Calculating the Net Present Value 473 Calculating Net Present Value Using NPV() 474 Net Present Value with Varying Cash Flows 475 Net Present Value with Nonperiodic Cash Flows 476 Calculating the Payback Period 477 Simple Undiscounted Payback Period 477 Exact Undiscounted Payback Point 478 Discounted Payback Period 479 Calculating the Internal Rate of Return 479 Using the IRR() Function 480 Calculating the Internal Rate of Return for Nonperiodic Cash Flows 480 Calculating Multiple Internal Rates of Return 481 Case Study: Publishing a Book 482 Index 497

Szczegóły: Excel 2016 Formulas and Functions: Includes Content Update Program - Paul McFedries

Tytuł: Excel 2016 Formulas and Functions: Includes Content Update Program
Autor: Paul McFedries
Producent: Que Corporation
ISBN: 9780789755643
Rok produkcji: 2015
Ilość stron: 528
Oprawa: Miękka
Waga: 0.84 kg

Recenzje: Excel 2016 Formulas and Functions: Includes Content Update Program - Paul McFedries