Principles of Financial Modelling

Preface xxv About the Author xxvii About the Website xxix Part One Introduction to Modelling, Core Themes and Best Practices 1 Chapter 1 Models of Models 3 Introduction 3 Context and Objectives 3 The Stages of Modelling 3 Backward Thinking and Forward Calculation Processes 4 Chapter 2 Using Models in Decision Support 7 Introduction 7 Benefits of Using Models 7 Providing Numerical Information 7 Capturing Influencing Factors and Relationships 7 Generating Insight and Forming Hypotheses 8 Decision Levers, Scenarios, Uncertainties, Optimisation,  Risk Mitigation and Project Design 8 Improving Working Processes, Enhanced Communications and Precise Data Requirements 9 Challenges in Using Models 9 The Nature of Model Error 9 Inherent Ambiguity and Circularity of Reasoning 10 Inconsistent Scope or Alignment of Decision and Model 10 The Presence on Biases, Imperfect Testing, False Positives and Negatives 11 Balancing Intuition with Rationality 11 Lack of Data or Insufficient Understanding of a Situation 12 Overcoming Challenges: Awareness, Actions and Best Practices 13 Chapter 3 Core Competencies and Best Practices: Meta-themes 15 Introduction 15 Key Themes 15 Decision-support Role, Objectives, Outputs and Communication 16 Application Knowledge and Understanding 17 Skills with Implementation Platform 17 Defining Sensitivity and Flexibility Requirements 18 Designing Appropriate Layout, Input Data Structures and Flow 20 Ensuring Transparency and Creating a User-friendly Model 20 Integrated Problem-solving Skills 21 Part Two Model Design and Planning 23 Chapter 4 Defining Sensitivity and Flexibility Requirements 25 Introduction 25 Key Issues for Consideration 25 Creating a Focus on Objectives and Their Implications 26 Sensitivity Concepts in the Backward Thought and Forward Calculation Processes 26 Time Granularity 30 Level of Detail on Input Variables 30 Sensitising Absolute Values or Variations from Base Cases 31 Scenarios Versus Sensitivities 32 Uncertain Versus Decision Variables 33 Increasing Model Validity Using Formulae 34 Chapter 5 Database Versus Formulae-driven Approaches 37 Introduction 37 Key Issues for Consideration 37 Separating the Data, Analysis and Presentation (Reporting) Layers 37 The Nature of Changes to Data Sets and Structures 39 Focus on Data or Formulae? 40 Practical Example 42 Chapter 6 Designing the Workbook Structure 47 Introduction 47 Designing Workbook Models with Multiple Worksheets 47 Linked Workbooks 47 Multiple Worksheets: Advantages and Disadvantages 48 Generic Best Practice Structures 49 The Role of Multiple Worksheets in Best Practice Structures 49 Type I: Single Worksheet Models 50 Type II: Single Main Formulae Worksheet, and Several Data Worksheets 50 Type III: Single Main Formulae Worksheet, and Several Data and Local Analysis Worksheets 51 Further Comparative Comments 51 Using Information from Multiple Worksheets: Choice (Exclusion) and Consolidation (Inclusion) Processes 52 Multi-sheet or “Three Dimensional” Formulae 53 Using Excel’s Data/Consolidation Functionality 54 Consolidating from Several Sheets into a Database Using a Macro 55 User-defined Functions 56 Part Three Model Building, Testing and Auditing 57 Chapter 7 Creating Transparency: Formula Structure, Flow and Format 59 Introduction 59 Approaches to Identifying the Drivers of Complexity 59 Taking the Place of a Model Auditor 59 Example: Creating Complexity in a Simple Model 60 Core Elements of Transparent Models 61 Optimising Audit Paths 62 Creating Short Audit Paths Using Modular Approaches 63 Creating Short Audit Paths Using Formulae Structure and Placement 67 Optimising Logical Flow and the Direction of the Audit Paths 68 Identifying Inputs, Calculations and Outputs: Structure and Formatting 69 The Role of Formatting 70 Colour-coding of Inputs and Outputs 70 Basic Formatting Operations 73 Conditional Formatting 73 Custom Formatting 75 Creating Documentation, Comments and Hyperlinks 76 Chapter 8 Building Robust and Transparent Formulae 79 Introduction 79 General Causes of Mistakes 79 Insufficient Use of General Best Practices Relating to Flow, Formatting, Audit Paths 79 Insufficient Consideration Given to Auditability and Other Potential Users 79 Overconfidence, Lack of Checking and Time Constraints 80 Sub-optimal Choice of Functions 80 Inappropriate Use or Poor Implementation of Named Ranges, Circular References or Macros 80 Examples of Common Mistakes 80 Referring to Incorrect Ranges or To Blank Cells 80 Non-transparent Assumptions, Hidden Inputs and Labels 82 Overlooking the Nature of Some Excel Function Values 82 Using Formulae Which are Inconsistent Within a Range 83 Overriding Unforeseen Errors with IFERROR 84 Models Which are Correct in Base Case but Not in Others 85 Incorrect Modifications when Working with Poor Models 85 The Use of Named Ranges 85 Mechanics and Implementation 86 Disadvantages of Using Named Ranges 86 Advantages and Key Uses of Named Ranges 90 Approaches to Building Formulae, to Testing, Error Detection and Management 91 Checking Behaviour and Detecting Errors Using Sensitivity Testing 91 Using Individual Logic Steps 93 Building and Splitting Compound Formulae 94 Using Absolute Cell Referencing Only Where Necessary 96 Limiting Repeated or Unused Logic 96 Using Breaks to Test Calculation Paths 97 Using Excel Error Checking Rules 97 Building Error-checking Formulae 98 Handling Calculation Errors Robustly 100 Restricting Input Values Using Data Validation 100 Protecting Ranges 101 Dealing with Structural Limitations: Formulae and Documentation 102 Chapter 9 Choosing Excel Functions for Transparency, Flexibility and Efficiency 105 Introduction 105 Key Considerations 105 Direct Arithmetic or Functions, and Individual Cells or Ranges? 105 IF Versus MIN/MAX 107 Embedded IF Statements 109 Short Forms of Functions 111 Text Versus Numerical Fields 112 SUMIFS with One Criterion 112 Including Only Specific Items in a Summation 113 AGGREGATE and SUBTOTAL Versus Individual Functions 114 Array Functions or VBA User-defined Functions? 115 Volatile Functions 115 Effective Choice of Lookup Functions 116 Chapter 10 Dealing with Circularity 117 Introduction 117 The Drivers and Nature of Circularities 117 Circular (Equilibrium or Self-regulating) Inherent Logic 117 Circular Formulae (Circular References) 118 Generic Types of Circularities 119 Resolving Circular Formulae 119 Correcting Mistakes that Result in Circular Formulae 120 Avoiding a Logical Circularity by Modifying the Model Specification 120 Eliminating Circular Formulae by Using Algebraic (Mathematical) Manipulation 121 Resolving a Circularity Using Iterative Methods 122 Iterative Methods in Practice 123 Excel’s Iterative Method 123 Creating a Broken Circular Path: Key Steps 125 Repeatedly Iterating a Broken Circular Path Manually and Using a VBA Macro 126 Practical Example 128 Using Excel Iterations to Resolve Circular References 129 Using a Macro to Resolve a Broken Circular Path 129 Algebraic Manipulation: Elimination of Circular References 130 Altered Model 1: No Circularity in Logic or in Formulae 130 Altered Model 2: No Circularity in Logic in Formulae 131 Selection of Approach to Dealing with Circularities: Key Criteria 131 Model Accuracy and Validity 132 Complexity and Transparency 133 Non-convergent Circularities 134 Potential for Broken Formulae 138 Calculation Speed 140 Ease of Sensitivity Analysis 140 Conclusions 141 Chapter 11 Model Review, Auditing and Validation 143 Introduction 143 Objectives 143 (Pure) Audit 143 Validation 144 Improvement, Restructuring or Rebuild 145 Processes, Tools and Techniques 146 Avoiding Unintentional Changes 146 Developing a General Overview and Then Understanding the Details 147 Testing and Checking the Formulae 151 Using a Watch Window and Other Ways to Track Values 151 Part Four Sensitivity and Scenario Analysis, Simulation and Optimisation 153 Chapter 12 Sensitivity and Scenario Analysis: Core Techniques 155 Introduction 155 Overview of Sensitivity-related Techniques 155 DataTables 156 Overview 156 Implementation 157 Limitations and Tips 157 Practical Applications 160 Example: Sensitivity of Net Present Value to Growth Rates 160 Example: Implementing Scenario Analysis 160 Chapter 13 Using GoalSeek and Solver 163 Introduction 163 Overview of GoalSeek and Solver 163 Links to Sensitivity Analysis 163 Tips, Tricks and Limitations 163 Practical Applications 164 Example: Breakeven Analysis of a Business 165 Example: Threshold Investment Amounts 166 Example: Implied Volatility of an Option 167 Example: Minimising Capital Gains Tax Liability 167 Example: Non-linear Curve Fitting 169 Chapter 14 Using VBA Macros to Conduct Sensitivity and Scenario Analyses 171 Introduction 171 Practical Applications 172 Example: Running Sensitivity Analysis Using a Macro 172 Example: Running Scenarios Using a Macro 173 Example: Using a Macro to Run Breakeven Analysis with GoalSeek 173 Example: Using Solver Within a Macro to Create a Frontier of Optimum Solutions 175 Chapter 15 Introduction to Simulation and Optimisation 177 Introduction 177 The Links Between Sensitivity and Scenario Analysis, Simulation and Optimisation 177 The Combinatorial Effects of Multiple Possible Input Values 177 Controllable Versus Non-controllable: Choice Versus Uncertainty of Input Values 178 Practical Example: A Portfolio of Projects 179 Description 179 Optimisation Context 180 Risk or Uncertainty Context Using Simulation 180 Further Aspects of Optimisation Modelling 182 Structural Choices 182 Uncertainty 183 Integrated Approaches to Optimisation 183 Modelling Issues and Tools 184 Chapter 16 The Modelling of Risk and Uncertainty, and Using Simulation 187 Introduction 187 The Meaning, Origins and Uses of Monte Carlo Simulation 187 Definition and Origin 187 Limitations of Sensitivity and Scenario Approaches 188 Key Benefits of Uncertainty and Risk Modelling and the Questions Addressable 189 The Nature of Model Outputs 190 The Applicability of Simulation Methods 190 Key Process and Modelling Steps in Risk Modelling 191 Risk Identification 191 Risk Mapping and the Role of the Distribution of Input Values 191 The Modelling Context and the Meaning of Input Distributions 192 The Effect of Dependencies Between Inputs 192 Random Numbers and the Required Number of Recalculations or Iterations 193 Using Excel and VBA to Implement Risk and Simulation Models 194 Generation of Random Samples 194 Repeated Recalculations and Results Storage 195 Example: Cost Estimation with Uncertainty and Event Risks Using Excel/VBA 196 Using Add-ins to Implement Risk and Simulation Models 196 Benefits of Add-ins 196 Example: Cost Estimation with Uncertainty and Event Risks Using @RISK 197 Part Five Excel Functions and Functionality 199 Chapter 17 Core Arithmetic and Logical Functions 201 Introduction 201 Practical Applications 201 Example: IF, AND, OR, NOT 202 Example: MIN, MAX, MINA, MAXA 204 Example: MINIFS and MAXIFS 204 Example: COUNT, COUNTA, COUNTIF and Similar Functions 205 Example: SUM, AVERAGE, AVERAGEA 206 Example: SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS 206 Example: PRODUCT 207 Example: SUMPRODUCT 209 Example: SUBTOTAL 209 Example: AGGREGATE 210 Example: IFERROR 212 Example: SWITCH 215 Chapter 18 Array Functions and Formulae 217 Introduction 217 Functions and Formulae: Definitions 217 Implementation 217 Advantages and Disadvantages 218 Practical Applications: Array Functions 218 Example: Capex and Depreciation Schedules Using TRANSPOSE 218 Example: Cost Allocation Using SUMPRODUCT with TRANSPOSE 218 Example: Cost Allocation Using Matrix Multiplication Using MMULT 219 Example: Activity-based Costing and Resource Forecasting Using Multiple Driving Factors 220 Example: Summing Powers of Integers from 1 Onwards 222 Practical Applications: Array Formulae 225 Example: Finding First Positive Item in a List 225 Example: Find a Conditional Maximum 226 Example: Find a Conditional Maximum Using AGGREGATE as an Array Formula 227 Chapter 19 Mathematical Functions 229 Introduction 229 Practical Applications 229 Example: EXP and LN 229 Example: ABS and SIGN 232 Example: INT, ROUNDDOWN, ROUNDUP, ROUND and TRUNC 233 Example: MROUND, CEILING.MATH and FLOOR.MATH 235 Example: MOD 236 Example: SQRT and POWER 236 Example: FACT and COMBIN 237 Example: RAND() 238 Example: SINE, ASIN, DEGREES and PI() 239 Example: BASE and DECIMAL 241 Chapter 20 Financial Functions 243 Introduction 243 Practical Applications 243 Example: FVSCHEDULE 244 Example: FV and PV 244 Example: PMT, IPMT, PPMT, CUMIPMT, CUMPRINC and NPER 246 Example: NPV and IRR for a Buy or Lease Decision 248 Example: SLN, DDB and VDB 250 Example: YIELD 252 Example: Duration of Cash Flows 252 Example: DURATION and MDURATION 253 Example: PDURATION and RRI 254 Other Financial Functions 255 Chapter 21 Statistical Functions 257 Introduction 257 Practical Applications: Position, Ranking and Central Values 258 Example: Calculating Mean and Mode 258 Example: Dynamic Sorting of Data Using LARGE 260 Example: RANK.EQ 261 Example: RANK.AVG 262 Example: Calculating Percentiles 262 Example: PERCENTRANK-type Functions 263 Practical Applications: Spread and Shape 264 Example: Generating a Histogram of Returns Using FREQUENCY 265 Example: Variance, Standard Deviation and Volatility 267 Example: Skewness and Kurtosis 271 Example: One-sided Volatility (Semi-deviation) 272 Practical Applications: Co-relationships and Dependencies 273 Example: Scatter Plots (X–Y Charts) and Measuring Correlation 274 Example: More on Correlation Coefficients and Rank Correlation 275 Example: Measuring Co-variances 277 Example: Covariance Matrices, Portfolio Volatility and Volatility Time Scaling 277 Practical Applications: Probability Distributions 280 Example: Likelihood of a Given Number of Successes of an Oil Exploration Process 282 Example: Frequency of Outcomes Within One or Two Standard Deviations 283 Example: Creating Random Samples from Probability Distributions 283 Example: User-defined Inverse Functions for Random Sampling 284 Example: Values Associated with Probabilities for a Binomial Process 285 Example: Confidence Intervals for the Mean Using Student (T) and Normal Distributions 285 Example: the CONFIDENCE.T and CONFIDENCE.NORM Functions 287 Example: Confidence Intervals for the Standard Deviation Using Chi-squared 289 Example: Confidence Interval for the Slope of Regression Line (or Beta) 289 Practical Applications: More on Regression Analysis and Forecasting 291 Example: Using LINEST to Calculate Confidence Intervals for the Slope (or Beta) 291 Example: Using LINEST to Perform Multiple Regression 292 Example: Using LOGEST to Find Exponential Fits 293 Example: Using TREND and GROWTH to Forecast Linear and Exponential Trends 294 Example: Linear Forecasting Using FORECAST.LINEAR 295 Example: Forecasting Using the FORECAST.ETS Set of Functions 296 Chapter 22 Information Functions 299 Introduction 299 Practical Applications 300 Example: In-formula Comments Using ISTEXT, ISNUMBER or N 300 Example: Building a Forecast Model that Can Be Updated with Actual Reported Figures 300 Example: Detecting Consistency of Data in a Database 301 Example: Consistent use of “N/A” in Models 301 Example: Applications of the INFO and CELL Functions: An Overview 303 Example: Creating Updating Labels that Refer to Data or Formulae 303 Example: Showing the User Which Recalculation Mode the File Is On 305 Example: Finding the Excel Version Used and Creating Backward Compatible Formulae 305 Example: File Location and Structural Information Using CELL, INFO, SHEET and SHEETS 306 Chapter 23 Date and Time Functions 307 Introduction 307 Practical Applications 308 Example: Task Durations, Resource and Cost Estimation 308 Example: Keeping Track of Bookings, Reservations or Other Activities 308 Example: Creating Precise Time Axes 309 Example: Calculating the Year and Month of a Date 309 Example: Calculating the Quarter in Which a Date Occurs 310 Example: Creating Time-based Reports and Models from Data Sets 311 Example: Finding Out on What Day of the Week You Were Born 311 Example: Calculating the Date of the Last Friday of Every Month 311 Example: the DATEDIF Function and Completed Time Periods 312 Chapter 24 Text Functions and Functionality 313 Introduction 313 Practical Applications 314 Example: Joining Text Using CONCAT and TEXTJOIN 314 Example: Splitting Data Using the Text-to-columns Wizard 315 Example: Converting Numerical Text to Numbers 316 Example: Dynamic Splitting Text into Components I 316 Example: Dynamic Splitting Text into Components II 317 Example: Comparing LEFT, RIGHT, MID and LEN 317 Example: Dynamic Splitting Text into Components III 318 Example: Comparing FIND and SEARCH 319 Example: the UPPER and LOWER Functions 319 Example: the PROPER Function 319 Example: the EXACT Function 320 Example: Comparing REPLACE with SUBSTITUTE 320 Example: the REPT Function 320 Example: the CLEAN and TRIM Functions 321 Example: Updating Model Labels and Graph Titles 322 Example: Creating Unique Identifiers or Keys for Data Matching 323 Chapter 25 Lookup and Reference Functions 325 Introduction 325 Practical Applications: Basic Referencing Processes 326 Example: the ROW and COLUMN Functions 326 Example: the ROWS and COLUMNS Functions 327 Example: Use of the ADDRESS Function and the Comparison with CELL 327 Practical Applications: Further Referencing Processes 328 Example: Creating Scenarios Using INDEX, OFFSET or CHOOSE 328 Example: Charts that Can Use Multiple or Flexible Data Sources 330 Example: Reversing and Transposing Data Using INDEX or OFFSET 331 Example: Shifting Cash Flows or Other Items over Time 334 Example: Depreciation Schedules with Triangle Calculations 334 Practical Applications: Combining Matching and Reference Processes 335 Example: Finding the Period in Which a Condition is Met Using MATCH 335 Example: Finding Non-contiguous Scenario Data Using Matching Keys 336 Example: Creating and Finding Matching Text Fields or Keys 336 Example: Combining INDEX with MATCH 337 Example: Comparing INDEX-MATCH with V- and HLOOKUP 338 Example: Comparing INDEX-MATCH with LOOKUP 343 Example: Finding the Closest Matching Value Using Array and Other Function Combinations 344 Practical Applications: More on the OFFSET Function and Dynamic Ranges 345 Example: Flexible Ranges Using OFFSET (I) 345 Example: Flexible Ranges Using OFFSET (II) 346 Example: Flexible Ranges Using OFFSET (III) 347 Example: Flexible Ranges Using OFFSET (IV) 347 Practical Applications: The INDIRECT Function and Flexible Workbook or Data Structures 349 Example: Simple Examples of Using INDIRECT to Refer to Cells and Other Worksheets 349 Example: Incorporating Data from Multiple Worksheet Models and Flexible Scenario Modelling 351 Example: Other Uses of INDIRECT – Cascading Drop-down Lists 352 Practical Examples: Use of Hyperlinks to Navigate a Model, and Other Links to Data Sets 352 Example: Model Navigation Using Named Ranges and Hyperlinks 353 Chapter 26 Filters, Database Functions and PivotTables 355 Introduction 355 Issues Common to Working with Sets of Data 356 Cleaning and Manipulating Source Data 356 Static or Dynamic Queries 356 Creation of New Fields or Complex Filters? 357 Excel Databases and Tables 357 Automation Using Macros 359 Practical Applications: Filters 359 Example: Applying Filters and Inspecting Data for Errors or Possible Corrections 359 Example: Identification of Unique Items and Unique Combinations 362 Example: Using Filters to Remove Blanks or Other Specified Items 363 Example: Extraction of Data Using Filters 365 Example: Adding Criteria Calculations to the Data Set 365 Example: Use of Tables 366 Example: Extraction of Data Using Advanced Filters 369 Practical Applications: Database Functions 370 Example: Calculating Conditional Sums and Maxima Using DSUM and DMAX 370 Example: Implementing a Between Query 371 Example: Implementing Multiple Queries 371 Practical Applications: PivotTables 373 Example: Exploring Summary Values of Data Sets 373 Example: Exploring Underlying Elements of the Summary Items 376 Example: Adding Slicers 376 Example: Timeline Slicers 378 Example: Generating Reports Which Ignore Errors or Other Specified Items 380 Example: Using the GETPIVOTDATA Functions 380 Example: Creating PivotCharts 382 Example: Using the Excel Data Model to Link Tables 383 Chapter 27 Selected Short-cuts and Other Features 387 Introduction 387 Key Short-cuts and Their Uses 387 Entering and Modifying Data and Formulae 388 Formatting 390 Auditing, Navigation and Other Items 391 Excel KeyTips 393 Other Useful Excel Tools and Features 393 Sparklines 393 The Camera Tool 393 Part Six Foundations of VBA and Macros 395 Chapter 28 Getting Started 397 Introduction 397 Main Uses of VBA 397 Task Automation 398 Creating User-defined Functions 398 Detecting and Reacting to Model Events 398 Enhancing or Managing the User Interface 399 Application Development 399 Core Operations 399 Adding the Developer Tab to Excel’s Toolbar 399 The Visual Basic Editor 399 Recording Macros 401 Typical Adaptations Required When Using Recorded Code 402 Writing Code 403 Running Code 404 Debugging Techniques 405 Simple Examples 406 Example: Using Excel Cell Values in VBA 406 Example: Using Named Excel Ranges for Robustness and Flexibility 407 Example: Placing a Value from VBA Code into an Excel Range 408 Example: Replacing Copy/Paste with an Assignment 409 Example: A Simple User-defined Function 409 Example: Displaying a Message when a Workbook is Opened 410 Chapter 29 Working with Objects and Ranges 413 Introduction 413 Overview of the Object Model 413 Objects, Properties, Methods and Events 413 Object Hierarchies and Collections 414 Using Set. . .=. . . . 415 Using the With. . .End With Construct 415 Finding Alternatives to the Selection or Activation of Ranges and Objects 416 Working with Range Objects: Some Key Elements 416 Basic Syntax Possibilities and Using Named Ranges 416 Named Ranges and Named Variables 417 The CurrentRegion Property 417 The xlCellTypeLastCell Property 418 Worksheet Names and Code Names 419 The UsedRange Property 419 The Cells Property 420 The Offset Property 421 The Union Method 421 InputBox and MsgBox 421 Application.InputBox 422 Defining Multi-cell Ranges 422 Using Target to React to Worksheet Events 422 Using Target to React to Workbook Events 423 Chapter 30 Controlling Execution 425 Introduction 425 Core Topics in Overview 425 Input Boxes and Message Boxes 425 For. . .Next Loops 425 For Each. . . In. . .Next 426 If. . .Then 427 Select Case. . .End Select 427 GoTo 428 Do. . .While/Until. . .Loop 428 Calculation and Calculate 429 Screen Updating 432 Measuring Run Time 432 Displaying Alerts 433 Accessing Excel Worksheet Functions 433 Executing Procedures Within Procedures 434 Accessing Add-ins 435 Practical Applications 435 Example: Numerical Looping 435 Example: Listing the Names of All Worksheets in a Workbook 436 Example: Adding a New Worksheet to a Workbook 437 Example: Deleting Specific Worksheets from a Workbook 437 Example: Refreshing PivotTables, Modifying Charts and Working Through Other Object Collections 438 Chapter 31 Writing Robust Code 441 Introduction 441 Key Principles 441 From the Specific to the General 441 Adapting Recorded Code for Robustness 442 Event Code 442 Comments and Indented Text 442 Modular Code 443 Passing Arguments ByVal or ByRef 443 Full Referencing 445 Using Worksheet Code Numbers 447 Assignment Statements, and Manipulating Objects Rather Than Selecting or Activating Them 447 Working with Ranges Instead of Individual Cells 448 Data Types and Variable Declaration 448 Choice of Names 449 Working with Arrays in VBA 450 Understanding Error Codes: An Introduction 451 Further Approaches to Testing, Debugging and Error-handling 452 General Techniques 452 Debugging Functions 453 Implementing Error-handling Procedures 454 Chapter 32 Manipulation and Analysis of Data Sets with VBA 455 Introduction 455 Practical Applications 455 Example: Working Out the Size of a Range 455 Example: Defining the Data Set at Run Time Based on User Input 457 Example: Working Out the Position of a Data Set Automatically 457 Example: Reversing Rows (or Columns) of Data I: Placement in a New Range 459 Example: Reversing Rows (or Columns) of Data II: In Place 460 Example: Automation of Other Data-related Excel Procedures 461 Example: Deleting Rows Containing Blank Cells 462 Example: Deleting Blank Rows 463 Example: Automating the Use of Filters to Remove Blanks or Other Specified Items 464 Example: Performing Multiple Database Queries 468 Example: Consolidating Data Sets That Are Split Across Various Worksheets or Workbooks 469 Chapter 33 User-defined Functions 473 Introduction 473 Benefits of Creating User-defined Functions 473 Syntax and Implementation 474 Practical Applications 475 Example: Accessing VBA Functions for Data Manipulation: Val, StrReverse and Split 476 Example: A Wrapper to Access the Latest Excel Function Version 477 Example: Replication of IFERROR for Compatibility with Excel 2003 478 Example: Sum of Absolute Errors 479 Example: Replacing General Excel Calculation Tables or Ranges 480 Example: Using Application.Caller to Generate a Time Axis as an Array Function 480 Example: User-defined Array Functions in Rows and Columns 482 Example: Replacing Larger Sets of Excel Calculations: Depreciation Triangles 484 Example: Sheet Reference Functions 485 Example: Statistical Moments when Frequencies Are Known 487 Example: Rank Order Correlation 489 Example: Semi-deviation of a Data Set 491 Index 493