In particular, note that the sample size value in cell B6 changes to 154.486.
2), we now enter the following values in the dialog box that appears (see Figure 6):įigure 6 – Using Goal Seek to determine minimum sample sizeĪfter clicking on the OK button, the worksheet changes to that in Figure 7.įigure 7 – Sample size requirement for Example 2 Using the worksheet in Figure 3 (making sure that the effect size in cell B9 is set to. You must click on the OK button in the Goal Seek Status box to lock in these new values (or Cancel to return to the original worksheet values).Ĭ) We again use Excel’s Goal Seek capability to answer the third question. In particular, we see that the Effect size (cell B9) contains the value 0.23691. Note that the values of a number of cells have changed to reflect the value necessary to obtain power of. After clicking on OK, a Goal Seek Status dialog box appears, and the worksheet from Figure 3 changes to that in Figure 5.įigure 5 – Determining detectable effect size for specified power The second entry must be a value and the third entry must point to a cell that contains a value (possibly blank) and not a formula. Here the first entry must point to a cell that contains a formula. We are requesting that Excel find the value of cell B9 (the effect size) that produces a value of. In the dialog box that appears (see Figure 4) enter the following values Using the worksheet in Figure 3, we now select Data > Data Tools | What-If Analysis. We summarize these calculations in the following worksheet:įigure 3 – Determining power based on effect and sample sizeī) We use Excel’s Goal Seek capability to answer the second question. 80?Ī) As described in Standardized Effect Size, we use the following measure of effect size: What sample size is required to detect an effect of size.What effect size (and mean) can be detected with power.What is the power of the test for detecting a standardized effect of size.We can repeat this calculation for values of μ 1≥ 62.5 to obtain the table and graph of the power values in Figure 2.Įxample 2: For the data in Example 1, answer the following questions: The situation is illustrated in Figure 1, where the curve on the left represents the normal curve being tested with a mean of μ 0 = 60, and the normal curve on the right represents the real distribution with a mean of μ 1 = 62.5. Now suppose that the actual mean is 62.5. The null hypothesis is rejected provided the sample mean is greater than the critical value of x, which is NORM.INV(1 – α, μ, s.e.) = NORM.INV(.95, 60, 1.144) = 61.88. What is the probability of a type II error if the actual mean length is 62.5? The manufacturer wants to check that the mean length of their bolts is 60 mm, and so takes a sample of 110 bolts and uses a one-tail test with α =.
#Statistical calculations in excel how to#
We now show how to estimate the power of a statistical test.Įxample 1: Suppose bolts are being manufactured using a process so that it is known that the length of the bolts follows a normal distribution with a standard deviation of 12 mm. the probability that the null hypothesis is not rejected even though it is false and power is 1 – β. As described in Null Hypothesis Testing, beta ( β) is the acceptable level of type II error, i.e.
0 kommentar(er)
