Statistical Analyses in Medicine

 

1.Describing Data. 2

1.1.Measures of Central Tendency. 2

1.2.Measures of Variation. 2

1.3.Standardized Z-Score. 3

2.Hypothesis Testing Basics. 4

2.1.The Normal Distribution. 4

2.2.Using theoretical sampling probability distributions. 4

2.3.Alpha and the role of the distribution tails. 4

2.3.1 The 1-tailed vs. 2-tailed statistical tests. 5

2.4. Skewness/Kurtosis. 5

2.4.1.Calculating and Assessment of the Skewness and Kurtosis. 6

2.5.Hypothesis Testing. 6

2.6.Steps to Hypothesis Testing. 7

2.6.1.General Assumptions. 8

2.6.2.State the Hypothesis. 8

2.6.3.Set the Rejection Criteria. 9

2.6.4.Compute the Test Statistic. 9

2.6.5.Decide Results of Null Hypothesis. 9

3. Calculating some statistical criterias. 9

3.1.Interval Estimation For Means (Margin of Error) 9

3.1.1.Steps. 9

3.1.2.Estimation Formula. 9

3.2.Comparing a Population Mean to a Sample Mean (T-test) 9

3.3.Comparing Two Independent Sample Means (T-test) With Homogeneity of Variance ………….. 10

3.4.Two Independent Sample Means (Cochran and Cox). T-test without Homogeneity of Variance ………….. 11

3.5.Computing F-ratio. 11

4.Statistical Analysis in Excel 12

4.1.Correlating Data. 12

4.2.Ranking and Percentiles. 14

4.3.Performing Statistical Tests. 16

4.5.Conducting ANOVA.. 18

5. Hypothesis Testing Example. 20

5.1.Problem.. 20

5.2.Solution. 20

Conclusion. 23

5.3.Additional and reference information. 23

 

1.Describing Data

1.1.Measures of Central Tendency

Mode: The most frequently occurring score. A distribution of scores can be unimodal (one score occurred most frequently), bimodal (two scores tied for most frequently occurring), or multimodal. In the table below the mode is 32. If there were also two scores with the value of 60, we would have a bimodal distribution (32 and 60).

Median:The point on a rank ordered list of scores below which 50% of the scores fall. It is especially useful as a measure of central tendency when there are very extreme scores in the distribution, such as would be the case if we had someone in the age distribution provided below who was 120. If the number of scores is odd, the median is the score located in the position represented by (n+1)/2. In the table below the median is located in the 4^th position (7+1)/2 and would be reported as a median of 42. If the number of scores are even, the median is the average of the two middle scores. As an example, if we dropped the last score (65) in the above table, the median would be represented by the average of the 3^rd (6/2) and 4^th score, or 37 (32+42)/2. Always remember to order the scores from low to high before determining the median.

Mean: The sum of the scores ( ) is divided by the number of scores (n) to compute an arithmetic average of the scores in the distribution. The mean is the most often used measure of central tendency. It has two properties: 1) the sum of the deviations of the individual scores (Xi) from the mean is zero, 2) the sum of squared deviations from the mean is smaller than what can be obtained from any other value created to represent the central tendency of the distribution. In the above table the mean age is 44.29 (310/7).

Weighted Mean: When two or more means are combined to develop an aggregate mean, the influence of each mean must be weighted by the number of cases in its subgroup.

1.2.Measures of Variation

Range: The difference between the highest and lowest score (high-low). It describes the span of scores but cannot be compared to distributions with a different number of observations. In the table below, the range is 41 (65-24).

Variance:The average of the squared deviations between the individual scores and the mean. The larger the variance the more variability there is among the scores. When comparing two samples with the same unit of measurement (age), the variances are comparable even though the sample sizes may be different. Generally, however, smaller samples have greater variability among the scores than larger samples. The sample variance for the data in the table below is 251.57. The formula is almost the same for estimating population variance. See formula in Appendix.

Standard deviation: The square root of variance. It provides a representation of the variation among scores that is directly comparable to the raw scores. The sample standard deviation in the following table is 15.86 years.

1.3.Standardized Z-Score

A standardized z-score represents both the relative position of an individual score in a distribution as compared to the mean and the variation of scores in the distribution. A negative z-score indicates the score is below the distribution mean. A positive z-score indicates the score is above the distribution mean. Z-scores will form a distribution identical to the distribution of raw scores; the mean of z-scores will equal zero and the variance of a z-distribution will always be one, as will the standard deviation.

To obtain a standardized score you must subtract the mean from the individual score and divide by the standard deviation. Standardized scores provide you with a score that is directly comparable within and between different groups of cases.

As an example of how to interpret z-scores, Ed is 1.31 standard deviations above the mean age for those represented in the sample. Another simple example is exam scores from two history classes with the same content but difference instructors and different test formats. To adequately compare student A’s score from class A with Student B’s score from class B you need to adjust the scores by the variation (standard deviation) of scores in each class and the distance of each student’s score from the average (mean) for the class.

2.Hypothesis Testing Basics

2.1.The Normal Distribution

Although there are numerous sampling distributions used in hypothesis testing, the normal distribution is the most common example of how data would appear if we created a frequency histogram where the x axis represents the values of scores in a distribution and the y axis represents the frequency of scores for each value. Most scores will be similar and therefore will group near the center of the distribution. Some scores will have unusual values and will be located far from the center or apex of the distribution. These unusual scores are represented below as the shaded areas of the distribution. In hypothesis testing, we must decide whether the unusual values are simply different because of random sampling error or they are in the extreme tails of the distribution because they are truly different from others. Sampling distributions have been developed that tell us exactly what the probability of this sampling error is in a random sample obtained from a population that is normally distributed.

Properties of a normal distribution

·         Forms a symmetric bell-shaped curve

·         50% of the scores lie above and 50% below the midpoint of the distribution

·         Curve is asymptotic to the x axis

·         Mean, median, and mode are located at the midpoint of the x axis

2.2.Using theoretical sampling probability distributions

Sampling distributions allow us to approximate the probability that a particular value would occur by chance alone. If you collected means from an infinite number of repeated random samples of the same sample size from the same population you would find that most means will be very similar in value, in other words, they will group around the true population mean. Most means will collect about a central value or midpoint of a sampling distribution. The frequency of means will decrease as one travels away from the center of a normal sampling distribution. In a normal probability distribution, about 95% of the means resulting from an infinite number of repeated random samples will fall between 1.96 standard errors above and below the midpoint of the distribution which represents the true population mean and only 5% will fall beyond (2.5% in each tail of the distribution).

The following are commonly used points on a distribution for deciding statistical significance:

·         90% of scores +/- 1.65 standard errors

·         95% of scores +/- 1.96 standard errors

·         99% of scores +/- 2.58 standard errors

Standard error: Mathematical adjustment to the standard deviation to account for the effect sample size has on the underlying probability distribution. It represents the standard deviation of the sampling distribution

2.3.Alpha and the role of the distribution tails

The percentage of scores beyond a particular point along the x axis of a sampling distribution represent the percent of the time during an infinite number of repeated samples one would expect to have a score at or beyond that value on the x axis. This value on the x axis is known as the critical value when used in hypothesis testing. The midpoint represents the actual population value. Most scores will fall near the actual population value but will exhibit some variation due to sampling error. If a score from a random sample falls 1.96 standard errors or farther above or below the mean of the sampling distribution, we know from the probability distribution that there is only a 5% chance of randomly selecting a set of scores that would produce a sample mean that far from the true population mean. When conducting significance testing, if we have a test statistic that is 1.96 standard errors above or below the mean of the sampling distribution, we assume we have a statistically significant difference between our sample mean and the expected mean for the population. Since we know a value that far from the population mean will only occur randomly 5% of the time, we assume the difference is the result of a true difference between the sample and the population mean, and is not the result of random sampling error. The 5% is also known as alpha and is the probability of being wrong when we conclude statistical significance.

2.3.1 The 1-tailed vs. 2-tailed statistical tests

A 2-tailed test is used when you cannot determine a priori whether a difference between population parameters will be positive or negative. A 1-tailed test is used when you can reasonably expect a difference will be positive or negative. If you retain the same critical value for a 1-tailed test that would be used if a 2-tailed test was employed, the alpha is halved (i.e., .05 alpha would become .025 alpha).

2.4. Skewness/Kurtosis

Skewness is the degree of departure from symmetry of a distribution. A positively skewed distribution has a “tail” which is pulled in the positive direction. A negatively skewed distribution has a “tail” which is pulled in the negative direction.

Kurtosis is the degree of peakedness of a distribution. A normal distribution is a mesokurtic distribution. A pure leptokurtic distribution has a higher peak than the normal distribution and has heavier tails. A pure platykurtic distribution has a lower peak than a normal distribution and lighter tails.

Most departures from normality display combinations of both skewness and kurtosis different from a normal distribution.

2.4.1.Calculating and Assessment of the Skewness and Kurtosis

There are many methods for calculating skewness and kurtosis indices. Not all computer programs calculate Skewness and Kurtosis the same way. If you use a computer program to obtain skewness and kurtosis indices be sure you know how it calculates them!

There are measures of skewness such as Pearson’s second coefficient of skewness, which is simply three times the mean minus the median divided by the standard deviation. There are also skewness indices which look at the quartiles, and many others. The most important group of measures of skewness and kurtosis use the third and fourth moments about the mean.

The moments about the mean are simply the sum of [each observed value minus the mean] raised to some power and divided by the sample size. In algebraic form, the rth moment about the mean is:

The second moment about the mean is simply the variance.

The Moment Coefficient of Skewness, denoted by statisticians as g3, is defined in dimensionless form as:

The expected value of this statistic will be zero for symmetrical distributions.

And similarly, the Moment Coefficient of Kurtosis, denoted by statisticians as g4, is defined in dimensionless form as:

This expected value of this statistic will be zero for Normal distributions.

For skewness, if the absolute value is equal or exceeds the critical value for your level of confidence, reject the assumption of normality.

For kurtosis, if the kurtosis value is greater than or equal to the high critical value, or is less than or equal to the low critical value, reject the assumption of normality.

2.5.Hypothesis Testing

The chain of reasoning and systematic steps used in hypothesis testing that are outlined in this section are the backbone of every statistical test regardless of whether one writes out each step in a classroom setting or uses statistical software to conduct statistical tests on variables stored in a database.

Chain of reasoning for inferential statistics

1.      Sample(s) must be randomly selected

2.      Sample estimate is compared to underlying distribution of the same size sampling distribution

3.      Determine the probability that a sample estimate reflects the population parameter

The four possible outcomes in hypothesis testing

Note. Alpha = probability of making a Type I error

 

Regardless of whether statistical tests are conducted by hand or through statistical software, there is an implicit understanding that systematic steps are being followed to determine statistical significance. These general steps are described on the following page and include 1) assumptions, 2) stated hypothesis, 3) rejection criteria, 4) computation of statistics, and 5) decision regarding the null hypothesis. The underlying logic is based on rejecting a statement of no difference or no association, called the null hypothesis. The null hypothesis is only rejected when we have evidence beyond a reasonable doubt that a true difference or association exists in the population(s) from which we drew our random sample(s).

Reasonable doubt is based on probability sampling distributions and can vary at the researcher’s discretion. Alpha .05 is a common benchmark for reasonable doubt. At alpha .05 we know from the sampling distribution that a test statistic will only occur by random chance five times out of 100 (5% probability). Since a test statistic that results in an alpha of .05 could only occur by random chance 5% of the time, we assume that the test statistic resulted because there are true differences between the population parameters, not because we drew an extremely biased random sample.

When learning statistics we generally conduct statistical tests by hand. In these situations, we establish before the test is conducted what test statistic is needed (called the critical value) to claim statistical significance. So, if we know for a given sampling distribution that a test statistic of plus or minus 1.96 would only occur 5% of the time randomly, any test statistic that is 1.96 or greater in absolute value would be statistically significant. In an analysis where a test statistic was exactly 1.96, you would have a 5% chance of being wrong if you claimed statistical significance. If the test statistic was 3.00, statistical significance could also be claimed but the probability of being wrong would be much less (about .002 if using a 2-tailed test or two-tenths of one percent; 0.2%). Both .05 and .002 are known as alpha; the probability of a Type I error.

When conducting statistical tests with computer software, the exact probability of a Type I error is calculated. It is presented in several formats but is most commonly reported as “p <“ or “Sig.” or “Signif.” or “Significance.” Using “p <” as an example, if a priori you established a threshold for statistical significance at alpha .05, any test statistic with significance at or less than .05 would be considered statistically significant and you would be required to reject the null hypothesis of no difference. The following table links p values with a benchmark alpha of .05:

2.6.Steps to Hypothesis Testing

Hypothesis testing is used to establish whether the differences exhibited by random samples can be inferred to the populations from which the samples originated.

Table 1. Hypothesis Testing order

2.6.1.General Assumptions

·         Population is normally distributed

·         Random sampling

·         Mutually exclusive comparison samples

·         Data characteristics match statistical technique

For interval / ratio data use t-tests, Pearson correlation, ANOVA, regression

For nominal / ordinal data use Difference of proportions, chi square and related measures of association

2.6.2.State the Hypothesis

Null Hypothesis (Ho): There is no difference between ___ and ___.

Alternative Hypothesis (Ha): There is a difference between __ and __.

Note: The alternative hypothesis will indicate whether a 1-tailed or a 2-tailed test is utilized to reject the null hypothesis.

Ha for 1-tail tested: The __ of __ is greater (or less) than the __ of __.

2.6.3.Set the Rejection Criteria

This determines how different the parameters and/or statistics must be before the null hypothesis can be rejected. This “region of rejection” is based on alpha ( ) — the error associated with the confidence level. The point of rejection is known as the critical value.

2.6.4.Compute the Test Statistic

The collected data are converted into standardized scores for comparison with the critical value.

2.6.5.Decide Results of Null Hypothesis

If the test statistic equals or exceeds the region of rejection bracketed by the critical value(s), the null hypothesis is rejected. In other words, the chance that the difference exhibited between the sample statistics is due to sampling error is remote – there is an actual difference in the population.

3.Calculating some statistical criterias

3.1.Interval Estimation For Means (Margin of Error)

Interval estimation involves using sample data to determine a range (interval) that, at an established level of confidence, will contain the mean of the population.

3.1.1.Steps

1.      Determine confidence level (df=n-1; alpha .05, 2-tailed)

2.      Use either z distribution (if n>120) or t distribution (for all sizes of n).

3.      Use the appropriate table to find the critical value for a 2-tailed test

4.      Multiple hypotheses can be compared with the estimated interval for the population to determine their significance. In other words, differing values of population means can be compared with the interval estimation to determine if the hypothesized population means fall within the region of rejection.

3.1.2.Estimation Formula

where = sample mean; CV = critical value (consult distribution table for df=n-1 and chosen alpha – commonly .05)

 (when using the t distribution)

 (when using the z distribution; assumes large sample size)

3.2.Comparing a Population Mean to a Sample Mean (T-test)

3.2.1.Assumptions

Interval/ratio level data

Random sampling

Normal distribution in population

3.2.2.State the Hypothesis

Null Hypothesis (Ho): There is no difference between the mean of the population and the mean of the sample.

Alternative Hypothesis (Ha): There is a difference between the mean of the population and the mean of the sample.

Ha for 1-tail test: The mean of the sample is greater (or less) than the mean of the population.

3.2.3.Set the Rejection Criteria

Determine the degrees of freedom = n-1

Determine level of confidence — alpha (1 or 2-tailed)

Use the t distribution table to determine the critical value

3.2.4.Compute the Test Statistic

Standard error of the sample mean

Test statistic

3.2.5.Decide Results of Null Hypothesis

There is/is not a significant difference between the mean of one population and the mean of another population from which the sample was obtained.

3.3.Comparing Two Independent Sample Means (T-test) With Homogeneity of Variance  

3.3.1.State the Hypothesis

Null Hypothesis (Ho): There is no difference between the mean of one population and the mean of another population.

Alternative Hypothesis (Ha): There is a difference between the mean of one population and the mean of another population.

Ha for 1-tailed test: The mean of one population is greater (or less) than the mean of the other population.

3.3.2.Set the Rejection Criteria

Determine the “degrees of freedom” = (n1+n2)-2

Determine level of confidence — alpha (1 or 2-tailed test)

Use the t-distribution table to determine the critical value

3.3.3.Compute the Test Statistic

Standard error of the difference between two sample means

Test statistic

3.3.4.Decide Results of Null Hypothesis

There is/is not a significant difference between the mean of one population and the mean of another population from which the two samples were obtained.

3.4.Two Independent Sample Means (Cochran and Cox). T-test without Homogeneity of Variance 

3.4.1.State the Hypothesis

Null Hypothesis (Ho): There is no difference between the mean of one population and the mean of another population.

Alternative Hypothesis (Ha): There is a difference between the mean of one population and the mean of another population.

Ha for 1-tailed test: The mean of one population is greater (or less) than the mean of the other population.

3.4.2.Set the Rejection Criteria

Determine the “degrees of freedom”

Determine level of confidence — alpha (1 or 2-tailed)

Use the t distribution to determine the critical value

3.4.3.Compute the Test Statistic

Standard error of the difference between two sample means

Test statistic

3.4.4.Decide Results of Null Hypothesis

There is/is not a significant difference between the mean of one population and the mean of another population from which the two samples were obtained.

3.5.Computing F-ratio

The F-ratio is used to determine whether the variances in two independent samples are equal. If the F-ratio is not statistically significant, you may assume there is homogeneity of variance and employ the standard t-test for the difference of means. If the F-ratio is statistically significant, use an alternative t-test computation such as the Cochran and Cox method.

3.5.1.Set the Rejection Criteria

Determine the “degrees of freedom” for each sample

df = n1 – 1 (numerator =for sample with larger variance)

df = n2 – 1 (denominator =for sample with smaller variance)

Determine the level of confidence — alpha

3.5.2.Compute the test statistic

where = largest variance = smallest variance

3.5.3.Decide Results of Null Hypothesis

Compare the test statistic with the f critical value (Fcv) listed in the F distribution. If the f-ratio equals or exceeds the critical value, the null hypothesis (Ho)  (there is no difference between the sample variances) is rejected. If there is a difference in the sample variances, the comparison of two independent means should involve the use of the Cochran and Cox method.

4.Statistical Analysis in Excel

4.1.Correlating Data

Problem. You’re working with a multivariable problem and want to compute the correlation coefficient between variables.

Solution. Use Excel’s built-in functions CORREL or PEARSON, or use the Correlation tool available in the Analysis ToolPak.

Excel provides two built-in functions that allow you to compute the Pearson product-moment correlation coefficients between variables. These functions are CORREL and PEARSON and, according to Excel’s help documents, they perform exactly the same calculation. (I’ve no idea why there are two functions to do the same thing.)

Figure 1 shows the data from the multiple linear regression example. For now, we show you how to examine the correlation between independent variablesthe x variablesusing Excel’s correlation functions as well as the Analysis ToolPak.

To compute the correlation coefficient between variables x1 and x2, you can use the cell formula =CORREL(E7:E22,F7:F22) or =PEARSON(E7:E22,F7:F22). They both produce the same results, which is 0.991589. Clearly, these two variables are positively correlated and in a regression analysis, for example, you would probably exclude one of these variables from the curve fit. You can compute correlation coefficients between the other variables in a similar manner.

Figure 1. Multiple linear regression data

To save some time when dealing with numerous variables, you might consider using the Correlation tool available in the Analysis ToolPak. Select Tools Data Analysis from the main menu bar to open the Data Analysis dialog box. If this items not present, select Tools Add-Ins from the main menu bar and select the Analysis ToolPak option from the list.

Select the Correlation tool and press OK to open the Correlation tool dialog box shown in Figure 2.

Figure 2. Correlation tool dialog box

The Correlation tool allows you to select multiple datasets at once; it will compute the correlation coefficients between all the datasets. For example, in Figure 2 I entered cells E6 through J22 in the Input Range. This cell range includes all of the x variables shown in Figure 1. I included the header labels in the input range, so I checked the “Labels in first row” option. Further, I specified cell M7 (on the same worksheet) as the starting cell where I’d like the output displayed. Figure 3 shows the results.

Figure 3. Correlation results

This table presents the correlation coefficient between each given variable and every other variable. It also correlates each variable with itself, which of course results in a correlation coefficient of 1 (as you can see in Figure 3).

Sometimes it’s useful to compute coefficients of determination instead of correlation coefficients alone. To compute a coefficient of determination, simply square the correlation coefficient. For example, if cell N9 contains the correlation coefficient between variables x1 and x2, then the formula =N9^2 would yield the coefficient of determination.

There are other types of correlation coefficients common in statistical analysis. For example, Phi, rank biserial, point biserial, and Spearman rank coefficients are common, in addition to the Pearson coefficient. Excel does not have built-in support for these other correlation coefficients; however, you can certainly set up a spreadsheet to calculate these coefficients.

For example, you can readily prepare a spreadsheet to compute the Spearman rank correlation coefficient between variables x1 and x2 from the previous example. The Spearman coefficient requires you to convert the raw data to ranks first. You can achieve this conversion using Excel’s built-in RANK function (see next section). Figure 4 shows a simple spreadsheet I set up to compute the Spearman rank correlation coefficient between the variables x1 and x2 from the previous example.

The first two columns in the table contain the raw data. The third and fourth columns contain the rank of each data point. I used the RANK function for these calculations. For example, cell E41 contains the cell formula =RANK(C41,$C$41:$C$56,1), which computes the rank for the first x1 value, assuming the data are sorted in ascending order (see next section).

The formula for computing the Pearson rank correlation coefficient is:

Here, d is the difference in ranks between corresponding values for both x1 and x2, and N is the number of data points. You can compute N using the COUNT function. Cell D58 contains the formula =COUNT(D41:D56) which returns a value of 16 for the number of points.

To compute d², I set up another column containing formulas like =(E41-F41)^2. The results are shown in the last column of the table shown in Figure 4.

Figure 4. Spearman rank correlation coefficient calculation

Cell G57 computes the sum of d² values using the formula =SUM(G41:G56). Cell G59 computes the N(N² – 1) term using the formula =(D58*(D58^2-1)). And finally, the correlation coefficient is computed in cell G60 using the formula =1-6*G57/G59. The resulting correlation coefficient is 0.997.

4.2.Ranking and Percentiles

Problem. You need to compute certain percentiles of a set of data and you’d also like to compute the rank of certain values in the dataset.

Solution. Use the built-in functions PERCENTILE, RANK, and PERCENTRANK. Or you can use the Rank and Percentile tool available in the Analysis ToolPak.

By way of example, let’s assume your dataset consists of the values 1, 3, 5, 7, 9, 2, 4, 6, 8, 10, and 0. Let’s also assume this dataset resides on a spreadsheet in the cell range C18:C28. (This is a rather simple dataset for example purposes; in practice your dataset can be anything and it need not be sorted.)

To compute the 25% percentile, use the formula =PERCENTILE(C18:C28,0.25), which returns a value of 2.5. To compute the 95% percentile, use =PERCENTILE(C18:C28,0.95), which returns a value of 9.5 as expected.

To compute the rank of a given value, use the formula =RANK(2,C18:C28,1). The rank of the value 2 in this dataset is 3, assuming the dataset is sorted in ascending order. If you want to compute the rank of a value as though the dataset were in descending order, then use a value of 0 for the third argument in the call to RANK.

If you’d like to compute the rank of a value in percentage terms, then use the PERCENTRANK function. For example, =PERCENTRANK(C18:C28,2,2) returns the rank of the value 2 as 20%. The second argument in this function call represents the value whose rank is sought. The third argument is the number of significant digits for the result (in this case, two significant digits).

Instead of using these built-in functions, you could use the Analysis ToolPak’s Rank and Percentile tool. Select Tools Data Analysis from the main menu bar to open the Data Analysis dialog box. Select “Rank and Percentile” from the list and press OK to open the “Rank and Percentile” dialog box shown in Figure 5.

In the Input Range field, type (or select from your spreadsheet) the cell range containing the input dataset you’d like to rank. You can also specify the output location, as shown in Figure 5.

Figure 5. Rank and Percentile tool dialog box

For the simple example dataset discussed earlier, the Rank and Percentile tool returns the results shown in Figure 6.

Figure 6. Rank and Percentile results

The ranks are computed assuming the data is sorted in descending order. The first column in the resulting table contains an index for each data point. The second column contains the raw value of the data point. The third column contains the corresponding rank. The last column contains the percentage rank.

In the event of ties (that is, where two or more values in the dataset are the same), you may want to use the correction recommended in Excel’s help file for computing ranks. The formula to use is =[COUNT(data)+1-RANK(value, data, 0)-RANK(value, data, 1)]/2. In this formula, data is a cell reference containing the input dataset and value is the value for which you want to find a rank. See the Excel help topic for the RANK function for more information. To the best of my knowledge, the Rank and Percentile tool in the Analysis ToolPak does not handle ties. So if your data contains ties and you need to correct for this, you should use the RANK function instead.

4.3.Performing Statistical Tests

Problem. You’re engaged in hypothesis testing and you’d like to learn about Excel’s support for conducting statistical tests .

Solution. Excel provides support for conducting standard z-tests, t-Tests, and F-tests. Read the following discussion to learn more.

The built-in function ZTEST computes the one-tailed z-test using the normal distribution. The syntax for this function is =ZTEST(cell ref, m, s) where cell ref is a cell reference containing an input dataset, m is the value being tested, and s is the population standard deviation. If s is not known, it can be omitted, in which case the sample’s standard deviation will be computed and used in the z-test calculation.

You can also compute two-tailed z-tests as follows: =2 * MIN(ZTEST(cell ref, m, s), 1 – ZTEST(cell ref, m, s)).

The function TTEST computes the one- or two-tailed t-test using Student’s t-distribution. Further, TTEST allows you the option of performing paired, two sample with equal variance, or two sample with unequal variance tests. The syntax for TTEST is =TTEST(cell ref 1, cell ref 2, tails, type), where cell ref 1 and cell ref 2 are cell references to the two datasets under consideration and tails is the number of tails to consider for the test (1 or 2). type is a value of 1, 2, or 3, indicating the test type: paired, two sample equal variance, or two sample unequal variance, respectively.

FTEST computes the two-tailed F-test for two datasets. The syntax for FTEST is =FTEST(cell ref 1, cell ref 2), where cell ref 1 and cell ref 2 are cell references to the two datasets under consideration.

All three of these test functions have corresponding inverse functions (namely, NORMSINV, TINV, and FINV) that are useful when computing critical values for comparison.

The Analysis ToolPak also provides several tools for conducting statistical tests. These tools include:

·         F-Test Two-Sample for Variances

·         t-Test Paired Two-Sample for Means

·         t-Test Two-Sample Assuming Equal Variances

·         t-Test Two-Sample Assuming Unequal Variances

·         z-Test Two-Sample for Means

To access these tools, select Tools Data Analysis from the main menu bar to open the Data Analysis dialog box. You’ll find each of the statistical test tools listed in this dialog box. They all work pretty much the same way: select a tool to open the tool’s dialog box, select input cell ranges, specify your hypothesis, and select your output options.

I’ll show an example using the t-Test Two-Sample Assuming Unequal Variances tool. Figure 7 shows a portion of a spreadsheet containing measured weights of North American bullfrogs sampled from two southern states.

Each state’s sample contains 60 data points. An example hypothesis to test here (a null hypothesis) is that there’s no difference between the mean weights of bullfrogs from Louisiana and Alabama.

To conduct this test, select t-Test: Two-Sample Assuming Unequal Variances from the Data Analysis dialog box discussed earlier to open the t-Test dialog box shown in Figure 8.

Figure 7. Bullfrog weight data

Figure 8. t-Test dialog box

In the Variable 1 Range and Variable 2 Range fields, type (or select from the spreadsheet) the cell ranges containing the dataset corresponding to Louisiana and Alabama, respectively. In this example, I included the column labels in these input ranges, so I checked the Labels checkbox.

Note. Alpha represents the level of significance related to the probability of encountering a type I error. 0.05 represents a 5% significant level. 0.01 represents a 1% significant level, and so on. See any standard text on statistics for more information on this value and hypothesis testing.

The hypothesis is that there is no difference between the means; therefore, enter a value of 0 in the Hypothesized Mean Difference field. In this example, I left the Alpha field (confidence field) at the default 5%; but you can change that as well. Finally, choose your output options. In this case, I chose to have the output on the same spreadsheet, starting at cell D93. Press OK; the results are shown in Figure 9.

Figure 9. t-Test results

These results include computed means and variances for the two input datasets, along with other information such as the number of observations, the hypothesized mean difference, and the degrees of freedom. The t-statistic is also reported, along with both the one-tailed and two-tailed probabilities and critical values. In this case, the t-statistic is 2.1, which is greater than the two-tailed t-critical value, thus indicating there is a statistically significant difference in means between the two datasets. You can also see that the P-value is less than the significance level (Alpha value of 5% in this case), which also indicates rejection of the null hypothesis.

The other statistical test tools available in the Analysis ToolPak generate results that look very much like the ones shown in Figure 9.

4.5.Conducting ANOVA

Problem. You’d like to conduct an analysis of variance (ANOVA).

Solution. The Analysis ToolPak offers three flavors of ANOVA: single factor, two-factor with replication, and two-factor without replication. Select Tools Data Analysis from the main menu bar to access these tools.

By way of example, consider the hypothetical drug effectiveness data shown in Figure 10.

The data values represent some measure of merit, or effectiveness, for drugs A, B, and C, along with a control group. The Anova: Single Factor tool allows you to perform an analysis of variance for two or more groups, so we’ll use that tool to test the hypothesis that there’s no difference between the means of these groups.

After selecting the Anova: Single Factor tool from the Data Analysis dialog box, you should see the Anova: Single Factor dialog box like the one shown in Figure 11.

In the Input Range field, type or select the cell range containing all the data under consideration. Your dataset must be in contiguous columns for this tool. If you include the column labels as I did in this example, then be sure to check the “Labels in first row” option. You can set the Alpha parameter to whatever you’d like. In this case I left it at 5%.

Finally, choose your output options and press OK. Here, I chose to generate the output in the same worksheet, starting in cell E178. The results of this analysis are shown in Figure 12.

These results show descriptive statistics for each group, along with standard ANOVA information. ANOVA results are displayed for between groups and within groups. SS represents the sum of squared deviations from the mean. df represents degrees of freedom. MS is the mean square value. F is the F-Ratio. And finally, F crit is the critical F-value based on the F-distribution. In this example, the P-value is less than the supplied Alpha value, and the obtained F-value is greater than the critical F-value. These results imply that we should reject the null hypothesis that there’s no significant difference between the means of these groups and conclude that there’s some statistically significant difference.

Figure 10. Drug effectiveness

Figure 11. Anova: Single Factor dialog box

Figure 12. ANOVA results

5. Hypothesis Testing Example

5.1.Problem

The scientists conducting the medical experiment checkup for 15 practically healthy men and women by age 17-18 years. For the each of them defined the concentration of haemoglobin and amount of red corpuscles (erythrocytes). Results was stored in table (Table 1). It is necessary to check up the next hypothesis and give scientific grounded answer:

Are there blood parameters depend to peoples gender or not?

Table 1. Results of the blood paramenters checkup

5.2.Solution

To answer above question the following steeps must be passed:

I. Formulate the task by the mathematical statistics point of view (define which one statistical hypothesis it is need checking). This is theoretical description of your task.

1. Such us we have two parameters for processing (Hb and Er) the hypothesis testing must be performed twice for each of it separately.

2. To answer general question we must answered both of the following questions:

Q1: Are there statistically meaningful differences exist in the haemoglobin concentration of the blood for the people of different sex?

Q2: Are there statistically meaningful differences exist in the amount of red corpuscles (erythrocytes) of the blood for the people of different sex?

Below will be shown order to answer Q1. The order to answer Q2 it is same.

3. Using table 1 (sect. 2.6) we can see that Q1 question very similar to the practical viewpoint that shown in the first row: “Comparing the control and experimental samples“. Reasons for this conclusion – we have only 2 samples (Hb for male and Hb for female). First sample can be seen as a control sample and second – as an experimental.

4. Statistical viewpoint for Q1 question we can receive from second column of the table 1 (sect. 2.6) – “Comparing Two Independent Sample Means“. Now the state of the hypothesis formulate is possible in according to hint that was shown in the sect. 2.6.2:

·         Null Hypothesis (Ho): There is no statistically meaningful differences between in the control sample mean (haemoglobin for male) and experimental sample mean (haemoglobin for female). Both samples are extracted from the one general population. Practical sense – concentration of haemoglobin did not depend to gender. Observed differences in sample values are results of the probability variances.

·         Alternative Hypothesis (Ha): There is present statistically meaningful differences between in the control sample mean (haemoglobin for male) and experimental sample mean (haemoglobin for female). Both samples represented two different general populations. Practical sense – concentration of haemoglobin depend to gender.

II. Check the additional conditions set (in according column 3 of the table 1).

1. Identify the type of the variables distribution. The Coefficient of skewness and coefficient of kurtosis (table 2 in the sect. 5.3.) need compute for each sample in your task for this. Then, compare calculated results with special tables of the critical values (table 3 in the sect 5.3.) in according to the sample size and get answer about the distribution type (see sect. 2.4.1.):

For the Coefficient of skewness:

·         If your calculated values are smallest than critical the distribution type is normal.

·         If your calculated values are greatest than critical the distribution type is abnormal.

For the Coefficient of kurtosis:

·         If your calculated values are between low and high critical then the distribution type is normal.

·         If your calculated values are outside the critical interval then the distribution type is abnormal.

For current example all calculated values for both the coefficient of skewness and the coefficient of kurtosis (Figure 13) are smallest then critical that shown in the table (table 3 in the sect. 5.3.) for the sample size n=15. It allow for as make conclusion that for both compared samples the distribution type is normal.

2. Test homogeneity of the Variance in selected samples. The Variance parameter (table 2 in sect. 5.3.) must be computed for each sample in your task. Then compute the F-ratio – divide the great variance by the smaller as it was shown in the sect. 3.5. Then, compare calculated results with special tables that contain list of the critical values of the F-ratio and get answer about the homogeneity of the Variance.

Figure 13. The Source data and results of calculation of the some descriptive statistic parameters on to the Excel worksheet.

We will use alternate method to apply power of Excel to check homogeneity of the Variance in selected samples. In the Excel Data Analysis toolpack available the F-Test Two-Sample for Variances module (see sect. 4.3). To use it follow this steps:

·         Select Tools Data Analysis from the main menu bar to open the Data Analysis dialog box. Choose the F-Test Two-Sample for Variances module from list to open the properly dialog box.

·         In the Variable 1 Range and Variable 2 Range fields, type (or select from the spreadsheet) the cell ranges containing the dataset corresponding to Hb for Wonan and Hb for Men, respectively (in this example – B4:B18 and D4:D18). This order is important because sample data for the Hb for Wonan have a largest variance value. The Alpha parameter left at 5% (0.05).

·         Finally, choose your output options and press OK. Here, I chose to generate the output in the same worksheet, starting in cell A23. The results of this analysis are shown in Figure 14 at the left side.

To make conclusion about the homogeneity of the variances of the processed samples we must compare calculated F-ratio value to the F critical value:

·         If calculated F-ratio smallest to the F critical value than variances are statistically equals (homogeneity).

·         If calculated F-ratio greatest of the F critical value than variances are statistically not equals (not homogeneity).

In this example calculated F-ratio smallest to the F critical value than variances are statistically equals (figure 14).

Figure 14. The Source data and results of calculation of the some descriptive statistic parameters on to the Excel worksheet.

III. Choose method of the statistical hypothesis check.

1. We need again back to the table 1 (sect. 2.6). From the results above we know that sample data distribution type is normal and variances are statistically equals. In according to this we come to the one possible method of the main statistical hypothesis testing – usage the T-test with homogeneity of Variance. Corresponded equation can be received from any statistical textbook. But in this example we again can use the MS Excel Data Analysis toolpack tool called t-Test Two-Sample Assuming Equal Variances (see sect. 4.3):

·         Select Tools Data Analysis from the main menu bar to open the Data Analysis dialog box. Select in list module with name t-Test Two-Sample Assuming Equal Variances to perform hypothesis testing. The t-Test dialog box will be open.

·         In the Variable 1 Range and Variable 2 Range fields, type (or select from the spreadsheet) the cell ranges containing the dataset corresponding to the men and women haemoglobin concentration, respectively (in this example – D4:D18 and B4:B18).

·         This also important because the haemoglobin concentration sample data have greatest mean (reason see in the sec. 3.3.3). Left the Alpha field (confidence level) at the default 5%. Finally, choose your output options. You can use any empty cell bellow your table data on the same spreadsheet (cell E23 in this example). Click OK to compute.

·         The results (at right on the Figure 14) include computed means and variances for the two input datasets, along with other information such as the number of observations, the hypothesized mean difference, and the degrees of freedom. The t-statistic is also reported, along with both the one-tailed and two-tailed probabilities and critical values. Use this data, get answer about rejection or acceptation hypothesis.

In this example calculated t Test value is greatest than t critical 2-tailed (Figure 14). Significant level (P) very small that indicate a low probability of the Type 1 error.

Conclusion.

The statistically significant difference between mean in the compared samples are available. As result – we accept ALTERNATIVE Hypothesis (Ha) and reject NULL (Ho).

5.3.Additional and reference information

Table 2. Statistical parameters and Excel functions

Table 3. The Scewness and Kurtosis Coefficients critical values ( for two-tailed test)