Transcript Problem 4.1 1 - Homework Minutes

HIMS 650
* To learn how to use the Excel program, watch these
helpful Youtube.com videos:
https://www.youtube.com/watch?v=8L1OVkw2ZQ8
https://www.youtube.com/watch?v=lJqfZw5j2R0
https://www.youtube.com/watch?v=rpCaVZCURZQ
https://www.youtube.com/watch?v=sYxiYzx0_yY
https://www.youtube.com/watch?v=RIIcRArOCok
https://www.youtube.com/watch?v=qZbWJC7mO4o
https://www.youtube.com/watch?v=csQrfaAXin0
https://www.youtube.com/watch?v=YU-KmgpG-ew
*
HIMS 650
Homework 1
2
HIMS 650
Homework 1
3
* Use the file, Chpt 04-1.xls that is posted in the
Course Content under Homework – Files
Needed
* The next slide shows you a screen shot of
where the Chpt 04-1.xls file is located in the
HIMS 650 classroom
*
HIMS 650
Homework 1
4
Screenshot from HIMS 650 Classroom
*
HIMS 650
Homework 1
5
* Select the variable Age and do the following:
* Using the =MIN(), =MAX(), and =FREQUENCY()
functions, replicate Figure 4.2
* =MIN(), and =MAX() are self explanatory
* =FREQUENCY() is an array function
*
HIMS 650
Homework 1
6
* From Excel help: “FREQUENCY is entered as an
array formula after you select a range of
adjacent cells into which you want the
returned distribution to appear…
* Formulas that return arrays must be entered as
array formulas
* To enter an array formula (for Mac) press
Command + Return
* For Windows, press Ctrl+Shift+Return
*
HIMS 650
Homework 1
7
* My data for Age is listed in cells A2:A101 (A colon or “:” means “to”
*
*
*
*
*
so A2:A101 is read as cells A2 to A101)
My Bin data is listed in cells D2:D7 (I simply copied the Bin numbers
from page 106 of your textbook)
To use =FREQUENCY(), I must put both the data array and the Bin
array into the formula
Therefore, my formula is typed as =FREQUENCY(A2:A101,D2:D7) into
cell E3 (Do NOT press return yet)
Since it is an array function, instead of just using the Return key, I
use both the Command and Return keys
Press Command + Return (for Mac) OR Ctl+Shift+Return (for Windows)
*
HIMS 650
Homework 1
8
* By clicking on both Command+Return, Excel
puts a bracket {} around the formula so that it
now looks like the following (in cell E3):
* {=FREQUENCY(A2:A103,D3:D8)}
* The first time that I use the =FREQUENCY()
function, it returns one number, so I need to
redo it by….
*
HIMS 650
Homework 1
9
* Highlight the cells E2:E8 by clicking on cell E2
and then hold down the shift key and clicking
on cell E8
* With the cells highlighted, click Control + U
(for Mac)
* Next click Command + Return (for Mac) or Ctrl
+ Shift + Return (for Windows)
* All of the cells, E2:E8 should contain numbers
which equal the numbers in Figure 4.2 on page
106
*
HIMS 650
Homework 1
10
* You can learn how to create charts and graphs
by watching the Youtube.com videos listed on
Slide 2
* Don’t forget to add the correct labels when
you create your charts
*
HIMS 650
Homework 1
11
* Generate a cumulative frequency for the
variable Age as well as percentage and
cumulative percentage distributions as shown
in Figure 4.13
* Figure 4.13 shows what your answer should
look like
* Figure 4.14 shows you the formulas to enter
into each cell to make your answer correct
* Part h should be self explanatory
*
HIMS 650
Homework 1
12
Figure 4.13
*
HIMS 650
Homework 1
13
Figure 4.14
*
HIMS 650
Homework 1
14
* Problem 4.1 3 is similar to problem 4.1 1
* Simply follow the procedure to create a frequency
distribution like you did in previous slides, using the
data found in the SWC tab (using the IMR – Infant
Mortality Rate column data) in the Chpt 04-1.xls
spreadsheet (where you previously found the data
for Age)
* Replicate figure 4.18. Don’t forget to create bins
before using the =FREQUENCY() formula. What are
your Bin numbers?
* Remember that =FREQUENCY() requires both the
IMR data and the Bin data
*
HIMS 650
Homework 1
15
* I like using Chart Quick Layouts number 9, so I
can easily edit the axes and the Chart title
* The next slide shows my reproduction of Figure
4.18
*
HIMS 650
Homework 1
16
Graph of Reproduced Figure 4.18
*
HIMS 650
Homework 1
17
* By now you should be very familiar with
creating frequency data
* Generate a frequency distribution of five Bins
for under-five mortality (USMR) and produce a
column graph for that variable
* The only difference for part b is that you
create your own Bins. If you need 5 Bins, how
would you get the numbers?
* Finally, use the textbook to discuss the
skewedness of the chart that you just created
*
HIMS 650
Homework 1
18
* Use the variable Sex on the Hospital Charges sheet in Chpt 041.xls and Create a frequency distribution using the pivot table
…, using Count of sex in the DATA field.
* The textbook teaches you how to build a Pivot table using
Excel 2007.
* To learn how to use pivot tables in Excel 2013, use this
website:
https://support.office.com/en-us/article/Create-a-PivotTableto-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9f99134456576
* Here is a website to teach you how to build a pivot table using
Excel for Mac 2011
http://www.techonthenet.com/excel/pivottbls/create2011.php
*
HIMS 650
Homework 1
19
* You create a pivot table in this problem, using
different data. Replicate Figure 4.34
* For this problem, you need to download Chpt
04-2.xls from the classroom. The next slide
shows the location of that data
*
HIMS 650
Homework 1
20
Screenshot from HIMS 650 Classroom
*
HIMS 650
Homework 1
21
* Use columns b and d for your pivot table under
the tab labeled DRG
* Since you have already done a pivot table, this
one should be easy for you to create
* You have now finished all of the required
problems for Chapter 4!
* Congratulations!
Now onto Chapter 5 problems
*
HIMS 650
Homework 1
22
HIMS 650
Homework 1
23
* Probability – the book does an excellent job of
explaining probability
* Terms to know: Simple (or Marginal)
Probability, Conditional probability, and Joint
Probability
* Figure 5.4 on page 152 is a good summary of
these 3 concepts
*
HIMS 650
Homework 1
24
* You are back to using pivot table capabilities of Excel for
part a. It’s a good thing that you practiced them in Chapter
4! (Don’t forget to drag the field name, Shift, to the Column
Tables area)
* See Figure 5.6 and the paragraph under that figure to do
parts b and c
* See Figure 5.9 and the paragraph titled, Calculating Joint
probability “or” Values… to complete part d
* You learn how to do part e on page 159-160, Bayes’ Theorem
and Conditional Probabilities
* Don’t forget to answer the last question of part e: confirm
that reason for arrival and time of arrival are not
independent
*
HIMS 650
Homework 1
25
Figure 5.6
Here is the equation that you will use in problem 1. The
next picture shows you how to use it
Figure 5.6 Equation
*
HIMS 650
Homework 1
26
Figure 5.6 Equation
In this picture, n = 5, x = 3, p = 0.5, and 1-p = 1-.5 or .5.
The problem gives you n, x, and p. Simply use the information
given in the problem in this equation to find the answers to
parts a and b. Simple!
*
HIMS 650
Homework 1
27
Figure 5.7
This equation is similar to equation 5.6. In the problem, you
are given both x and n. The only concept that you need to
Understand is factorial. From page 168, “The term “factorial”
means to multiply the number to which the factorial refers by
Every number less than it in the number sequence. So, for example,
5! Is 5x4x3x2x1 or 120.” Just substitute, use factorial, and Voila!
*
HIMS 650
Homework 1
28
Figure 5.8
This problem is easy! You have already done problems 5.1 and 5.2.
Simply do them again with the information in problem 5.3,
Then multiply the answers together. Voila!
*
HIMS 650
Homework 1
29
* We now turn out attention to the =BINOMDIST()
function in Excel. (Excel has a function that
will calculate the preceding equations for you)
* From page 169, “The =BINOMDIST() function
takes 4 arguments. These are the number of
emergency visits…, the number of visits
observed…, the probability of an emergency…,
and a 0 or 1 to indicate whether the value to
be determined is the actual probability or the
cumulative probability.”
*
HIMS 650
Homework 1
30
* Read this website to learn more about the Binomial
Distribution:
http://stattrek.com/probability-distributions/binomial.aspx
* From the website, “The binomial probability refers to the
probability that a binomial experiment results in exactly X
successes.”
* From the website, “A cumulative binomial probability refers
to the probability that the binomial random variable falls
within a specified range (e.g., is greater than or equal to a
stated lower limit and less than or equal to a stated upper
limit)
* Know the difference between a binomial probability and a
cumulative binomial probability
*
HIMS 650
Homework 1
31
* Excel’s =BINOMDIST() function: read this website to
learn how Excel calculates =BINOMDIST()
https://support.microsoft.com/en-us/kb/827459/
* Remember that you needed to learn the difference
between binomial probability and cumulative binomial
probability?
* From the website, “Then cumulative =TRUE, the
BINOMDIST(x, n, p, cumulative) function returns the
probability of x or fewer successes in n independent
Bernoulli trials…When cumulative = FALSE, BINOMDIST
returns the probability of exactly x successes.”
* Most of the time, cumulative will = 1 in your problems
*
HIMS 650
Homework 1
32
* All of the information needed for the
=BINOMDIST() function is in problem 5.3 7
* Use =BINOMDIST() to solve problem 5.3 7
* Read the problem carefully. Look for language
like “…10 patients or more out of 15…” Will
cumulative = 0 or 1 in this case?
* This problem should be easy for you to solve
since Excel does all of the arithmetic for you
*
HIMS 650
Homework 1
33
* For part a, use =POISSON(x, Average number of arrivals, 0)
[=POISSON is compatible with Excel 2007 and =POISSON.DIST
is used with Excel 2010 and later]
https://www.khanacademy.org/math/probability/randomvariables-topic/poisson_process/v/poisson-process-1
* For part b, use the equation 5.9 on page 177 which looks like
the following when typed into Excel: = (Average^x*EXP(1)^(Average))/FACT(x)
* For part c, use the numbers generated by part b when you
calculate through 9, e.g. calculate POISSON when x = 0, 1, 2,
3, 4, 5, 6, 7, 8, 9 etc…
* Then use the answers to the POISSON equations to generate
a graph
* That’s it!
*
HIMS 650
Homework 1
34
HIMS 650
Homework 1
35