关键词 > Excel代写
STATISTICAL FUNCTIONS IN EXCEL
发布时间:2023-06-16
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
STATISTICAL FUNCTIONS IN EXCEL
In this appendix, we discuss the statistical functions in Excel that are useful for the material in this text. They will be referenced by Chapter within the text.
The statistical functions can be accessed by clicking on fx at the main menu and then clicking on Statistical Functions in the next submenu. At the next submenu you click on the statistical function you want. Each statistical function has its own submenu where the user supplies the appropriate parameters for the function to work on. These parameters usually include column locations within the spreadsheet where the data lie and in some cases other parameters needed by the function. Before clicking on fx , the cell where the result from using the function should be placed should be the active cell in the spreadsheet. A nice feature of Excel is that if a cell is made active, then the formula bar displays the function used to compute the result for that cell. Thus, all calculations are self-documented for further reference. There are other statistical commands that are available in the Tools menu, Data Analysis sub-menu. There are beyond the scope of this appendix.
CHAPTER 2 Descriptive Statistics |
We have entered the cholesterol levels before adopting a vegetarian diet from Table 2.1 (study guide, Chapter 2) in Table A. 1 in cells C5–C28. To compute the arithmetic mean, click on fx , Statistical Functions, and then Average. Another screen will appear asking for the range of columns defining the dataset where the average is to be computed. Since the 1st value is in C5 and the last value is in C28, we enter C5:C28. The arithmetic mean is displayed to the right of Average(C5:C28). A similar approach is used for the median (median), standard deviation (Stdev), Variance (Var) and Geometric Mean (GeoMean). To compute specific percentiles, we specify the range of data columns (i.e., C5:C28) and then the percentile desired which must be between 0 and 1. The 10th (153. 1) and 90th (237.4) percentiles are given in the spreadsheet. |
CHAPTER 4 Discrete Probability Distributions |
BINOMDIST
BINOMDIST is the statistical function used to compute individual probabilities and cumulative probabilities for the binomial distribution. Suppose we want to compute the probability of 5 successes (k) in 26 trials (n), where the probability of success on one trial is .34 (p). The function BINOMDIST requires 4 parameters to perform this computation. The 1st parameter is k; the 2nd parameter is n; the 3rd parameter is p; the 4th parameter is the word TRUE if a cumulative probability is desired or the word FALSE if an individual probability is desired. Thus, in this case, we specify BINOMDIST(5, 26, .34, FALSE) = .049 (see Table A.2). If instead we want the probability of obtaining ≤ 5 successes, then we specify BINOMDIST(5, 26, .34, TRUE) = .079. To obtain the probability of ≥ 6 successes, we simply subtract from l and obtain .921.
Table A.1 Cholesterol data before adopting a vegetarian diet from Table 2.1
Average (C5:C28) Median (C5:C28) Stdev (C5:C28) Var (C5:C28) GeoMean (C5:C28) Percentile (C5:C28, 0. 1) Percentile (C5:C28, 0.9) |
195 145 205 159 244 166 250 236 192 224 238 197 169 158 151 197 180 222 168 168 167 161 178 137 187.7917 179 33.15967 1099.563 185.0761 153.1 237.4 |
Table A.2 Example of BINOMDIST and POISSON
Example of BINOMDIST n k p |
||||
26 |
5 |
0.34 |
BINOMDIST(5,26,.34, FALSE) |
0.048519 |
26 |
≤5 |
0.34 |
BINOMDIST(5,26,0.34,TRUE) |
0.079197 |
26 |
≥6 |
0.34 |
1-BINOMDIST(5,26,0.34,TRUE) |
0.920803 |
|
Example of POISSON |
|
|
|
µ |
k |
|
|
|
7.4 |
3 |
|
POISSON(3,7.4, FALSE) |
0.041282 |
7.4 |
≤3 |
|
POISSON(3,7.4,TRUE) |
0.063153 |
7.4 |
≥4 |
|
1-POISSON(3,7.4,TRUE) |
0.936847 |
POISSON
This function works similarly to BINOMDIST. There are 3 parameters, µ, k and (TRUE/FALSE). Suppose we want to compute Pr(X = 3 µ = 7.4) . This is given by POISSON(3, 7.4, FALSE) = .041. (See Table A.2). If instead we want Pr(X ≤ 3 µ
= 7.4) , then we use POISSON(3, 7.4, TRUE) = .063. Note: If we want Pr(X ≥ 4 µ
= 7.4) , then we compute 1 – POISSON(3, 7.4, TRUE)= .937. (not 1 – POISSON(4, 7.4, TRUE))
.........................................................................................................................................................................................
CHAPTER 5 Continuous Probability Distributions
.........................................................................................................................................................................................
There are 4 commands in Excel that are associated with the normal distribution.
NORMDIST
NORMDIST can calculate the pdf and cdf for any normal distribution. The parameters of NORMDIST are NORMDIST(x, mean, sd, TYPE)
Suppose we want to evaluate the pdf at x = 1.0 for a normal distribution with mean = 3 and sd = 2. This is given by
NORMDIST(l.0, 3, 2, FALSE)= pdf of Na3, 22 f distribution evaluated at x = 1.0 = exp
−
2
= 0.121
(See Figure A. 1).
FIGURE A.1
|
1
x
Suppose we want to evaluate the cdf at x = 1.0 for a normal distribution with mean = 3 and sd = 2. This is given by:
NORMDIST(l.0, 3, 2, TRUE)= cdf of a N(3, 2) distribution evaluated at x = 1.0
= Pr x ≤ 1.0 X
~ Na3, 22 f
= Φ = Φ( −1) = .159
(See Figure A.2).
FIGURE A.2
|
1
x
NORMINV
NORMINV can calculate percentiles for any normal distribution. The parameters of NORMINV are
NORMINV(p, mean, sd) = the value x such that Pr X ≤ x
X ~ Namean, sd2 f = p Suppose we want to calculate the 20th percentile of a Na3, 22 f distribution. This is given by
NORMINV(.2, 3, 2) = the value x such that Pr X ≤ x
X ~ Na3, 22 f
= 0.2 .
The corresponding x-value is 1.32.
(See Figure A.3).
FIGURE A.3
|
1.32
x
NORMSDIST
NORMSDIST can calculate the cdf for a standard normal distribution. NORMSDIST has a single parameter x, where
NORMSDIST(x) = PrX ≤ x X
~ N(0, 1)
For example, NORMSDIST(1) = Φ(1) = .841 . (See Figure A.4).