The convenience of Excel software in statistical calculations
In today's field of data processing and statistical analysis, the Excel software is truly a powerful tool, demonstrating unparalleled convenience in calculations. Different from traditional statistical calculation methods, when using the Excel software for calculations, we no longer need to refer to cumbersome statistical tables. This convenience not only saves a great deal of time and effort but also reduces potential human errors that may occur when referring to tables, greatly improving the efficiency and accuracy of calculations.
Calculation of the standard normal distribution table (Z-value table)
Calculation of critical values in the standard normal distribution table
The calculation of critical values in the standard normal distribution table plays an important role in statistical analysis. In Excel software, we can easily obtain the corresponding results through specific formulas. For the calculation of two - sided critical values, the formula is NORMSINV(1 - α/2). Here, α represents the significance level, which is a key parameter in statistics. Different α values correspond to different critical values. For example, when α is set to 0.05, substitute it into the formula NORMSINV(1 - 0.05/2). In the formula editing bar of Excel, enter "=NORMSINV(1 - 0.05/2)" (note that you must add an equal sign in English input mode before the formula. This is the key for Excel to recognize and calculate the formula. If the equal sign is not added, Excel will regard it as ordinary text and no calculation result can be obtained). After pressing the Enter key, the result 1.959963985 will be obtained.
For the calculation of the one-sided critical value, the formula is NORMSINV(1 - α). Also taking α = 0.05 as an example, enter "=NORMSINV(1 - 0.05)" in the Excel formula editing bar, and the calculated result is 1.644853627. We only need to copy and paste these formulas into the Excel formula editing bar, and then substitute the specific value of α to directly obtain the calculation result, without having to look through the thick statistical tables to find the corresponding critical values.
Calculation of the P - value
When we have calculated the Z value, we can directly calculate the P value according to a specific formula, and there is also no need to look up the table. For the calculation of the two - sided P value, the formula is P value = (1 - NORMSDIST(Z value))×2. The NORMSDIST function here is a function in Excel used to calculate the cumulative probability of the standard normal distribution. For example, when the Z value is 1.96, first calculate 1 - NORMSDIST(1.96), getting 0.024997895, and then multiply it by 2 to obtain the two - sided P value of 0.05.
The calculation of the one-sided P-value uses the formula P-value = 1 - NORMSDIST(Z-value). Still taking a Z-value of 1.96 as an example, enter "=1 - NORMSDIST(1.96)" in Excel. The result 0.024997895 obtained is the one-sided P-value, which is usually approximated to 0.025. It should be noted that if the Z-value is negative, we can handle it in two ways. One is to take the absolute value of the Z-value and then substitute it into the above formula for calculation; the other is to directly use NORMSDIST(Z-value) instead of 1 - NORMSDIST(Z-value). For example, when the Z-value is -1.96, enter "=NORMSDIST(-1.96)" in Excel. The result 0.024997895 is consistent with the result calculated by 1 - NORMSDIST(1.96).
Critical Values and Hypothesis Testing
In statistics, Zα is called the critical value of the standard normal distribution, and t(α, n - 1) is called the critical value of the t-distribution (Student's distribution). In the past, obtaining these two values usually required referring to the appendices of statistical textbooks. However, now we can also calculate them using the formulas under the item "Calculation of Critical Values in the Standard Normal Distribution Table" mentioned earlier. The following uses the comparison of whether two rates p1 and p2 come from the same population as an example for illustration.
In this example, the null hypothesis H0 is generally set as p1 being equal to p2, and the corresponding alternative hypothesis H1 is that p1 is not equal to p2. At this time, the formula for calculating the Z - value is Z = (p1 - p2)/sqrt[p1×(1 - p1)/n1 + p2×(1 - p2)/n2], where sqrt represents taking the square root, and n1 and n2 represent the sample sizes of the two samples respectively. After obtaining the Z - value, we have two ways to conduct the hypothesis test.
One is the method of calculating the P - value. Calculate the P - value according to the formula under the item "Calculation of the P - value" mentioned earlier, and then set the significance level according to industry customs. When the P - value < 0.05 (sometimes 0.01, sometimes 0.10), we reject the null hypothesis h0; otherwise, we accept h0. this is a method commonly used in various statistical software.
Another method is to find the critical value. We can look up the two - sided critical value of Z0.05 (sometimes Z0.01 or Z0.10) in the appendix of statistical textbooks. When Z
It should be noted that the calculation formulas for the Z value vary in different situations. If you want to have an in - depth understanding of these calculation methods, we can learn about the relevant knowledge of statistical hypothesis testing. This testing method based on the Z value is generally called the u - test, which is applicable when the population standard deviation is known. When the population standard deviation is unknown but the sample standard deviation is known, the t - test needs to be used, and its calculation process is basically the same as that of the u - test.