Schmuller J. Statistical Analysis with Excel For Dummies

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

389

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

What could that answer possibly be? 10

means 10 × 10 and that gives you

100. 10

means 10 × 10 × 10 and that’s 1,000. But 453?

Here’s where you have to think outside the dialog box. You have to imag-

ine exponents that aren’t whole numbers. I know, I know . . . how can you

multiply a number by itself a fraction at a time? If you could, somehow, the

number in that 453 equation would have to be between 2 (which gets you to

100) and 3 (which gets you to 1,000).

In the 16th century, mathematician John Napier showed how to do it and

logarithms were born. Why did Napier bother with this? One reason is that

it was a great help to astronomers. Astronomers have to deal with numbers

that are . . . well . . . astronomical. Logarithms ease computational strain in a

couple of ways. One way is to substitute small numbers for large ones: The

logarithm of 1,000,000 is 6 and the logarithm of 100,000,000 is 8. Also, working

with logarithms opens up a helpful set of computational shortcuts. Before

calculators and computers appeared on the scene, this was a very big deal.

Incidentally,

meaning that

You can use Excel to check that out if you don’t believe me. Select a cell and

type

=LOG(453,10)

press Enter, and watch what happens. Then just to close the loop, reverse

the process. If your selected cell is — let’s say — D3, select another cell

and type

=POWER(10,D3)

=10^D3

Either way, the result is 453.

Ten, the number that’s raised to the exponent, is called the base. Because it’s

also the base of our number system and we’re so familiar with it, logarithms

of base 10 are called common logarithms.

28 454060-ch20.indd 38928 454060-ch20.indd 389 4/21/09 7:38:55 PM4/21/09 7:38:55 PM

390

Part V: The Part of Tens

Does that mean you can have other bases? Absolutely. Any number (except 0

or 1 or a negative number) can be a base. For example,

If you ever see log without a base, base 10 is understood, so

In terms of bases, one number is special . . .

What is e?

Which brings me to e, a constant that’s all about growth. Before I get back to

logarithms, I’ll tell you about e.

Imagine the princely sum of $1 deposited in a bank account. Suppose the

interest rate is 2 percent a year. (Good luck with that.) If it’s simple interest,

the bank adds $.02 every year, and in 50 years you have $2.

If it’s compound interest, at the end of 50 years you have (1 + .02)

— which

is just a bit more than $2.68, assuming the bank compounds the interest once

a year.

Of course, if they compound it twice a year, each payment is $.01, and after

50 years they’ve compounded it 100 times. That gives you (1 + .01)

100

, or just

over $2.70. What about compounding it four times a year? After 50 years —

200 compoundings — you have (1 + .005)

200

which results in the don’t-spend-

it-all-in-one-place amount of $2.71 and a tiny bit more.

Focusing on “just a bit more” and a “tiny bit more,” and taking it to extremes,

after one hundred thousand compoundings you have $2.718268. After one

hundred million, you have $2.718282.

If you could get the bank to compound many more times in those 50 years,

your sum of money approaches a limit — an amount it gets ever so close to,

but never quite reaches. That limit is e.

The way I set up the example, the rule for calculating the amount is

28 454060-ch20.indd 39028 454060-ch20.indd 390 4/21/09 7:38:55 PM4/21/09 7:38:55 PM

391

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

where n represents the number of payments. Two cents is 1/50th of a dollar

and I specified 50 years — 50 payments. Then I specified two payments a

year (and each year’s payments have to add up to 2 percent), so that in 50

years you have 100 payments of 1/100th of a dollar, and so on.

To see this in action, enter numbers into a column of a spreadsheet as I have

in Figure 20-14. In cells C2 through C20, I have the numbers 1 through 10 and

then selected steps through one hundred million. In D2, I put this formula

=(1+(1/C2))^C2

and then autofilled to D20. The entry in D20 is very close to e.

Figure 20-14:

Getting to e.

Mathematicians can tell you another way to get to e:

Those exclamation points signify factorial. 1! = 1, 2! = 2 X 1, 3! = 3 X 2 X 1. (For

more on factorials, see Chapter 16).

Excel helps visualize this one, too. Figure 20-15 lays out a spreadsheet with

selected numbers up to 170 in Column C. In D2, I put this formula:

=1+ 1/FACT(C2)

and, as the Formula Bar in the Figure shows, in D3 I put this one:

=D2 +1/ FACT(C3)

Then I autofilled up to D17. The entry in D17 is very close to e. In fact, from

D11 on, you see no change, even if you increase the amount of decimal places.

28 454060-ch20.indd 39128 454060-ch20.indd 391 4/21/09 7:38:55 PM4/21/09 7:38:55 PM

392

Part V: The Part of Tens

Figure 20-15:

Another

path to e.

Why did I stop at 170? Because that takes Excel to the max. At 171, you get an

error message.

So e is associated with growth. Its value is 2.781828 . . . The three dots mean

you never quite get to the exact value (like π, the constant that enables you

to find the area of a circle).

This number pops up in all kinds of places. It’s in the formula for the normal

distribution (see Chapter 8), and it’s in distributions I discuss in Chapter 17.

Many natural phenomena are related to e.

It’s so important that scientists, mathematicians, and business analysts use

it as the base for logarithms. Logarithms to the base e are called natural loga-

rithms. A natural logarithm is abbreviated as ln.

Table 20-2 presents some comparisons (rounded to three decimal places)

between common logarithms and natural logarithms:

Table 20-2 Some Common Logarithms (Log)

and Natural Logarithms (Ln)

Number Log Ln

e 0.434 1.000

10 1.000 2.303

50 1.699 3.912

100 2.000 4.605

453 2.656 6.116

1000 3.000 6.908

28 454060-ch20.indd 39228 454060-ch20.indd 392 4/21/09 7:38:56 PM4/21/09 7:38:56 PM

393

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

One more thing. In many formulas and equations, it’s often necessary to raise

e to a power. Sometimes the power is a fairly complicated mathematical

expression. Because superscripts are usually printed in small font, it can be

a strain to have to constantly read them. To ease the eyestrain, mathemati-

cians have invented a special notation: exp. Whenever you see exp followed

by something in parentheses, it means to raise e to the power of whatever’s

in the parentheses. For example,

Excel’s EXP function does that calculation for you.

Speaking of raising e, when Google, Inc., filed their IPO they said they wanted

to raise $2,718,281,828, which is e times a billion dollars rounded to the near-

est dollar.

On to the Excel functions.

LOGNORMDIST

A random variable is said to be lognormally distributed if its natural loga-

rithm is normally distributed. Maybe the name is a little misleading, because I

just said log means “common logarithm” and ln means “natural logarithm.”

Unlike the normal distribution, the lognormal can’t have a negative number

as a possible value for the variable. Also unlike the normal, the lognormal is

not symmetric — it’s skewed to the right.

Like the Weibull distribution I describe earlier, engineers use it to model the

breakdown of physical systems — particularly of the wear-and-tear variety.

Here’s where the large-numbers-to-small numbers property of logarithms

comes into play. When huge numbers of hours figure into a system’s life

cycle, it’s easier to think about the distribution of logarithms than the distri-

bution of the hours.

Excel’s LOGNORMDIST works with the lognormal distribution. You specify

a value, a mean, and a standard deviation for the lognormal. LOGNORMDIST

returns the probability that the variable is, at most, that value.

For example, the FarKlempt Robotics Inc. has gathered extensive hours-to-

failure data on a universal joint component that goes into their robots. They

find that hours-to-failure is lognormally distributed with a mean of 10 and a

standard deviation of 2.5. What is the probability that this component fails in,

at most, 10,000 hours?

28 454060-ch20.indd 39328 454060-ch20.indd 393 4/21/09 7:38:56 PM4/21/09 7:38:56 PM

394

Part V: The Part of Tens

Figure 20-16 shows the LOGNORMDIST Function Arguments dialog box for

this example. In the X box, I entered ln(10000). I entered 10 into the Mean box

and 2.5 into the Standard_dev box. The dialog box shows the answer, .000929

(and some more decimals).

Figure 20-16:

The

LOGNORM

DIST

Function

Arguments

dialog box.

LOGINV

LOGINV turns LOGNORMDIST around. You supply a probability, a mean,

and a standard deviation for a lognormal distribution. LOGINV gives you the

value of the random variable that cuts off that probability.

To find the value that cuts off .001 in the preceding example’s distribution, I

used the LOGINV dialog box in Figure 20-17. With the indicated entries, the

dialog box shows that the value is 9.722 (and more decimals).

Figure 20-17:

The LOGINV

Function

Arguments

dialog box.

By the way, in terms of hours that’s 16,685 — just for .001.

28 454060-ch20.indd 39428 454060-ch20.indd 394 4/21/09 7:38:56 PM4/21/09 7:38:56 PM

395

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

Array Function: LOGEST

In Chapter 14, I tell you all about linear regression. It’s also possible to have a

relationship between two variables that’s curvilinear rather than linear.

The equation for a line that fits a scatterplot is

One way to fit a curve through a scatterplot is with this equation:

LOGEST estimates a and b for this curvilinear equation. Figure 20-18 shows

the LOGEST function arguments dialog box and the data for this example. It

also shows an array for the results. Before using this function, I attached the

name x to B2:B12 and y to C2:C12.

Figure 20-18:

The

Function

Arguments

dialog box

for LOGEST,

along with

the data and

the selected

array for the

results.

Here are the steps for this function:

1. With the data entered, select a five-row-by-two-column array of cells

for LOGEST’s results.

I selected F4:G8.

2. From the Statistical Functions menu, select LOGEST to open the

Function Arguments dialog box for LOGEST.

28 454060-ch20.indd 39528 454060-ch20.indd 395 4/21/09 7:38:56 PM4/21/09 7:38:56 PM

396

Part V: The Part of Tens

3. In the Function Arguments dialog box, type the appropriate values for

the arguments.

In the Known_y’s box, type the cell range that holds the scores for the

y-variable. For this example, that’s y (the name I gave to C2:C12).

In the Known_x’s box, type the cell range that holds the scores for the

x-variable. For this example, it’s x (the name I gave to B2:B12).

In the Const box, the choices are TRUE (or leave it blank) to calculate

the value of a in the curvilinear equation I showed you or FALSE to set a

to 1. I typed TRUE.

The dialog box uses b where I use a. No set of symbols is standard.

In the Stats box, the choices are TRUE to return the regression statistics

in addition to a and b, FALSE (or leave it blank) to return just a and b. I

typed TRUE.

Again, the dialog box uses b where I use a and m-coefficient where I use b.

4. IMPORTANT: Do NOT click OK. Because this is an array function,

press Ctrl+Shift+Enter to put LOGEST’s answers into the selected

array.

Figure 20-19 shows LOGEST’s results. They’re not labeled in any way, so I added

the labels for you in the worksheet. The left column gives you the exp(b) —

more on that in a moment, standard error of b, R Square, F, and the SS

regression

The right column provides a, standard error of a, standard error of estimate,

degrees of freedom, and SS

residual

. For more on these statistics, see Chapters

14 and 15.

Figure 20-19:

LOGEST’s

results in

the selected

array.

About exp(b). LOGEST, unfortunately, doesn’t return the value of b — the

exponent for the curvilinear equation. To find the exponent, you have to cal-

culate the natural logarithm of what it does return. Applying Excel’s LN work-

sheet function here gives 0.0256 as the value of the exponent.

28 454060-ch20.indd 39628 454060-ch20.indd 396 4/21/09 7:38:56 PM4/21/09 7:38:56 PM

397

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

So the curvilinear regression equation for the sample data is:

or in that exp notation I told you about,

A good way to help yourself understand all of this is to use Excel’s graphics

capabilities to create a scatterplot. (See Chapter 3.) Then right-click on a data

point in the plot and select Add Trendline from the pop-up menu. That opens

the Format Trendline dialog box (Figure 20-20). Click the radio button next to

Exponential, as I’ve done in the figure. Also, as I’ve done in the figure, toward

the bottom of the dialog box click the checkbox next to Display Equation on

Chart.

Figure 20-20:

The Type

tab on

the Add

Trendline

dialog box.

Click Close, and you have a scatterplot complete with curve and equation. I

reformatted mine in several ways to make it look clearer on the printed page.

Figure 20-21 shows the result.

28 454060-ch20.indd 39728 454060-ch20.indd 397 4/21/09 7:38:57 PM4/21/09 7:38:57 PM

398

Part V: The Part of Tens

Figure 20-21:

The scatter-

plot with

curve and

equation.

Array Function: GROWTH

GROWTH is curvilinear regression’s answer to TREND (Chapter 14). You can

use this function two ways — to predict a set of y-values for the x-values in

your sample, or to predict a set of y-values for a new set of x-values.

Predicting y’s for the x’s in your sample

Figure 20-22 shows GROWTH set up to calculate y’s for the x’s I already have.

I included the Formula Bar in this screen shot so you can see what the for-

mula looks like for this use of GROWTH.

Here are the steps:

Figure 20-22:

The Function

Arguments

dialog box

for GROWTH,

along with

the sample

data.

GROWTH

is set up

to predict

x’s for the

sample y’s.

28 454060-ch20.indd 39828 454060-ch20.indd 398 4/21/09 7:38:57 PM4/21/09 7:38:57 PM