# TWISTY’S PLAYER’S THEATRE

Excel Stat Pack 1

TWISTY’S PLAYER’S THEATRE

## Measures of Descriptive Statistics

1) Measures of Central Tendency: Mode, Median, Mean

2) Measures of Variability: Sample Standard deviation, Variance,

Range, Skewness, and Inter-quartile Range,

## The Facts

Twisty’s Player’s Theater is a local theatre mostly performing avant-garde plays from unheralded playwrights. Twisty’s resident director is dedicated to her art, but the theatre business is a risky business. Thus, for the 135-seat theatre advance sales is highly important.

The Data

Twisty’s has collected the advance sales numbers for the past 30 operating days. The data is located in the Excel Worksheets.

Steps:

1) Open Excel.

2) Pull up Twisty’s Player Theatre data.

3) Select Data Tab from the menu bar and *click* on Data Analysis.

4) Scroll down and select Descriptive Statistics. Click OK.

5) In the text box for Input Range type: A5:A35. This is the

range of cells the data is located in, including the label.

6) Grouped by: Columns (Should be the default choice).

7) Click: Output Range – Type in E5. This will place the upper-left cell of our output at E5.

Summary Statistics.

Labels in the First Row,

Confidence Level for Mean – 95%,

Kth Largest and Smallest — 7

This will give us the approximate values for P75 and P25, the 7th largest and 7th smallest values out of 30 data points. Subtracting the Kth Largest value from the Kth Smallest value will give you the approximate value for the inter-quartile range.

9) *Click*: OK.

10) Widen Column width so all results are legible.

## J. DILLINGER’S BANK & TRUST

Discrete Probability Distributions

1. Binomial Distribution

### The Facts

J. Dillinger’s Bank & Trust is a community bank in the town of Hardscrabble, CA. Based upon past experience they know that 35 percent (.35) of the people entering will make a withdrawal. The rest are depositing money, checking accounts, or performing loan transactions. Suppose 20 people enter the bank. What is the probability that 10 people make a withdrawal? That less than 3 make a withdrawal?

These questions can be answered using the binomial distribution.

Note: There are NO “lettered cells” for this example.

Steps:

1) Pull up J Dillinger’s Bank & Trust data.

2) Place cursor on cell B6, and using the function wizard ( *fx* )

*select* BINOMDIST.

3) In the first text box Type: A6.

4) For trials Type: 20.

5) For probability Type: .35

6) For cumulative Type: False. (Or input zero.) And click OK.

7) The result is for the probability no one will make a

withdrawal. *Click and drag* to calculate the probabilities for

1-20 people making withdrawals.

Note: Some cells may contain “E-#” which is scientific notion meaning the decimal point is really “#” places to the left for the number in the cell. Excel does this because the cell is not wide enough. You can leave it as is.

8) To calculate the Cumulative Binomial, rather than individual probabilities, place the cursor in cell D6. Follow the above steps, only Type TRUE (or input the number 1) in step 7.

As you the number of trials approaches 20 the cumulative probability will approach 1 (100%).

2. Hypergeometric

Suppose 12 employees at J. Dillinger’s applied for the 2 open positions of loan officer, eight applicants being women. Assuming all 12 were qualified, what is the probability no women were promoted to loan officer? One woman?

Steps:

1) Place the cursor cell B31.

2) Using the function wizard ( *fx* ) select HYPGEOMDIST

3) Dialog box inputs:

Sample_s (1st box) -– A31

Number_sample (2d box) — 2

Population_s (3d box) — 8

Number_pop (4th box) –- 12

Some versions will have a fifth input box:

Cumulative –- False (Noncumulative)

4) *Click and drag* the formula to fill in cells B32 and B33.

OFF ROAD RAGE, INC.

The Normal Distribution

The Facts

Off Road Rage Inc. manufactures mountain bike tires. The average life of their tires is 1,150 miles with a standard deviation of 150 miles. What percentage of their tires will wear out before 950 miles [P (X < 950)]? What percentage of tires will last more than 1300 miles [P (X > 1300)]?

Steps:

1) Pull up the Off Road Rage Inc. data.

2) Select cell B6. Then using the function wizard ( *fx* ) select

NORMDIST. (Do not select NORMSDIST which is for the standard normal

distribution (mean = 0, std. = 1).

3) Fill in the text boxes as follows:

X — A6 (location of first value).

Mean — 1150

Std — 150

Cumulative — False

4) Click and drag to obtain the probabilities for the data set.

5) To obtain the cumulative probabilities repeat the above steps,

except Type: True instead of False. Click and drag to obtain

the probabilities for the complete set. Again, the cumulative

probability will approach 1 (100%) near the end of the column.

Graphing Results

1) Select cells B6: B24.

2) *Click *theInsert Tab from toolbar.

3) *Select* Area (Chart type)

4) Select the first picture icon under 2-D Area.

Note: Your chart should closely resemble a normal distribution.

5) To graph the cumulative Probabilities select cells C6:C24.

6) *Click* the Insert Tab from the toolbar.

7) Select Area.

8) Select the first picture icon under 2-D Area

Note: Your chart should be upward sloping, flatting out near 1 (100%)

9) Label your chart to help the reader understand it.

10) Move both charts to the right of your data, if you are going to print them on the same sheet. Otherwise print them on a separate page.

GRAPHICS

Done well, tables and graphs are useful and effective tools in telling a story, trying to convince others of a position, and/or explaining a phenomenon. Done poorly and graphs can be confusing and misleading.

Creating Tables and Graphs using the Raw Survey Results page —

A. Creating Table CATEGORIES

1) Copy and Paste the Major data (Column A) to a new page.

2) Select Data Tab, Filter Advanced. Dialog box inputs:

•Select Copy to a New Location

•List Range: the column of data you copied & pasted, including

the heading (Major) (Ex: Sheet1!$A$1:$A$75)

•Copy to: input any open cell on the page (Ex: Cell D1)

•Select Unique Records Only (Leave Criteria Range blank)

3) You should now have a new list of all the majors (Categories)

B. Creating table FREQUENCIES

4) Type “No. of Students” in the cell to right of the new heading

“Major” (Ex: Cell E1)

5) Put the cursor in the cell below “No. of Students” (Ex: Cell E2)

6) Select the *fx* icon and choose COUNTIF. Dialog box inputs:

•Range: the column of raw survey data you copied & pasted,

NOT including the heading MAJOR (Ex: A2:A75)

•Criteria: Your newly created list of majors (NOT including the

heading MAJOR) (Ex: D2:D7)

•In the formula bar where you see the COUNTIF formula being written,

highlight the cells and hit the F4 key*. Click OK.

•Drag the formula to fill in the frequency counts for each major

•You should now have a summary data table similar to those on the

Summary Data Tables page of the Class Survey workbook.

7) Copy & Paste your table anywhere else on the page but when you paste

choose Paste Values.**

•DELETE the raw survey data and original table.

C. Creating a Chart

8) Highlight your data, select Insert, and choose an appropriate chart.

See Graphs of Summary Data page for examples.

*The F4 key will place dollar signs before the cell letter and number (A2 will become $A$2), thus “locking” them. That is, the formula will reference cell A2 even though it is dragged into other cells. If your F4 key does not place $ into the cell, do it manually. In the formula bar you should see something like: =COUNTIF($A$2:$A$75,$D$2:$D$7).

**If you do not copy & paste the table (using PASTE VALUES), when you delete the raw survey data the cells in the table will show the #REF! error rather than the values, as the formulas in the table reference

the original, raw data.

C. Campus Engagement: Table & Graph

1) Using the Campus Engagement data (Column B from the Raw Survey

Results page) and steps similar to the previous example create a

table and graph.

2) Choose an appropriate graph style that is different from the one you

created for the Major data.

Tables & Graphs: clean up all tables and all graphs, labeling, titling, appropriate axis, and setting them properly within the document.

For the Student Majors and Campus Engagement exercise turn in the tables and their corresponding graphs only. You can arrange the two tables and two graphs onto one page.

Note: Tables and graphs can also be created using the PIVOT TABLE function under the INSERT tab.

END NOTE:

Turn in one copy of you work. FULL POINTS requires all pages to be (A) in the same order as given in the Fact Set and (B) formatted as directed in the Mandatory Standards document contained in the Read First! file.

## We've got everything to become your favourite writing service

### Money back guarantee

Your money is safe. If we fail to satisfy your expectations, you can always request a refund and get your money back.

### Confidentiality

We don’t share your private information with anyone. What happens on our website stays on our website.

### Our service is legit

We provide you with a sample paper on the topic you need, and this kind of academic assistance is perfectly legitimate.

### Get a plagiarism-free paper

We check every paper with our plagiarism-detection software, so you get a unique paper written for your particular purposes.

### We can help with urgent tasks

Need a paper tomorrow? We can write it even while you’re sleeping. Place an order now and get your paper in 8 hours.

### Pay a fair price

Our prices depend on urgency and level of study. If you want a cheap essay, place your order with as much time as possible. Our prices start from $11 per page.