Schmuller J. Statistical Analysis with Excel For Dummies

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

399

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

1. With the data entered, select a cell range for GROWTH’s answers.

I selected D2:D12 to put the predicted y’s right next to the sample y’s.

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

Function Arguments dialog box for GROWTH.

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

I’m not calculating values for new x’s here, so I leave the New_x’s box

blank.

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

a, or FALSE to set a to 1. I entered TRUE. (I really don’t know why you’d

enter FALSE.) Once again, the dialog uses b where I use a.

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

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

column.

Figure 20-23 shows the answers in D2:D12.

Figure 20-23:

The results

of GROWTH:

Predicted

y’s for the

sample x’s.

Predicting a new set of y’s for a new set of x’s

Here, I use GROWTH to predict y’s for a new set of x’s. Figure 20-24 shows

GROWTH set up for this. In addition to the array named x and the array

named y, I defined New_x as the name for B15:B22, the cell range that holds

the new set of x’s.

Figure 20-24 also shows the selected array of cells for the results. Once

again, I included the Formula Bar to show you the formula for this use of the

function.

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

400

Part V: The Part of Tens

Figure 20-24:

The

Function

Arguments

dialog box

for GROWTH,

along

with data.

GROWTH

is set up to

predict y’s

for a new

set of x’s.

To do this, follow these steps:

1. With the data entered, select a cell range for GROWTH’s answers.

I selected C15:C22.

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

Function Arguments dialog box for GROWTH.

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

the arguments.

In the Known_y’s box, enter 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, enter 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 New_x’s box, enter the cell range that holds the new scores for

the x-variable. That’s New_x (the name I gave to B15:B22).

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

or FALSE to set a to one. I typed TRUE. (Again, I really don’t know why

you’d enter FALSE.)

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

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

column.

Figure 20-25 shows the answers in C15:C22.

28 454060-ch20.indd 40028 454060-ch20.indd 400 4/21/09 7:38:58 PM4/21/09 7:38:58 PM

401

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

Figure 20-25:

The Results

of GROWTH:

Predicted

y’s for a

New Set

of x’s.

When Your Data Live Elsewhere

In a few places throughout the book, I use data from the history of baseball.

Happily, the Web site www.baseball-reference.com provides these data.

How did I get these data from a web page into a worksheet?

It’s way easier than you might think. To take the first giant step, I select

Data| Get External Data

and then choose the type of data source I’m dealing with. Figure 20-26 shows

how to do it.

Figure 20-26:

Selecting

Data | Get

External

Data.

As you can see, you can import from a number of different types of sources.

Here, I just discuss data import from the web. To illustrate, I import data

from the Web site of Wiley Publishing.

28 454060-ch20.indd 40128 454060-ch20.indd 401 4/21/09 7:38:58 PM4/21/09 7:38:58 PM

402

Part V: The Part of Tens

After I choose From Web, the New Web Query window opens within the

spreadsheet. The window displays the home page that appears when I open

my browser. In the Address box, I enter the address of the Web page that

supplies the data. Specifically, I typed Wiley.com into the Address box. When

I clicked Go, the window opened the page with this URL:

www.wiley.com/WileyCDA/

Figure 20-27 shows the appearance of the New Web Query window with

this URL.

Figure 20-27:

The New

Web Query

window

displaying a

Wiley Web

page.

The important items to note here are the right-pointing arrows throughout

the window. You can’t see it on the figure, but those arrows are in yellow.

Each one points to a grouping of data you can import into Excel.

I clicked the arrow next to ABOUT WILEY. Then I clicked Import. The Import

Data dialog box opens and gives the choice of putting the data into the exist-

ing worksheet or a new one. I chose the existing worksheet, and specified cell

A1 as the starting point for the table. (I could have also selected a cell before

I opened the New Web Query window.) Then I clicked OK. Figure 20-28 shows

the result.

28 454060-ch20.indd 40228 454060-ch20.indd 402 4/21/09 7:38:58 PM4/21/09 7:38:58 PM

403

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

Figure 20-28:

Data

imported

from a Web

page into a

worksheet.

That’s all there is to it!

28 454060-ch20.indd 40328 454060-ch20.indd 403 4/21/09 7:38:59 PM4/21/09 7:38:59 PM

404

Part V: The Part of Tens

28 454060-ch20.indd 40428 454060-ch20.indd 404 4/21/09 7:38:59 PM4/21/09 7:38:59 PM

Appendix A

When Your Worksheet

Is a Database

In This Chapter

▶ Databases in Excel

▶ Statistics in databases

▶ Pivot tables

xcel’s main function in life is to perform calculations. As the chapters in

this book show, many of those calculations revolve around built-in sta-

tistical capabilities.

You can also set up a worksheet to store information in something like a

database, although Excel is not as sophisticated as a dedicated database

package. Excel offers database functions that are much like its statistical

functions, so I thought I’d familiarize you with them.

Introducing Excel Databases

Strictly speaking, Excel provides a data list. This is an array of worksheet

cells into which you enter related data in a uniform format. You organize the

data in columns, and you put a name at the top of each column. In database

terminology, each named column is a field. Each row is a separate record.

This type of structure is useful for keeping inventories, as long as they’re not

overly huge. You wouldn’t use an Excel database for recordkeeping in a ware-

house or a large corporation. For a small business, however, it might fit the bill.

The Satellites database

Figure A-1 shows an example. This is an inventory of the classic satellites in

our solar system. By “classic,” I mean that astronomers discovered most of

29 454060-bapp01.indd 40529 454060-bapp01.indd 405 4/21/09 7:39:35 PM4/21/09 7:39:35 PM

406

Statistical Analysis with Excel For Dummies, 2nd Edition

them before the 20th century, via conventional telescopes. The three 20th-

century entries are so dim that astronomers discovered them by examin-

ing photographic plates. Today’s super telescopes and space probes have

revealed many more satellites that I didn’t include.

Figure A-1:

The

Satellites

database.

The database is in cells B10:G35. I defined Satellites as the name of this cell

range. Notice that I included the field names in the range. (Reread Chapter 2

if you don’t remember how to name a cell range.)

The Name field provides the name of the satellite; the Planet field indicates

the planet around which the satellite revolves.

Orbital_Period_Days shows how long it takes for a satellite to make a com-

plete revolution around its planet. Our Moon, for example, takes a little over

27 days. A couple of records have negative values in this field. That means they

revolve around the planet in a direction opposite to the planet’s rotation.

Average Distance_X_1000_km is the average distance from the planet to the

satellite in thousands of kilometers. The last two fields provide the year of

discovery, and the astronomer who discovered the satellite. For our Moon, of

course, those two are unknown.

After you label each field, you attach a name to each cell that holds a field

name. Important point: The range for each name is just the cell that holds the

field name, not the whole column of data. So here, I define Name as the name

of the cell labeled Name.

29 454060-bapp01.indd 40629 454060-bapp01.indd 406 4/21/09 7:39:35 PM4/21/09 7:39:35 PM

407

Appendix A: When Your Worksheet Is a Database

Okay, I worked really hard to set up the premise for the preceding sentence.

Here are two examples that are easier to follow: I define Planet as the name of

cell C10, and Orbital_Period_Days as the name of D10, and so on. Now I can

use these field names in Excel’s database formulas.

The criteria range

I copied the column headers — excuse me, field names — into the top row. I

also put some information into nearby cells. This area is for the criteria range.

This range enables you to use Excel’s database functions to ask (and answer)

questions about the data. Database honchos call this “querying.” Criteria are

a part and parcel of each database function. (“Criteria” is plural. The singular

form is “criterion.”)

It’s not necessary to have this range at the top of the worksheet. You can des-

ignate any range in the worksheet as the criteria range.

When you use an Excel database function it’s in this format:

=FUNCTION(Database, Field, Criteria)

The function operates on the specified database, in the designated field,

according to the indicated criteria.

For example, if you want to know how many satellites revolve around Saturn,

you select a cell and enter

=DCOUNT(Satellites,Average_Distance_X_1000_km,C1:C2)

Here’s what this formula means: In the database (B1:G35), DCOUNT tallies up

the amount of number-containing cells in the Average_Distance_X_1000_km

field, constrained by the criterion specified in the cell range C1:C2. That cri-

terion is equivalent to Planet = Saturn. Note that a criterion has to include at

least one column header . . . uh . . . field name from the criteria range, and at

least one row. Bear in mind that you can’t use the actual field name in the cri-

teria. You use the cell ID (like C1).

When you include more than one row, you’re saying “or.” For example, if

your criterion happens to be G1:G3, you’re specifying satellites discovered by

Galileo or Cassini.

When you include more than one column in a criterion, you’re saying “and.”

If your criterion is E1:F2, you’re specifying satellites farther than 150,000 km

from their planets and discovered after 1877.

29 454060-bapp01.indd 40729 454060-bapp01.indd 407 4/21/09 7:39:36 PM4/21/09 7:39:36 PM

408

Statistical Analysis with Excel For Dummies, 2nd Edition

The format of a database function

The formula I just showed you

=DCOUNT(Satellites,Average_Distance__X_1000_km,C1:C2)

is accessible via a Function Arguments dialog box, as is the case for all the

other worksheet functions in Excel. Figure A-2 shows the equivalent dialog

box for the preceding formula, set against the backdrop of the database and

the criteria range.

Figure A-2:

The

DCOUNT

Function

Arguments

dialog box.

How do you open this dialog box? Unlike the Statistical Functions or the

Math & Trig Functions, Database Functions do not reside on their own menu.

Instead you click the Insert Function button to open the Insert Function

dialog box. Then in that dialog box, you scroll down the list of functions until

you find the database function you’re looking for.

Here’s an example:

1. Select a worksheet cell.

As Figure A-2 shows, I selected H6.

2. Click the Insert Function button (it’s labeled fx) to open the Insert

Function dialog box.

29 454060-bapp01.indd 40829 454060-bapp01.indd 408 4/21/09 7:39:36 PM4/21/09 7:39:36 PM