Volume 5 in the series Excel professionals

Volume 1: Excel For Beginners

Volume 2: Charting in Excel

Volume 3: Excel-- Beyond The Basics

Volume 4: Managing & Tabulating Data in Excel

Volume 5: Statistical Analysis with Excel

Volume 6: Financial Analysis using Excel

CONTENTS

CHAPTER 1 WRITING FORMULAS 25

1.1 The Basics Of Writing Formulae 26

1.2 Tool for using this chapter effectively: Viewing the formula instead of the end

result 26

1.2.a The “A1” vs. the “R1C1“ style of cell references 28

1.2.b Writing a simple formula that references cells 29

1.3 Types Of References Allowed In A Formula 30

1.3.a Referencing cells from another worksheet 30

1.3.b Referencing a block of cells 30

1.3.c Referencing non–adjacent cells 31

1.3.d Referencing entire rows 32

1.3.e Referencing entire columns 32

1.3.f Referencing corresponding blocks of cells/rows/columns from a set of

worksheets 33

CHAPTER 2 COPYING/CUTTING AND PASTING FORMULAE 35

2.1 Copying And Pasting A Formula To Other Cells In The Same Column 36

2.2 Copying And Pasting A Formula To Other Cells In The Same Row 37

2.3 Copying And Pasting A Formula To Other Cells In A Different Row And Column

38

2.4 Controlling Cell Reference Behavior When Copying And Pasting Formulae (Use

Of The “$” Key) 39

2.4.a Using the “$” sign in different permutations and computations in a

formula 41

2.5 Copying And Pasting Formulas From One Worksheet To Another 42

2.6 Pasting One Formula To Many Cells, Columns, Rows 43

2.7 Pasting Several Formulas To A Symmetric But Larger Range 43

2.8 Defining And Referencing A “Named Range” 43

Adding several named ranges in one step 46

Using a named range 47

2.9 Selecting All Cells With Formulas That Evaluate To A Similar Number Type 48

2.10 Special Paste Options 48

2.10.a Pasting only the formula (but not the formatting and comments) 48

2.10.b Pasting the result of a formula, but not the formula itself 48

2.11 Cutting And Pasting Formulae 49

Intoduction & Contents

7

2.11.a The difference between “copying and pasting” formulas and “cutting and

pasting” formulas 49

2.12 Creating A Table Of Formulas Using Data/Table 50

2.13 Saving Time By Writing, Copying And Pasting Formulas On Several Worksheets

Simultaneously 50

CHAPTER 3 PASTE SPECIAL 52

3.1 Pasting The Result Of A Formula, But Not The Formula 53

3.2 Other Selective Pasting Options 56

3.2.a Pasting only the formula (but not the formatting and comments) 56

3.2.b Pasting only formats 56

3.2.c Pasting data validation schemes 57

3.2.d Pasting all but the borders 57

3.2.e Pasting comments only 57

3.3 Performing An Algebraic “Operation” When Pasting One Column/Row/Range On

To Another 58

3.3.a Multiplying/dividing/subtracting/adding all cells in a range by a number

58

3.3.b Multiplying/dividing the cell values in cells in several “pasted on”

columns with the values of the copied range 59

3.4 Switching Rows To Columns 59

CHAPTER 4 INSERTING FUNCTIONS 61

4.1 Basics 61

4.2 A Simple Function 64

4.3 Functions That Need Multiple Range References 67

4.4 Writing A “Function Within A Function” 69

4.5 New Function-Related Features In The XP Version Of Excel 73

Searching for a function 73

4.5.a Enhanced Formula Bar 73

4.5.b Error Checking and Debugging 74

CHAPTER 5 TRACING CELL REFERENCES & DEBUGGING FORMULA

ERRORS 76

5.1 Tracing the cell references used in a formula 76

5.2 Tracing the formulas in which a particular cell is referenced 78

5.3 The Auditing Toolbar 79

5.4 Watch window (only available in the XP version of Excel) 80

Statistical Analysis with Excel

8

5.5 Error checking and Formula Evaluator (only available in the XP version of Excel)

81

5.6 Formula Auditing Mode (only available in the XP version of Excel) 84

5.7 Cell-specific Error Checking and Debugging 85

5.8 Error Checking Options 86

CHAPTER 6 FUNCTIONS FOR BASIC STATISTICS 89

6.1 “Averaged” Measures Of Central Tendency 90

6.1.a AVERAGE 90

6.1.b TRIMMEAN (“Trimmed mean”) 91

6.1.c HARMEAN (“Harmonic mean”) 92

6.1.d GEOMEAN (“Geometric mean”) 93

6.2 Location Measures Of Central Tendency (Mode, Median) 94

6.2.a MEDIAN 95

6.2.b MODE 95

6.3 Other Location Parameters (Maximum, Percentiles, Quartiles, Other) 95

6.3.a QUARTILE 96

6.3.b PERCENTILE 96

6.3.c Maximum, Minimum and “Kth Largest” 97

MAX (“Maximum value”) 97

MIN (“Minimum value”) 98

LARGE 98

SMALL 99

6.3.d Rank or relative standing of each cell within the range of a series 99

PERCENTRANK 99

RANK 100

6.4 Measures Of Dispersion (Standard Deviation & Variance) 100

Sample dispersion: STDEV, VAR 100

Population dispersion: STDEVP, VARP 101

6.5 Shape Attributes Of The Density Function (Skewness, Kurtosis) 102

6.5.a Skewness 102

6.5.b Kurtosis 104

6.6 Functions Ending With An “A” Suffix 105

CHAPTER 7 PROBABILITY DENSITY FUNCTIONS AND CONFIDENCE

INTERVALS 109

7.1 Probability Density Functions (PDF), Cumulative Density Functions (CDF), and

Inverse functions 110

7.1.a Probability Density Function (PDF) 110

7.1.b Cumulative Density Function (CDF) 111

The CDF and Confidence Intervals 112

7.1.c Inverse mapping functions 114

Intoduction & Contents

9

7.2 Normal Density Function 115

Symmetry 116

Convenience of using the Normal Density Function 117

Are all large-sample series Normally Distributed? 117

Statistics & Econometrics: Dependence of Methodologies on the assumption

of Normality 118

The Standard Normal and its power 119

7.2.a The Probability Density Function (PDF) and Cumulative Density Function

(CDF) 119

7.2.b Inverse function 121

7.2.c Confidence Intervals 121

95% Confidence Interval 121

90% Confidence Interval 122

7.3 Standard Normal or Z–Density Function 123

Inverse function 124

Confidence Intervals 124

7.4 T–Density Function 125

Inverse function 126

Confidence Intervals 126

7.4.a One–tailed Confidence Intervals 127

95% Confidence Interval 127

90% Confidence Interval 127

7.5 F–Density Function 129

Inverse function 129

One–tailed Confidence Intervals 130

7.6 Chi-Square Density Function 130

Inverse function 131

One–tailed Confidence Intervals 131

7.7 Other Continuous Density Functions: Beta, Gamma, Exponential, Poisson,

Weibull & Fisher 132

7.7.a Beta Density Function 132

Inverse Function 133

Confidence Intervals 134

7.7.b Gamma Density Function 134

Inverse Function 135

Confidence Intervals 136

7.7.c Exponential Density Function 136

7.7.d Fisher Density Function 138

7.7.e Poisson Density Function 138

7.7.f Weibull Density Function 138

7.7.g Discrete probabilities— Binomial, Hypergeometric & Negative Binomial

139

Binomial Density Function 139

Hypergeometric Density Function 139

Negative Binomial 139

7.8 List of Density Function 140

7.9 Some Inverse Function 141

Statistical Analysis with Excel

10

CHAPTER 8 OTHER MATHEMATICS & STATISTICS FUNCTIONS 144

8.1 Counting and summing 145

COUNT function 145

COUNTA function also counts cells with logical or text values 147

COUNTBLANK function counts the number of empty cells in the range

reference 148

SUM function 148

PRODUCT function 149

SUMPRODUCT function 149

8.2 The “If” counting and summing functions: Statistical functions with logical

conditions 150

SUMIF function 150

COUNTIF function 151

8.3 Transformations (log, exponential, absolute, sum, etc) 153

Standardizing a series that follows a Normal Density Function 155

8.4 Deviations from the Mean 156

DEVSQ 156

AVEDEV 156

8.5 Cross series relations 157

8.5.a Covariance and correlation functions 157

8.5.b Sum of Squares 157

SUMXMY2 function 158

SUMX2MY2 function 158

CHAPTER 9 ADD-INS: ENHANCING EXCEL 161

9.1 Add-Ins: Introduction 161

9.1.a What can an Add-In do? 162

9.1.b Why use an Add-In? 162

9.2 Add–ins installed with Excel 162

9.3 Other Add-Ins 163

9.4 The Statistics Add-In 163

9.4.a Choosing the Add-Ins 163

CHAPTER 10 STATISTICS TOOLS 169

10.1 Descriptive statistics 170

10.2 Rank and Percentile 175

Interpreting the output: 177

10.3 Bivariate relations— correlation, covariance 178

Correlation analysis 178

Interpreting the output 179

10.3.a Covariance tool and formula 180

Intoduction & Contents

11

CHAPTER 11 HYPOTHESIS TESTING 183

11.1 Z-testing for population means when population variances are known 184

Interpreting the output 189

11.2 T-testing means when the two samples are from distinct groups 189

11.2.a The pretest— F-testing for equality in variances 189

Interpreting the output 191

11.2.b T-test: Two–Sample Assuming Unequal Variances 193

Interpreting the output 196

11.2.c T-test: Two–Sample Assuming Equal Variances 199

11.3 Paired Sample T-tests 199

11.4 ANOVA 205

Interpreting the output 207

CHAPTER 12 REGRESSION 211

12.1 Assumptions Underlying Regression Models 211

12.1.a Assumption 1: The relationship between any one independent series and

the dependent series can be captured by a straight line in a 2–axis graph

213

12.1.b Assumption 2: The independent variables do not change if the sampling is

replicated 213

12.1.c Assumption 3: The sample size must be greater than the number of

independent variables (N should be greater than K–1) 214

12.1.d Assumption 4: Not all the values of any one independent series can be the

same 215

12.1.e Assumption 5: The residual or disturbance error terms follow several rules

216

Assumption 5a: The mean/average or expected value of the disturbance

equals zero 216

Assumption 5b: The disturbance terms all have the same variance 216

Assumption 5c: A disturbance term for one observation should have no

relation with the disturbance terms for other observations or with any

of the independent variables 217

Assumption 5d: There is no specification bias 217

Assumption 5e: The disturbance terms have a Normal Density Function 218

12.1.f Assumption 6: There are no strong linear relationships among the

independent variables 218

12.2 Conducting the Regression 219

12.3 Brief guideline for interpreting regression output 222

12.4 Breakdown of classical assumptions: validation and correction 226

CHAPTER 13 OTHER TOOLS FOR STATISTICS 229

13.1 Sampling analysis 229

13.2 Random Number Generation 231

Statistical Analysis with Excel

12

13.3 Time series 234

Exponential Smoothing 234

Moving Average analysis 235

CHAPTER 14 THE SOLVER TOOL FOR CONSTRAINED LINEAR OPTIMIZATION

239

14.1 Defining the objective function (Choosing the optimization criterion) 239

14.2 Adding constraints 243

14.3 Choosing Algorithm Options 244

Running the Solver 245

INDEX 245