The Fictioning Horror Sale
 
 

Recently Viewed clear list


Original Essays | September 15, 2014

Lois Leveen: IMG Forsooth Me Not: Shakespeare, Juliet, Her Nurse, and a Novel



There's this writer, William Shakespeare. Perhaps you've heard of him. He wrote this play, Romeo and Juliet. Maybe you've heard of it as well. It's... Continue »
  1. $18.19 Sale Hardcover add to wish list

    Juliet's Nurse

    Lois Leveen 9781476757445

spacer
Qualifying orders ship free.
$39.99
New Trade Paper
Ships in 1 to 3 days
Add to Wishlist
available for shipping or prepaid pickup only
Available for In-store Pickup
in 7 to 12 days
Qty Store Section
6 Remote Warehouse Personal Computers- Desktop Applications- Spreadsheets

Formulas and Functions: Microsoft Excel 2010 (Business Solutions)

by

Formulas and Functions: Microsoft Excel 2010 (Business Solutions) Cover

 

Synopses & Reviews

Publisher Comments:

MASTER CORE EXCEL 2010 TOOLS FOR BUILDING POWERFUL, RELIABLE SPREADSHEETS!

 

Excel expert Paul McFedries shows how to use Excel 2010’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 breakthrough Excel 2010 improvements–from Sparklines to the brand-new version of Solver. Along the way, you’ll 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.

 

• Create more powerful 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

• Troubleshoot problems with formulas, ranges, and functions

 

About MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excelskills, and presents focused tasks and examples for performing them rapidly and effectively. Selectedby Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will:

 

• Dramatically increase your productivity–saving you 50 hours a year, or more

• Present proven, creative strategies for solving real-world problems

• Show you how to get great results, no matter how much data you have

• Help you avoid critical mistakes that even experienced users make

 

Paul McFedries, president of Logophilia Limited, is a Microsoft Office expert and full-time technical writer. Paul has been authoring computer books since 1991 and has more than 70 books to his credit, which combined have sold more than three million copies worldwide. His recent titles include Using the Microsoft Office Web Apps, and Microsoft Windows 7 Unleashed. He is also proprietor of Word Spy (www.wordspy.com), a website that tracks new words and phrases as they enter the English language.

 

Book News Annotation:

Excel users utilize only five percent of the program's features says this Microsoft Office expert. To help users take advantage of the other 95%, McFedries covers Excel's intermediate and advanced formula-building features including the new Solver for complex problems. Explanations and step-by-step tutorials treat mastering ranges for building spreadsheet formulas, major function categories, and building business models and financial formulas (e.g., for tracking trends, amortizing loans, analyzing investments and cash-flow). Chapters include practical application examples with screenshots, case studies, tips, cautions, and cross-references. Access to the online edition is included. Annotation ©2010 Book News, Inc., Portland, OR (booknews.com)

Synopsis:

Managers, entrepreneurs, and business professionals of all kinds need better skills for building more powerful Microsoft Excel spreadsheet models. In this book, Paul McFedries cuts through Excel's complexity, thoroughly demystifying the most valuable features for spreadsheet model-builders: formulas, functions, ranges, and data analysis tools. Drawing on his unsurpassed experience simplifying difficult technologies for mainstream users, McFedries walks through using these features to solve a wide variety of real-world problems. You will learn how to: " Create advanced formulas and troubleshoot formula problems " Work with text, logical, information, lookup, date/time, math, and statistical functions " Analyze data with standard tables and PivotTables " Use Excel's powerful business modeling tools " Utilize Regression to track trends and make forecasts " Build loan, investment, and discount formulas, and more Packed with no-nonsense, step-by-step tutorials and practical examples, Formulas and Functions has been thoroughly updated to reflect Excel 2010's many improvements, from Sparklines to the newest version of Solver. McFedries also illuminates important Excel 2007 enhancements that many users haven't yet mastered, including conditional formatting. This book is part of the new MrExcel Library series, edited by Bill Jelen, world-renowned Excel expert and host of the enormously popular Excel help site, MrExcel.com.

 

  • No-nonsense, step-by-step tutorials and practical examples from a world-renowned expert in demystifying complex technology, Paul McFedries
  • Includes specific techniques for analyzing complex data, modeling business problems, tracking trends, making forecasts, and building financial formulas
  • Part of the brand-new MrExcel's Library series, edited by Excel legend Bill Jelen
  • About the Author

    Paul McFedries, president of Logophilia Limited, is a full-time technical writer, passionate computer tinkerer, and Windows expert. He has authored 60+ computer books that have sold 3,000,000+ copies. His recent titles include Microsoft Windows 7 Unleashed, Microsoft Home Server Unleashed, Tweak It and Freak It: A Killer Guide to Making Windows Run Your Way, Formulas and Functions with Microsoft Excel 2007; and Tr icks of the Microsoft Office 2007 Gurus. He is also proprietor of Word Spy (www.wordspy.com) a website that tracks new words and phrases as they enter the English language.

     

     

    Editor:

    Bill Jelen has made more than 75 guest appearances on TechTV with Leo Laporte and was voted guest of the year on the Computer America radio show. He has produced more than 600 episodes of his daily video Learn Excel podcast. Before founding MrExcel.com in 1998, Jelen spent 12 years “in the trenches” as a financial analyst for the accounting, finance, marketing, and operations departments of a publicly held company. Since then, his company has automated Excel reports for hundreds of clients around the world. The website answers more than 30,000 questions a year–for free–for readers all over the world.

    Table of Contents

    Introduction ............................................................................................................................................................................................ 1

        What’s in the Book ............................................................................................................................................................................. 2

        This Book’s Special Features ......................................................................................................................................................... 2

    I MASTERING EXCEL RANGES AND FORMULAS

    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 .................................................................................................................................................................... 13

    Filling a Range ................................................................................................................................................................................... 14

    Using the Fill Handle ...................................................................................................................................................................... 14

        Using AutoFill to Create Text and Numeric Series ................................................................................................ 14

        Creating a Custom AutoFill List ....................................................................................................................................... 16

        Filling a Range........................................................................................................................................................................... 17

    Creating a Series ............................................................................................................................................................................... 17

    Advanced Range Copying ........................................................................................................................................................... 18

    Copying Selected Cell Attributes ............................................................................................................................................. 19

        Combining the Source and Destination Arithmetically ..................................................................................... 20

        Transposing Rows and Columns .................................................................................................................................... 21

    Clearing a Range ............................................................................................................................................................................... 22

    Applying Conditional Formatting to a Range .................................................................................................................. 22

        Creating Highlight Cells Rules ......................................................................................................................................... 22

        Creating Top/Bottom Rules ............................................................................................................................................... 24

        Adding Data Bars ..................................................................................................................................................................... 26

        Adding Color Scales ................................................................................................................................................................ 28

        Adding Icon Sets ...................................................................................................................................................................... 31

        From Here .................................................................................................................................................................................... 32

    2 Using Range Names ............................................................................................................................................................ 33

    Defining a Range Name ............................................................................................................................................................... 34

        Working with the Name Box ............................................................................................................................................ 34

        Using the New Name Dialog Box ................................................................................................................................... 35

        Changing the Scope to Define Sheet-Level Names ............................................................................................. 37

        Using Worksheet Text to Define Names ..................................................................................................................... 37

        Naming Constants .................................................................................................................................................................. 39

    Working with Range Names ..................................................................................................................................................... 41

        Referring to a Range Name ............................................................................................................................................... 41

        Working with Name AutoComplete............................................................................................................................. 43

        Navigating Using Range Names ..................................................................................................................................... 43

        Pasting a List of Range Names in a Worksheet...................................................................................................... 44

        Displaying the Name Manager ....................................................................................................................................... 44

        Filtering Names ........................................................................................................................................................................ 44

        Editing a Range Name’s Coordinates ........................................................................................................................... 45

        Adjusting Range Name Coordinates Automatically ............................................................................................ 45

        Changing a Range Name .................................................................................................................................................... 47

        Deleting a Range Name ....................................................................................................................................................... 47

        Using Names with the Intersection Operator.......................................................................................................... 47

        From Here .................................................................................................................................................................................... 49

    3 Building Basic Formulas................................................................................................................................................. 51

    Understanding Formula Basics ................................................................................................................................................ 51

        Formula Limits in Excel 2007 and Excel 2010 ......................................................................................................... 52

        Entering and Editing Formulas ....................................................................................................................................... 52

        Using Arithmetic Formulas ................................................................................................................................................ 53

        Using Comparison Formulas ............................................................................................................................................. 54

        Using Text Formulas .............................................................................................................................................................. 54

        Using Reference Formulas ................................................................................................................................................. 55

    Understanding Operator Precedence ................................................................................................................................... 55

        The Order of Precedence ..................................................................................................................................................... 55

        Controlling the Order of Precedence ............................................................................................................................ 56

    Controlling Worksheet Calculation ........................................................................................................................................ 58

    Copying and Moving Formulas ................................................................................................................................................ 59

        Understanding Relative Reference Format............................................................................................................... 60

        Understanding Absolute Reference Format ............................................................................................................. 62

        Copying a Formula Without Adjusting Relative References .......................................................................... 63

    Displaying Worksheet Formulas ............................................................................................................................................. 63

    Converting a Formula to a Value ............................................................................................................................................ 63

    Working with Range Names in Formulas ........................................................................................................................... 64

        Pasting a Name into a Formula ...................................................................................................................................... 64

        Applying Names to Formulas ........................................................................................................................................... 65

        Naming Formulas .................................................................................................................................................................... 68

    Working with Links in Formulas ............................................................................................................................................. 69

        Understanding External References .............................................................................................................................. 69

        Updating Links .......................................................................................................................................................................... 71

        Changing the Link Source .................................................................................................................................................. 72

    Formatting Numbers, Dates, and Times ............................................................................................................................. 72

        Numeric Display Formats .................................................................................................................................................... 72

        Date and Time Display Formats ...................................................................................................................................... 80

        Deleting Custom Formats ................................................................................................................................................... 83

        From Here .................................................................................................................................................................................... 83

    4 Creating Advanced Formulas ................................................................................................................................... 85

    Working with Arrays ...................................................................................................................................................................... 85

        Using Array Formulas ............................................................................................................................................................ 86

    Understanding Array Formulas ................................................................................................................................................ 87

        Array Formulas That Operate on Multiple Ranges ............................................................................................... 88

    Using Array Constants ................................................................................................................................................................... 89

        Functions That Use or Return Arrays ............................................................................................................................ 90

    Using Iteration and Circular References .............................................................................................................................. 91

    Consolidating Multisheet Data ................................................................................................................................................ 93

        Consolidating by Position ................................................................................................................................................... 93

        Consolidating by Category ................................................................................................................................................. 97

    Applying Data-Validation Rules to Cells .............................................................................................................................. 98

    Using Dialog Box Controls on a Worksheet .................................................................................................................... 101

        Displaying the Developer Tab ....................................................................................................................................... 101

        Using the Form Controls .................................................................................................................................................. 101

        Adding a Control to a Worksheet ............................................................................................................................... 101

        Linking a Control to a Cell Value ................................................................................................................................. 102

        Understanding the Worksheet Controls .................................................................................................................. 103

        From Here ................................................................................................................................................................................. 108

    5 Troubleshooting Formulas ...................................................................................................................................... 109

    Understanding Excel’s Error Values .................................................................................................................................... 110

        #DIV/0! ................................................................................................................................................................................... 110

        #N/A ............................................................................................................................................................................................ 111

        #NAME? ...................................................................................................................................................................................... 111

        Case Study: Avoiding #NAME? Errors When Deleting Range Names ..................................................... 112

        #NULL! ...................................................................................................................................................................................... 113

        #NUM! ......................................................................................................................................................................................... 113

        #REF! ......................................................................................................................................................................................... 113

        #VALUE! ................................................................................................................................................................................... 114

    Fixing Other Formula Errors .................................................................................................................................................... 114

        Missing or Mismatched Parentheses ......................................................................................................................... 114

        Erroneous Formula Results ............................................................................................................................................. 115

        Fixing Circular References ............................................................................................................................................... 116

    Handling Formula Errors with IFERROR() ................................................................................................................... 117

    Using the Formula Error Checker ......................................................................................................................................... 118

        Choosing an Error Action ................................................................................................................................................. 119

        Setting Error Checker Options ....................................................................................................................................... 119

    Auditing a Worksheet................................................................................................................................................................. 122

    Understanding Auditing .................................................................................................................................................. 123

        Tracing Cell Precedents ..................................................................................................................................................... 123

        Tracing Cell Dependents .................................................................................................................................................. 124

        Tracing Cell Errors ................................................................................................................................................................. 124

        Removing Tracer Arrows .................................................................................................................................................. 124

        Evaluating Formulas ........................................................................................................................................................... 124

        Watching Cell Values.......................................................................................................................................................... 125

        From Here ................................................................................................................................................................................. 126

    II HARNESSING THE POWER OF FUNCTIONS

    6 Understanding Functions ......................................................................................................................................... 127

    About Excel’s Functions ............................................................................................................................................................. 128

    The Structure of a Function ..................................................................................................................................................... 128

    Typing a Function into a Formula ....................................................................................................................................... 130

    Using the Insert Function Feature ...................................................................................................................................... 131

    Loading the Analysis ToolPak ................................................................................................................................................ 134

        From Here ................................................................................................................................................................................. 134

    7 Working with Text Functions ............................................................................................................................... 137

    Excel’s Text Functions ................................................................................................................................................................. 137

    Working with Characters and Codes ................................................................................................................................. 137

        The CHAR() Function ........................................................................................................................................................ 139

        The CODE() Function ........................................................................................................................................................ 141

    Converting Text .............................................................................................................................................................................. 142

        The LOWER() Function ..................................................................................................................................................... 142

        The UPPER() Function ..................................................................................................................................................... 143

        The PROPER() Function.................................................................................................................................................. 143

    Formatting Text ............................................................................................................................................................................. 143

        The DOLLAR() Function.................................................................................................................................................. 144

        The FIXED() Function ..................................................................................................................................................... 144

        The TEXT() Function ........................................................................................................................................................ 145

        Displaying When a Workbook Was Last Updated ............................................................................................. 145

    Manipulating Text ........................................................................................................................................................................ 146

    Removing Unwanted Characters from a String ........................................................................................................... 146

        The TRIM() Function ........................................................................................................................................................ 146

        The CLEAN() Function ..................................................................................................................................................... 147

        The REPT() Function: Repeating a Character .................................................................................................... 147

        Padding a Cell ......................................................................................................................................................................... 147

        Building Text Charts............................................................................................................................................................ 148

    Extracting a Substring ................................................................................................................................................................ 149

        The LEFT() Function ........................................................................................................................................................ 149

        The RIGHT() Function ..................................................................................................................................................... 150

        The MID() Function ........................................................................................................................................................... 150

        Converting Text to Sentence Case ............................................................................................................................... 150

        A Date-Conversion Formula ........................................................................................................................................... 151

    Searching for Substrings ........................................................................................................................................................... 151

        The FIND() and SEARCH() Functions ................................................................................................................... 151

    Case Study: Generating Account Numbers ......................................................................................................................152

        Extracting a First Name or Last Name ...................................................................................................................... 153

        Extracting First Name, Last Name, and Middle Initial .................................................................................... 154

        Determining the Column Letter .................................................................................................................................. 154

    Substituting One Substring for Another.......................................................................................................................... 155

        The REPLACE() Function .............................................................................................................................................. 155

        The SUBSTITUTE() Function ..................................................................................................................................... 156

        Removing a Character from a String ......................................................................................................................... 156

        Removing Two Different Characters from a String ........................................................................................... 157

    Case Study: Generating Account Numbers, Part 2 ..................................................................................................... 157

        Removing Line Feeds ......................................................................................................................................................... 158

        From Here ................................................................................................................................................................................. 158

    8 Working with Logical and Information Functions......................................................................... 159

    Adding Intelligence with Logical Functions ................................................................................................................... 159

        Using the IF() Function ................................................................................................................................................. 160

        Performing Multiple Logical Tests .............................................................................................................................. 163

        Combining Logical Functions with Arrays .............................................................................................................. 168

    Case Study: Building an Accounts Receivable Aging Worksheet ...................................................................... 173

    Getting Data with Information Functions ....................................................................................................................... 176

        The CELL() Function ........................................................................................................................................................ 176

        The ERROR.TYPE() Function ..................................................................................................................................... 179

        The INFO() Function ........................................................................................................................................................ 180

        The IS Functions .................................................................................................................................................................... 181

        From Here ................................................................................................................................................................................. 183

    9 Working with Lookup Functions ........................................................................................................................ 185

    Understanding Lookup Tables .............................................................................................................................................. 186

    The CHOOSE() Function .......................................................................................................................................................... 187

        Determining the Name of the Day of the Week ................................................................................................. 187

        Determining the Month of the Fiscal Year ............................................................................................................. 188

        Calculating Weighted Questionnaire Results ....................................................................................................... 189

        Integrating CHOOSE() and Worksheet Option Buttons ............................................................................... 189

    Looking Up Values in Tables ................................................................................................................................................... 190

        The VLOOKUP() Function .............................................................................................................................................. 190

        The HLOOKUP() Function .............................................................................................................................................. 191

        Returning a Customer Discount Rate with a Range Lookup ....................................................................... 192

        Returning a Tax Rate with a Range Lookup .......................................................................................................... 193

        Finding Exact Matches ...................................................................................................................................................... 193

        Advanced Lookup Operations ....................................................................................................................................... 195

        From Here ................................................................................................................................................................................. 200

    10 Working with Date and Time Functions.................................................................................................... 201

    How Excel Deals with Dates and Times ............................................................................................................................ 201

        Entering Dates and Times ............................................................................................................................................... 202

        Excel and Two-Digit Years .............................................................................................................................................. 203

    Using Excel’s Date Functions .................................................................................................................................................. 204

        Returning a Date ................................................................................................................................................................... 205

        Returning Parts of a Date ................................................................................................................................................ 207

        Calculating the Difference Between Two Dates ................................................................................................. 216

    Using Excel’s Time Functions ................................................................................................................................................. 220

        Returning a Time .................................................................................................................................................................. 220

        Returning Parts of a Time ............................................................................................................................................... 221

        Calculating the Difference Between Two Times ................................................................................................. 224

    Case Study: Building an Employee Time Sheer ............................................................................................................ 224

        From Here ................................................................................................................................................................................. 228

    11 Working with Math Functions ............................................................................................................................. 229

    Understanding Excel’s Rounding Functions ................................................................................................................... 232

        ROUND() Function .............................................................................................................................................................. 232

        MROUND() Function ........................................................................................................................................................... 233

        ROUNDDOWN() and ROUNDUP() Functions .......................................................................................................... 233

        CEILING() and FLOOR() Functions ...................................................................................................................... 234

        Determining the Fiscal Quarter in Which a Date Falls .................................................................................... 235

        Calculating Easter Dates ................................................................................................................................................... 235

        EVEN() and ODD() Functions..................................................................................................................................... 236

        INT() and TRUNC() Functions ................................................................................................................................. 236

        Using Rounding to Prevent Calculation Errors..................................................................................................... 237

        Setting Price Points ............................................................................................................................................................. 237

    Case Study: Rounding Billable Time .................................................................................................................................. 238

    Summing Values ............................................................................................................................................................................ 238

        SUM() Function .................................................................................................................................................................... 238

        Calculating Cumulative Totals ...................................................................................................................................... 239

        Summing Only the Positive or Negative Values in a Range ........................................................................ 240

    MOD() Function ............................................................................................................................................................................. 240

        Better Formula for Time Differences ......................................................................................................................... 241

        Summing Every nth Row ................................................................................................................................................ 241

        Determining Whether a Year Is a Leap Year ......................................................................................................... 242

        Creating Ledger Shading ................................................................................................................................................. 242

    Generating Random Numbers............................................................................................................................................... 244

        RAND() Function ................................................................................................................................................................. 244

        RANDBETWEEN() Function............................................................................................................................................ 246

        From Here ................................................................................................................................................................................. 247

    12 Working with Statistical Functions ................................................................................................................ 249

    Understanding Descriptive Statistics ................................................................................................................................ 249

    Counting Items with the COUNT() Function ................................................................................................................ 252

    Calculating Averages ................................................................................................................................................................... 253

        AVERAGE() Function ........................................................................................................................................................ 253

        MEDIAN() Function ........................................................................................................................................................... 253

        MODE() Function ................................................................................................................................................................. 254

        Calculating the Weighted Mean ................................................................................................................................. 254

    Calculating Extreme Values .................................................................................................................................................... 256

        MAX() and MIN() Functions ........................................................................................................................................ 256

        LARGE() and SMALL() Functions ............................................................................................................................ 256

        Performing Calculations on the Top k Values ...................................................................................................... 258

        Performing Calculations on the Bottom k Values ............................................................................................. 258

    Calculating Measures of Variation ...................................................................................................................................... 258

        Calculating the Range ....................................................................................................................................................... 258

        Calculating the Variance .................................................................................................................................................. 259

        Calculating the Standard Deviation ........................................................................................................................... 260

    Working with Frequency Distributions ............................................................................................................................. 261

        FREQUENCY() Function .................................................................................................................................................. 262

        Understanding the Normal Distribution and the NORMDIST() Function ............................................. 263

        Shape of the Curve I: The SKEW() Function ......................................................................................................... 264

        Shape of the Curve II: The KURT() Function ....................................................................................................... 265

    Using the Analysis ToolPak Statistical Tools .................................................................................................................. 267

        Using the Descriptive Statistics Tool .......................................................................................................................... 270

        Determining the Correlation Between Data ......................................................................................................... 272

        Working with Histograms ............................................................................................................................................... 274

        Using the Random Number Generation Tool ....................................................................................................... 276

        Working with Rank and Percentile ............................................................................................................................. 279

        From Here ................................................................................................................................................................................. 281

    IIIBUILDING BUSINESS MODELS

    13 Analyzing Data with Tables ................................................................................................................................... 283

    Converting a Range to a Table .............................................................................................................................................. 285

    Basic Table Operations ............................................................................................................................................................... 286

    Sorting a Table ................................................................................................................................................................................ 287

        Performing a More Complex Sort ............................................................................................................................... 288

        Sorting a Table in Natural Order ................................................................................................................................. 289

        Sorting on Part of a Field ................................................................................................................................................. 290

        Sorting Without Articles ................................................................................................................................................... 291

    Filtering Table Data...................................................................................................................................................................... 292

        Using Filter Lists to Filter a Table ................................................................................................................................ 292

        Using Complex Criteria to Filter a Table .................................................................................................................. 296

        Entering Computed Criteria ........................................................................................................................................... 299

        Copying Filtered Data to a Different Range .......................................................................................................... 300

    Referencing Tables in Formulas ........................................................................................................................................... 301

        Using Table Specifiers ........................................................................................................................................................ 301

        Entering Table Formulas .................................................................................................................................................. 303

    Excel’s Table Functions .............................................................................................................................................................. 305

        About Table Functions ...................................................................................................................................................... 305

        Table Functions That Don’t Require a Criteria Range ...................................................................................... 305

        Table Functions That Accept Multiple Criteria ..................................................................................................... 307

        Table Functions That Require a Criteria Range ................................................................................................... 309

    Case Study: Applying Statistical Table Functions to a Defects Database ..................................................... 313

        From Here ................................................................................................................................................................................. 314

    14 Analyzing Data with PivotTables..................................................................................................................... 315

    What Are PivotTables? ............................................................................................................................................................... 315

        How PivotTables Work ...................................................................................................................................................... 316

        PivotTable Terms .................................................................................................................................................................. 317

    Building PivotTables .................................................................................................................................................................... 318

        Building a PivotTable from a Table or Range ....................................................................................................... 319

        Building a PivotTable from an External Database ............................................................................................. 322

        Working with and Customizing a PivotTable ....................................................................................................... 323

    Working with PivotTable Subtotals ................................................................................................................................... 323

        Hiding PivotTable Grand Totals ................................................................................................................................... 324

        Hiding PivotTable Subtotals .......................................................................................................................................... 324

        Customizing the Subtotal Calculation ...................................................................................................................... 324

    Changing the Data Field Summary Calculation ........................................................................................................... 325

        Using a Difference Summary Calculation................................................................................................................ 326

        Using a Percentage Summary Calculation ............................................................................................................. 327

        Using a Running Total Summary Calculation ....................................................................................................... 330

        Using an Index Summary Calculation ....................................................................................................................... 331

    Creating Custom PivotTable Calculations ........................................................................................................................ 332

        Creating a Calculated Field ............................................................................................................................................. 334

        Creating a Calculated Item ............................................................................................................................................. 335

    Case Study: Budgeting with Calculated Items.............................................................................................................. 337

    Using PivotTable Results in a Worksheet Formula .................................................................................................... 339

        From Here ................................................................................................................................................................................. 340

    15 Using Excel’s Business-Modeling Tools ....................................................................................................... 341

    Using What-If Analysis .............................................................................................................................................................. 341

        Setting Up a One-Input Data Table ............................................................................................................................ 342

        Adding More Formulas to the Input Table ............................................................................................................. 344

        Setting Up a Two-Input Table ...................................................................................................................................... 345

        Editing a Data Table ............................................................................................................................................................ 346

    Working with Goal Seek............................................................................................................................................................ 347

        How Does Goal Seek Work? ........................................................................................................................................... 347

        Running Goal Seek .............................................................................................................................................................. 347

        Optimizing Product Margin ............................................................................................................................................ 349

        Note About Goal Seek’s Approximations ................................................................................................................ 351

        Performing a Break-Even Analysis .............................................................................................................................. 352

        Solving Algebraic Equations .......................................................................................................................................... 352

    Working with Scenarios ............................................................................................................................................................ 354

        Understanding Scenarios ................................................................................................................................................. 354

        Setting Up Your Worksheet for Scenarios .............................................................................................................. 355

        Adding a Scenario ................................................................................................................................................................ 355

        Displaying a Scenario ......................................................................................................................................................... 357

        Editing a Scenario ................................................................................................................................................................ 358

        Merging Scenarios ............................................................................................................................................................... 358

        Generating a Summary Report .................................................................................................................................... 359

        Deleting a Scenario ............................................................................................................................................................. 360

        From Here ................................................................................................................................................................................. 361

    16 Using Regression to Track Trends and Make Forecasts ............................................................ 363

    Setting Up and Performing a Find ...................................................................................................................................... 363

    Choosing a Regression Method ............................................................................................................................................ 364

    Using Simple Regression on Linear Data ......................................................................................................................... 364

        Analyzing Trends Using Best-Fit Lines ..................................................................................................................... 365

        Making Forecasts .................................................................................................................................................................. 372

    Case Study: Trend Analysis and Forecasting for a Seasonal Sales Model ..................................................... 377

    Using Simple Regression on Nonlinear Data ................................................................................................................. 384

        Working with an Exponential Trend .......................................................................................................................... 384

        Working with a Logarithmic Trend ............................................................................................................................ 388

        Working with a Power Trend ........................................................................................................................................ 391

        Using Polynomial Regression Analysis ..................................................................................................................... 394

    Using Multiple Regression Analysis ................................................................................................................................... 396

        From Here ................................................................................................................................................................................. 399

    17 Solving Complex Problems with Solver ..................................................................................................... 401

    Some Background on Solver .................................................................................................................................................. 401

        The Advantages of Solver ................................................................................................................................................ 402

        When Do You Use Solver? ............................................................................................................................................... 402

    Loading Solver ................................................................................................................................................................................ 403

    Using Solver ...................................................................................................................................................................................... 403

    Adding Constraints ....................................................................................................................................................................... 406

    Saving a Solution as a Scenario ............................................................................................................................................ 408

    Setting Other Solver Options .................................................................................................................................................. 408

        Selecting the Method Solver Uses ............................................................................................................................... 409

        Controlling How Solver Works...................................................................................................................................... 409

        Working with Solver Models ......................................................................................................................................... 412

    Making Sense of Solver’s Messages ................................................................................................................................... 413

    Case Study: Solving the Transportation Problem ....................................................................................................... 415

    Displaying Solver’s Reports ..................................................................................................................................................... 417

        The Answer Report .............................................................................................................................................................. 417

        The Sensitivity Report ........................................................................................................................................................ 418

        The Limits Report ................................................................................................................................................................. 420

        From Here ................................................................................................................................................................................. 420

    IV BUILDING FINANCIAL FORMULAS

    18 Building Loan Formulas ............................................................................................................................................. 421

    Understanding the Time Value of Money ....................................................................................................................... 421

    Calculating the Loan Payment .............................................................................................................................................. 422

        Loan Payment Analysis ..................................................................................................................................................... 423

        Working with a Balloon Loan ....................................................................................................................................... 424

        Calculating Interest Costs, Part 1 ................................................................................................................................ 424

        Calculating the Principal and Interest ...................................................................................................................... 425

        Calculating Interest Costs, Part 2 ................................................................................................................................ 426

        Calculating Cumulative Principal and Interest .................................................................................................... 426

    Building a Loan Amortization Schedule ........................................................................................................................... 428

        Building a Fixed-Rate Amortization Schedule ..................................................................................................... 428

        Building a Dynamic Amortization Schedule ......................................................................................................... 429

    Calculating the Term of the Loan ........................................................................................................................................ 431

    Calculating the Interest Rate Required for a Loan ..................................................................................................... 433

    Calculating How Much You Can Borrow ........................................................................................................................... 434

    Case Study: Working with Mortgages ............................................................................................................................... 435

        From Here ................................................................................................................................................................................. 438

    19 Building Investment Formulas ........................................................................................................................... 439

    Working with Interest Rates .................................................................................................................................................. 439

        Understanding Compound Interest ........................................................................................................................... 440

        Nominal Versus Effective Interest ............................................................................................................................... 440

        Converting Between the Nominal Rate and the Effective Rate ................................................................ 441

    Calculating the Future Value .................................................................................................................................................. 442

        The Future Value of a Lump Sum ............................................................................................................................... 442

        The Future Value of a Series of Deposits ................................................................................................................. 443

        The Future Value of a Lump Sum Plus Deposits ................................................................................................. 444

    Working Toward an Investment Goal ............................................................................................................................... 444

        Calculating the Required Interest Rate .................................................................................................................... 444

        Calculating the Required Number of Periods ....................................................................................................... 445

        Calculating the Required Regular Deposit ............................................................................................................. 446

        Calculating the Required Initial Deposit .................................................................................................................. 447

        Calculating the Future Value with Varying Interest Rates ........................................................................... 448

    Case Study: Building an Investment Schedule ............................................................................................................. 449

        From Here ................................................................................................................................................................................. 451

    20 Building Discount Formulas .................................................................................................................................. 453

    Calculating the Present Value ............................................................................................................................................... 454

        Taking Inflation into Account ....................................................................................................................................... 454

        Calculating Present Value Using PV() ..................................................................................................................... 455

        Income Investing Versus Purchasing a Rental Property ................................................................................ 456

        Buying Versus Leasing ...................................................................................................................................................... 457

    Discounting Cash Flows ............................................................................................................................................................. 458

        Calculating the Net Present Value .............................................................................................................................. 459

        Calculating Net Present Value Using NPV() ........................................................................................................ 460

        Net Present Value with Varying Cash Flows ......................................................................................................... 462

        Net Present Value with Nonperiodic Cash Flows ............................................................................................... 463

    Calculating the Payback Period ............................................................................................................................................ 464

        Simple Undiscounted Payback Period ...................................................................................................................... 464

        Exact Undiscounted Payback Point ............................................................................................................................ 465

        Calculating the Internal Rate of Return ................................................................................................................... 466

        Using the IRR() Function.............................................................................................................................................. 467

        Calculating the Internal Rate of Return for Nonperiodic Cash Flows ..................................................... 468

        Calculating Multiple Internal Rates of Return ..................................................................................................... 468

    Case Study: Publishing a Book .............................................................................................................................................. 469

        From Here ................................................................................................................................................................................. 473

    TOC, 9780789743060, 4/13/10

     

    Product Details

    ISBN:
    9780789743060
    Author:
    Mcfedries, Paul
    Publisher:
    Que
    Author:
    McFedries, Paul
    Subject:
    Spreadsheets - General
    Subject:
    Spreadsheets - Excel
    Subject:
    Business -- Computer programs.
    Subject:
    Electronic spreadsheets
    Subject:
    Personal Computers-Applications
    Subject:
    Personal Computers-Desktop Applications-Spreadsheets
    Copyright:
    Edition Description:
    Trade paper
    Series:
    MrExcel Library
    Publication Date:
    May 2010
    Binding:
    TRADE PAPER
    Grade Level:
    General/trade
    Language:
    English
    Illustrations:
    Y
    Pages:
    512
    Dimensions:
    9.08 x 7 x 1.008 in 789 gr

    Other books you might like

    1. Writing (Discoveries)
      Used Trade Paper $2.95

    Related Subjects

    Computers and Internet » Operating Systems » Microsoft Windows » Applications
    Computers and Internet » Operating Systems » Microsoft Windows » Windows 95 » General
    Computers and Internet » Personal Computers » Desktop Applications » Spreadsheets

    Formulas and Functions: Microsoft Excel 2010 (Business Solutions) New Trade Paper
    0 stars - 0 reviews
    $39.99 In Stock
    Product details 512 pages Que - English 9780789743060 Reviews:
    "Synopsis" by ,

    Managers, entrepreneurs, and business professionals of all kinds need better skills for building more powerful Microsoft Excel spreadsheet models. In this book, Paul McFedries cuts through Excel's complexity, thoroughly demystifying the most valuable features for spreadsheet model-builders: formulas, functions, ranges, and data analysis tools. Drawing on his unsurpassed experience simplifying difficult technologies for mainstream users, McFedries walks through using these features to solve a wide variety of real-world problems. You will learn how to: " Create advanced formulas and troubleshoot formula problems " Work with text, logical, information, lookup, date/time, math, and statistical functions " Analyze data with standard tables and PivotTables " Use Excel's powerful business modeling tools " Utilize Regression to track trends and make forecasts " Build loan, investment, and discount formulas, and more Packed with no-nonsense, step-by-step tutorials and practical examples, Formulas and Functions has been thoroughly updated to reflect Excel 2010's many improvements, from Sparklines to the newest version of Solver. McFedries also illuminates important Excel 2007 enhancements that many users haven't yet mastered, including conditional formatting. This book is part of the new MrExcel Library series, edited by Bill Jelen, world-renowned Excel expert and host of the enormously popular Excel help site, MrExcel.com.

     

  • No-nonsense, step-by-step tutorials and practical examples from a world-renowned expert in demystifying complex technology, Paul McFedries
  • Includes specific techniques for analyzing complex data, modeling business problems, tracking trends, making forecasts, and building financial formulas
  • Part of the brand-new MrExcel's Library series, edited by Excel legend Bill Jelen
  • spacer
    spacer
    • back to top
    Follow us on...




    Powell's City of Books is an independent bookstore in Portland, Oregon, that fills a whole city block with more than a million new, used, and out of print books. Shop those shelves — plus literally millions more books, DVDs, and gifts — here at Powells.com.