Schmuller J. Statistical Analysis with Excel For Dummies

Подождите немного. Документ загружается.

Chapter 19

Ten Statistical and Graphical

Tips and Traps

In This Chapter

▶ Beware of significance

▶ Be wary of graphs

▶ Be cautious with regression

▶ Be careful with concepts

he world of statistics is full of pitfalls, but it’s also full of opportunities.

Whether you’re a user of statistics or someone who has to interpret them,

it’s possible to fall into the pitfalls. It’s also possible to walk around them. Here

are ten tips and traps from the areas of hypothesis testing, regression, correla-

tion, and graphs.

Significant Doesn’t Always

Mean Important

As I say earlier in the book, “significance” is, in many ways, a poorly chosen

term. When a statistical test yields a significant result, and the decision is to

reject H

, that doesn’t guarantee that the study behind the data is an impor-

tant one. Statistics can only help decision making about numbers and infer-

ences about the processes that produced them. They can’t make those

processes important or earth shattering. Importance is something you have

to judge for yourself — and no statistical test can do that for you.

27 454060-ch19.indd 36927 454060-ch19.indd 369 4/21/09 7:38:14 PM4/21/09 7:38:14 PM

370

Part V: The Part of Tens

Trying to Not Reject a Null Hypothesis

Has a Number of Implications

Let me tell you a story: Some years ago, an industrial firm was trying to show

it was finally in compliance with environmental cleanup laws. They took

numerous measurements of the pollution in the body of water surrounding

their factory, compared the measurements with a null hypothesis-generated

set of expectations, and found that they couldn’t reject H

with α = .05. Their

measurements didn’t differ significantly (there’s that word again) from

“clean” water.

This, the company claimed, was evidence that they had cleaned up their

act. Closer inspection revealed that their data approached significance, but

the pollution wasn’t quite of a high enough magnitude to reject H

. Does this

mean they’re not polluting?

Not at all. In striving to “prove” a null hypothesis, they had stacked the deck

in favor of themselves. They set a high barrier to get over, didn’t clear it, and

then patted themselves on the back.

Every so often, it’s appropriate to try and not reject H

. When you set out on

that path, be sure to set a high value of α (about .20-.30), so that small diver-

gences from H

cause rejection of H

. (I discuss this in Chapter 10 and I men-

tion it in other parts of the book. I think it’s important enough to mention

again here.)

Regression Isn’t Always linear

When trying to fit a regression model to a scatterplot, the temptation is to

immediately use a line. This is the best-understood regression model, and

when you get the hang of it, slopes and intercepts aren’t all that daunting.

But linear regression isn’t the only kind of regression. It’s possible to fit a

curve through a scatterplot. I won’t kid you: The statistical concepts behind

curvilinear regression are more difficult to understand than the concepts

behind linear regression.

It’s worth taking the time to master those concepts, however. Sometimes,

a curve is a much better fit than a line. (This is partly a plug for Chapter 20,

where I take you through curvilinear regression — and some of the concepts

behind it.)

27 454060-ch19.indd 37027 454060-ch19.indd 370 4/21/09 7:38:14 PM4/21/09 7:38:14 PM

371

Chapter 19: Ten Statistical and Graphical Tips and Traps

Extrapolating Beyond a Sample

Scatterplot Is a Bad Idea

Whether you’re working with linear regression or curvilinear regression,

keep in mind that it’s inappropriate to generalize beyond the boundaries of

the scatterplot.

Suppose you’ve established a solid predictive relationship between a test of

mathematics aptitude and performance in mathematics courses, and your

scatterplot only covers a narrow range of mathematics aptitude. You have

no way of knowing whether the relationship holds up beyond that range.

Predictions outside that range aren’t valid.

Your best bet is to expand the scatterplot by testing more people. You might

find that the original relationship only tells part of the story.

Examine the Variability Around

a Regression Line

Careful analysis of residuals (the differences between observed and pre-

dicted values) can tell you a lot about how well the line fits the data. A foun-

dational assumption is that variability around a regression line is the same

up and down the line. If it isn’t, the model might not be as predictive as you

think. If the variability is systematic (greater variability at one end than at

the other), curvilinear regression might be more appropriate than linear. The

standard error of estimate won’t always be the indicator.

A Sample Can Be Too Large

Believe it or not. This sometimes happens with correlation coefficients. A

very large sample can make a small correlation coefficient statistically sig-

nificant. For example, with 100 degrees of freedom and α = .05, a correlation

coefficient of .195 is cause for rejecting the null hypothesis that the popula-

tion correlation coefficient is equal to zero.

But what does that correlation coefficient really mean? The coefficient of

determination — r

— is just .038, meaning that the SS

Regression

is less than 4

percent of the SS

Total

(See Chapter 16.) That’s a very small association.

27 454060-ch19.indd 37127 454060-ch19.indd 371 4/21/09 7:38:14 PM4/21/09 7:38:14 PM

372

Part V: The Part of Tens

Bottom line: When looking at a correlation coefficient, be aware of the sample

size. If it’s large enough, it can make a trivial association turn out statistically

significant. (Hmmm . . . “significance” . . . there it is again!)

Consumers: Know Your Axes

When you look at a graph, make sure that you know what’s on each axis.

Make sure that you understand the units of measure. Do you understand the

independent variable? Do you understand the dependent variable? Can you

describe each one in your own words? If the answer to any of those ques-

tions is “No,” you don’t understand the graph you’re looking at.

When looking at a graph in a TV ad, be very wary if it disappears too quickly,

before you can see what’s on the axes. The advertiser may be trying to create

a lingering false impression about a bogus relationship inside the graph. The

graphed relationship might be as valid as that other staple of TV advertising —

scientific proof via animated cartoon: Tiny animated scrub brushes cleaning

cartoon teeth might not necessarily guarantee whiter teeth for you if you buy

the product. (I know that’s off-topic, but I had to get it in.)

Graphing a Categorical Variable as

Though It’s a Quantitative Variable

Is Just Wrong

So you’re just about ready to compete in the Rock-Paper-Scissors World

Series. In preparation for this international tournament, you’ve tallied all

your matches from the past ten years, listing the percentage of times you

won when you played each role.

To summarize all the outcomes, you’re about to use Excel’s graphics capa-

bilities to create a graph. One thing’s sure: Whatever your preference rock-

paper-scissors-wise, the graph absolutely, positively had better NOT look like

Figure 19-1.

So many people create these kinds of graphs — people who should know

better. The line in the graph implies continuity from one point to another.

With these data, of course, that’s impossible. What’s between Rock and

Paper? Why are they equal units apart? Why are the three categories in that

order? (Can you tell this is my pet peeve?)

27 454060-ch19.indd 37227 454060-ch19.indd 372 4/21/09 7:38:14 PM4/21/09 7:38:14 PM

373

Chapter 19: Ten Statistical and Graphical Tips and Traps

Figure 19-1:

Absolutely

the wrong

way to

graph cat-

egorical

data.

Simply put, a line graph is not the proper graph when at least one of your

variables is a set of categories. Instead, create a column graph. A pie chart

works here, too, because the data are percentages and you have just a few

slices. (See Chapter 3 for Yogi Berra’s pie-slice guidelines.)

When I wrote the first edition of this book, I whimsically came up with the idea

of a Rock Paper Scissors World Series for this example. Between then and now,

I found out . . . there really is one! (The World RPS Society puts it on.)

Whenever Appropriate, Include

Variability in Your Graph

When the points in your graph represent means, make sure that the graph

includes the standard error of each mean. This gives the viewer an idea of

the variability in the data — which is an important aspect of the data. Here’s

another plug: In Chapter 20, I show you how to do that in Excel.

Means by themselves don’t always tell you the whole story. Take every

opportunity to examine variances and standard deviations. You may find

some hidden nuggets. Systematic variation — high values of variance associ-

ated with large means, for example — might be a clue about a relationship

you didn’t see before. (Appendix C shows you some additional ways to

explore data.)

27 454060-ch19.indd 37327 454060-ch19.indd 373 4/21/09 7:38:14 PM4/21/09 7:38:14 PM

374

Part V: The Part of Tens

Be Careful When Relating Statistics-Book

Concepts to Excel

If you’re serious about doing statistical work, you’ll probably have occasion

to look into a statistics text or two. Bear in mind that the symbols in some

areas of statistics aren’t standard: For example, some texts use M rather

than to represent the sample mean, and some represent a deviation from the

mean with just x.

Connecting textbook concepts to Excel’s statistical functions can be a chal-

lenge, because of the texts and because of Excel. Messages on dialog boxes

and in help files might contain symbols other than the ones you read about,

or they might use the same symbols but in a different way. The discrepancy

might lead you to make an incorrect entry into a parameter in a dialog box,

resulting in an error that’s hard to trace.

27 454060-ch19.indd 37427 454060-ch19.indd 374 4/21/09 7:38:15 PM4/21/09 7:38:15 PM

Chapter 20

Ten Things (Twelve, Actually) That

Didn’t Fit in Any Other Chapter

In This Chapter

▶ What’s in the forecast?

▶ Visualizing variability

▶ Odds and ends of probability

▶ Looking for independence

▶ Logging out

▶ Importing data

wrote this book to show you all of Excel’s statistical capabilities. My

intent was to tell you about them in the context of the world of statistics,

and I had a definite path in mind.

Some of the capabilities don’t neatly fit along that path. I still want you to be

aware of them, however, so here they are.

Some Forecasting

Here are a couple of useful techniques to help you come up with some fore-

casts. Although they didn’t quite fit into the regression chapter, and they

really didn’t go into the descriptive statistics chapters, so they deserve a sec-

tion of their own.

A moving experience

In many contexts, it makes sense to gather data over periods of time. When

you do this, you have a time series.

28 454060-ch20.indd 37528 454060-ch20.indd 375 4/21/09 7:38:51 PM4/21/09 7:38:51 PM

376

Part V: The Part of Tens

Investors often have to base their decisions on time series — like stock

prices — and the numbers in a time series typically show numerous ups

and downs. A mean that takes all the peaks and valleys into account might

obscure the big picture of the overall trend.

One way to smooth out the bumps and see the big picture is to calculate a

moving average. This is an average calculated from the most recent scores in

the time series. It moves because you keep calculating it over the time series.

As you add a score to the front end, you delete one from the back end.

Suppose you have daily stock prices of a particular stock for the last 20 days,

and you decide to keep a moving average for the most recent 5 days. Start

with the average from days 1–5 of those 20 days. Then average the prices

from days 2–6. Next, average days 3–7, and so on, until you average the final 5

days of the time series.

Excel’s Moving Average data analysis tool does the work for you. Figure 20-1

shows a fictional company’s stock prices for 20 days, and the dialog box for

the Moving Average tool.

Figure 20-1:

Fictional

stock prices

and the

Moving

Average

dialog box.

The figure shows my entries for Moving Average. The Input Range is cells A1

through A21, the Labels in First Row checkbox is checked, and the Interval

is 5. That means that each average consists of the most recent five days.

Cells B2 through B21 are the output range, and I checked the boxes for Chart

Output and for Standard Errors.

The results are in Figure 20-2. Ignore the ugly-looking #N/A symbols. Each

number in Column B is a moving average — a forecast of the price on the

basis of the most recent five days.

28 454060-ch20.indd 37628 454060-ch20.indd 376 4/21/09 7:38:51 PM4/21/09 7:38:51 PM

377

Chapter 20: Ten Things (Twelve, Actually) That Didn’t Fit in Any Other Chapter

Each number in Column C is a standard error. In this context, a standard

error is the square root of the average of the squared difference between the

price and the forecast for the previous five days. So the first standard error

in cell C10 is

Figure 20-2:

The results:

moving

averages

and stan-

dard errors.

The graph (stretched out from its original appearance and with a reformat-

ted vertical axis) shows the moving average in the series labeled Forecast.

Sometimes the forecast matches up with the data, sometimes it doesn’t.

As the figure shows, the moving average smoothes out the peaks and valleys

in the price data.

In general, how many scores do you include? That’s up to you. Include too

many and you risk obsolete data influencing your result. Include too few and

you risk missing something important.

How to be a smoothie, exponentially

Exponential smoothing is similar to a moving average. It’s a technique for

forecasting based on prior data. In contrast with the moving average, which

works just with a sequence of actual values, exponential smoothing takes its

previous prediction into account.

28 454060-ch20.indd 37728 454060-ch20.indd 377 4/21/09 7:38:51 PM4/21/09 7:38:51 PM

378

Part V: The Part of Tens

Exponential smoothing operates according to a damping factor, a number

between zero and one. With α representing the damping factor, the formula is

In terms of stock prices from the preceding example, yt’ represents the pre-

dicted stock price at a time t. If t is today, t-1 is yesterday. So yt-1 is yester-

day’s actual price and y’t-1 is yesterday’s predicted price. The sequence of

predictions begins with the first predicted value as the observed value from

the day before.

A larger damping factor gives more weight to yesterday’s prediction. A

smaller damping factor gives greater weight to yesterday’s actual value. A

damping factor of 0.5 weighs each one equally.

Figure 20-3 shows the dialog box for the Exponential Smoothing data

analysis tool. It’s similar to the Moving Average tool, except for the Damping

Factor box.

Figure 20-3:

The

Exponential

Smoothing

data

analysis tool

dialog box.

I applied Exponential Smoothing to the data from the previous example. I

did this three times with 0.1, 0.5, and 0.9 as the damping factors. Figure 20-4

shows the graphic output for each result.

The highest damping factor, 0.9, results in the flattest sequence of predic-

tions. The lowest, 0.1, predicts the most pronounced set of peaks and valleys.

How should you set the damping factor? Like the interval in the moving aver-

age, that’s up to you. Your experience and the specific area of application are

the determining factors.

In Appendix C, I show you another technique for smoothing. That one is based

on medians.

28 454060-ch20.indd 37828 454060-ch20.indd 378 4/21/09 7:38:52 PM4/21/09 7:38:52 PM