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,
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.
Twisty’s has collected the advance sales numbers for the past 30 operating days. The data is located in the Excel Worksheets.
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.
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
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.
1) Pull up J Dillinger’s Bank & Trust data.
2) Place cursor on cell B6, and using the function wizard ( fx )
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%).
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?
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
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)]?
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.
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.
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.
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.