Showing posts with label t test. Show all posts
Showing posts with label t test. Show all posts

Tuesday, August 3, 2010

t Tests - How To Use the t Test in Excel To Find Out If Your New Marketing Worked

The t Test

Simplified and Done

in Excel


If you had recently launched a new marketing campaign, you would want to know as soon as possible whether the campaign was working. If you are able to take a large sample of before and after measurements (for example, in all of the sales territories), Excel has the perfect tool for you  a data analysis tool called the two-sample paired t-test for means. It is very simple to use and the output is straight-forward and easy to interpret.

t Test - General Description

This test will tell you whether the difference between the before and after numbers is genuine or whether this difference could merely have been the result of chance. Overall a t-test compares two means and determines within a specified degree of certainty whether the two means really are different, or whether the difference might have occurred by chance.

Two-Sample, Paired t Test

The two-sample paired t-test for means evaluates whether the average difference between the before and after measurements is greater than zero or not. In other words, this test evaluates within a specified degree of certainty whether the average measured difference between before and after is real or could have occurred merely by chance.

Before we start discussing this specific test in detail, The t-test needs to be generally explained. The basic question to be answered is:


The t Test - What Is It?


The t test is a statistics test generally used to test whether means of populations are different. In the t test, a t value is calculated based upon the difference in the means and variances of the two populations. The greater the t value, the more certain it is that the means are different.

The t value can be generally described as follows:

t value = (Difference between the group means) / (Variability of the groups)

There are many variations of the t test. Each has its own specific formula for calculating a t value for the sampled data sets. All of the t value formulas can be described by the above formula.


The Higher the t Value - The More Likely the Groups Are Different

The higher the t value is, the more likely it is that the two means are different. If the two groups being compared have a high degree of variance (t value has a high denominator), it is much harder to tell them apart. On the other hand, if the two groups being compared have a low degree of variance (the t value has a low denominator), it is much easier to tell the two groups apart.

The Lower the Combined Variance, the Higher the t Value

The illustrations below should clarify how the degree of variance in the two groups determines how easy or difficult it is to state that the means of the two groups are really different. The t test quantifies this relationship and provides a way to determine whether the measured difference between two means can be considered real or not based upon the amount of variance in both groups. Here are illustrations that should clarify this relationship.
t tests in excel, t test, normal distribution, normally distributed, statistical tests in excel

We can see that pair of data sets on the right are much easier to differentiate because they have much less overlap than the pair of data sets on the right. The overlap represents the overall variability between the two data sets in each pair. The higher the total variablility within the pair of data sets, the higher will be the denominator in the t value formula. The higher the denominator, the lower the t value for the pair of data sets. The lower the t value, the less likely it is that the two data sets are separate data sets with different means.


T-Test Paired Two Sample for Means

A paired t test or paired difference t test is use to determine whether the average of the "before" and "after" measurements taken of a single set of objects is the same. The Null Hypothesis being tested states that there is no difference between the average "before" and "after" measurements. Specifically, the Null Hypothesis states that the mean of all "after" measurements minus the mean of all "before" measurements taken of the same objects equals 0.

We are going to use the paired t test to determine within 95% certainty whether the average sales from a group of sales territories increased after a new marketing program was implemented. We will simply measure the before and after sales from each territory and apply this t test using Excel to get our result.



A Little Bit More About This t Test


The t Test in general is a special case of one-way (sometimes called “single factor”) ANOVA. This paired two-sample student’s t test is applied when there is a natural pairing of samples. It is most often used to determine whether “before” and “after” means of a sample of the same objects have changed during an experiment. One really great thing about this t test is that the paired two-sample t test does not require that the variances of both populations to be the same.

To sum up the paired two-sample student’s t test, a single t value is calculated from data from both samples. Here is the formula to calculate the t value for a paired two-sample student’s t test if you are testing to determine whether the difference between two means is greater than zero:

t value =      Average Difference Between Each Pair /
               [ Stan. Dev. Of Average Differences / SQRT(n) ]

You can see that this follows the general formula for calculating the t value in a t test, which is:

t value = (Difference between the group means) / (Variability of the groups)

The t value is a specific point on the x-axis in the t distribution (student’s t distribution). If this t value falls outside the region of required certainty, it can be stated that the two means are probably different. If this t value falls within the region of required certainty, it cannot be stated that the two means are probably different.

The required region of certainty depends upon the degree of certainty required in the test. If 95% certainty is required, then the required region of certainty consists of 95% of the area under the student’s t distribution. The outer 5% is the region of uncertainty. This is also referred to as α (alpha) or the degree of significance. If the t value is large enough to be located all the way out on the x-axis in the 5% region of uncertainty, it can be stated within 95% certainty that the two means are different.

A t test can be a one-tailed test or a two-tailed test. A one-tailed test determines whether the means are different in one specific direction. For example, a one-tailed test could be used to determine only if the mean of the “after” measurements is greater than the mean of the “before” measurements. A two-tailed test determines whether the two means are merely different.




Two-Tailed t Test Is More Stringent

The two-tailed test is more stringent because the area in the outer tails outside of the region of required degree of certainty is split into two tails. For example, if the required degree of certainty is 95% on a two-tailed test, the calculated t value must be all the way out in the outer 2.5% of either tail for the t test to conclude within 95% certainty that the means are different.

One-Tailed t Test Is Less Stringent

A one-tailed test is less stringent. If the required degree of certainty is 95% on a one-tailed test, the calculated t value only has to be within the outer 5% of whatever tail is being tested to be able to state the two means are probably different.



Doing The Paired Two-Sample t Test in Excel


We are testing to determine whether a new marketing campaign has increased sales in a group of six sales territories. In this case the sample size (n) equals 6. For this type of t test, the degrees of freedom = n – 1 = 5.

The data need to be arranged in Excel as follows:

t tests in excel, t test, normal distribution, normally distributed, statistical tests in excel
Click on Image To See Enlarged View

Now, access this Excel t Test as follows (this is Excel 2003):
Tools / Data Analysis / t-Test: Paired Two Sample for Means

This following dialogue box will appear:
t tests in excel, t test, normal distribution, normally distributed, statistical tests in excel
Click on Image To See Enlarged View

Input the data as followings:

Variable 1 Range: Select everything that is highlighted light blue, including the label “Sales After New Ads.” If you are trying to determine whether the “after” measurements have gone up, the “after” data is Input Variable 1. If you are trying to determine whether the “after” measurements have gone down, the “after” data is Input Variable 2.

Variable 2 Range: Select everything that is highlighted in yellow, including the label “Sales Before New Ads.”

Hypothesized Mean Difference: 0

Labels: Check the box because you included the labels for Variables 1 and 2.

Alpha: This depends on your desired degree of certainty. 0.05, if you desired 95% certainty. 0.20 if you desire 80% certainty.

Output Range: Select the cell that you want the upper left corner of the output to appear in.

Hit “OK” to run the analysis and the following Excel output appears:
t tests in excel, t test, normal distribution, normally distributed, statistical tests in excel
Click on Image To See Enlarged View

This output can be interpreted as follows:

The t value is 2.511.

One-tailed Test



This t value is greater than the critical t value for a one-tailed test (2.015). We can therefore state with 95% certainty that the mean sales has increased as a result of the new marketing campaign.

The above conclusion can also be reached because the p Value for the one-tailed test (highlighted in light blue on the Excel output) is 0.027. This is less than alpha (0.05). The p Value being less than alpha is an equivalent result to the t value being greater than the t critical value.


Two-Tailed Test



A different result is arrived at for the two-tailed test. The two-tailed test is more stringent because the alpha region of uncertainty (5% of the area under the student’s t distribution curve) is now divided between both outer tails. The t value needs to be larger for the two-tailed test to wind up in the outer 2.5% area of either outer tail.

In this case, the t value was not large enough to be positioned in the outer 2.5% of either outer tail. The t value (2.511) is smaller than the critical t value for the two-tailed test (2.571). This indicates that it cannot be stated with 95% certainty that there has been a change in the mean from before to after.

The p value calculated for the two-tailed test (0.054) is larger than alpha (0.05). This is an equivalent result to the above.


Hand Calculation of the t Value and p Value



Let’s calculate the t value and p values for the one and two-tailed tests by hand to make sure that Excel has done a correct job. The t value is stated as the t statistic.

Here is the original test data: 
t tests in excel, t test, normal distribution, normally distributed, statistical tests in excel
Click on Image To See Enlarged View


Here is the hand calculation of the t value and p values for the one and two-tailed tests for this Paired Two-Sample t Test. The hand calculation agrees with the Excel outputs. There are very slight differences due to rounding differences:
t tests in excel, t test, normal distribution, normally distributed, statistical tests in excel
Click on Image To See Enlarged View

The Paired Two-Sample t Test is a very simple test to run and can be applied to nearly any aspect of your marketing program to see if a single change affected a large number of elements whose before and after measurements can be taken. One note: the before and after measurements must be continuous and using the same scale.


If you would like to create a link to this blog article, here is the link to copy for your convenience:

The t Tests - How and When Should the Marketer Use Them In Excel

t tests in excel, t test, normal distribution, normally distributed, statistical tests in excel
Please post any comments you have on this article. Your opinion is highly valued!


If You Like This, Then Share It...
Dig this Stumble upon Delicious Technorati Reddit Buzz it Twitthis

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Posted by at 5 comments:
Labels: , , , , , , , , , , ,

Friday, July 30, 2010

The 22 Biggest Statistical Mistakes You Don't Want To Make

The 22 Most Common

Statistical Errors

1) Not using nonparametric tests when analyzing small samples of unknown distribution

– The t Distribution should only be used in small sample analysis if the population from which the samples were drawn was normally distributed. Nonparametric tests are valid when the population distribution is not known, or is known not to be normally distributed. Using the t distribution in either of these cases for small sample analysis is invalid. I will write a couple of articles in this blog in the future detailing how and when to perform a couple of commonly-used nonparametric tests with Excel.

2) Using a one-tailed test instead of a two-tailed test when accuracy is needed

– If accuracy it what you are seeking, it might be better to use the two-tailed when performing, for example, a hypothesis test. The two-tailed test is more stringent than the one-tailed test because the outer regions (I call them the regions of uncertainty) are half the size in a two-tailed test than in a one-tailed test. The two-tailed test tells you merely that the means are different. The one-tailed test tells you that the means are different in one specific direction.

3) Using covariance analysis instead of correlation analysis

– The output of covariance analysis is dependent upon the scale used to measure the data. Different scales of measurement can produce completely different results on the same data if covariance analysis is used. Correlation analysis is completely independent of the scale used to measure the data. Different scales of measurement will produce the same results on a data set using correlation analysis, unlike covariance analysis.

4) Not taking steps to ensure that your sample is normally distributed when analyzing with the normal distribution

– One way to ensure that you have a normally distributed sample for analysis is to take a number of large samples (each sample consists of at least 30 objects) and then tke the mean from each sample as one sample point. You will then have one final, working sample that consists of the means of all of your previous samples. A statistical theory called the Central Limit Theory states that the means of a group of large samples (each sample consists of at least 30 objects) will be normally distributed, no matter how the underlying population is distributed. You can then perform statistical analysis on that final sample using the normal distribution.

5) Thinking it is impossible to get a statistically significant sample if your target market is large

– The sample size you need from a large population is probably quite a bit smaller than you think. Nationwide surveys are normally within a percentage point or two from real answer after only several thousand interviews have been conducted. That of course depends hugely on obtaining a representative sample to interview.

6) Always requiring 95% certainty

– This could really slow you down. For example, if I’m A/B split-testing keywords or ads in an AdWords campaign, I will typically pick a winner when my split-tester tells me that it is 80% sure that one result is better than the other. Achieving 95% certainty would often take too long.

7) Not using Excel

– This point may sound a little self-serving, but knowing how to do this stuff in Excel is a real time-saver, particularly if you are in marketing, and especially if you’re an Internet marketer. You’ll never need to pick up another thick confusing statistics text book or figure how to work those confusing statistics tables ever again. I’ve actually thrown out all of my statistics text books (well, not quite, I sold them on eBay).

8) Attempting to apply the wrong type of hypothesis test

– There are 4 ways that the data must be classified before the correct hypothesis test can be selected. Another article in this blog discusses this. Also, Chapters 8 and 9 of the Excel Statistical Master provide clear, detailed instructions on how to analyze your data prior to hypothesis test selection. You probably wouldn’t get far into a hypothesis test if you have incorrectly classified the data and selected the wrong hypothesis test.

9) Using the wrong t test

– The t-test to be applied depends upon factors such as whether or samples have the same size and variance. It is important to pick the right t-test before starting.

10) Not controlling or taking into account other variables besides the one(s) being testing when using the t test, ANOVA, or hypothesis tests.

Other variables that not part of the test need to be held as constant as possible during the above tests or your answer might be invalid without you knowing.

11) Not removing outliers prior to statistical analysis

– A couple of outliers can skew results badly. Once again, eyeball the data and determine what belongs and what doesn’t.

12) Analyzing non-normal data with the normal distribution

– Data should always be eyeballed and analyzed for normality before using the normal distribution. If the data is not normally distributed, you must use data fitting techniques to determine which statistical distribution most closely fits the data.

13) Drawing a conclusion before a statistically significant result has been reached

– This is often caused by choosing a statistical test requiring a lot of samples but depending on a low sample rate. A common occurrence of this would be performing multivariate testing on a web site that does not have sufficient traffic. Such a test is likely to be concluded prematurely. A better solution might be to perform a number of successive A/B split-tests in place of multivariate analysis. You get a lot more testing done a lot faster, and correctly.

14) Drawing a conclusion without applying the proper statistical analysis

- This occurs quite often when people simply eyeball the results instead of performing a hypothesis test to determine if the observed change has at least an 80% chance (or whatever level of certainty you desire) of not being pure chance.

15) Not drawing a representative sample from a population

– This is usually solved by taking a larger sample and using a random sampling technique such as nth-ing (sampling every nth object in the population).

16) Only evaluating r square in a regression equation

– In the output of regression performed in Excel, there are actually four very important components of the output that should be looked at. There is an article in this blog that covers this topic in a lot more detail than could be done in this bullet point.

17) Not examining the residuals in regression

– You should always at least eyeball the residuals. If the residuals show a pattern, your regression equation is not explaining all of the behavior of the data.

18) Applying input variables to a regression equation that are outside of the value of the original input variables that were used to create the regression equation

– Here is an example to illustrate why this might produce totally invalid results. Suppose that you created a regression equation that predicted a child’s weight based upon the child’s age, and then you provided an adult age as an input. This regression equation would predict a completely incorrect weight for the adult, because adult data was not used to construct the original regression equation.

19) Adding a large number of new input variables into a regression analysis all at once

– One way to ensure that you have a normally distributed sample for analysis is to take a large number of samples (at 30) with each sample consisting of several random and simultaneously-chosen data points and then take the mean from each sample. Make that mean the sample. Your samples will now be Normally distributed. You can take as few as 2 data points per sample, but the more data points per sample, the fewer data points it will take for your samples (each sample is the mean of the data points collected for one sample) to appear to be Normally distributed. If you are taking only 3 data points per sample, you may have to collect over samples (that would be a total of 300 data points) for your samples to appear to be Normally distributed.


A statistical theory called the Central Limit Theory states that the means of samples (at least 30 samples and each sample having at least 2 data points that are averaged to get a mean, which will be the value of the sample) will be Normally distributed, no matter how the underlying population is distributed. You can then perform statistical analysis on that group using the normal distribution-based techniques.

20) Not doing correlation analysis on all variables prior to performing regression
– You’ll save yourself a lot of time if you can remove any input variables that have a low correlation with the dependent (output – Y) variable or that have a high correlation with another input variable (this error of highly correlated input variables is called multicollinearity). In the 2nd case, you would want to remove the input variable from the highly correlated pair of input variables that has the lowest correlation with the output variable.

21) Not graphing and eyeballing the data prior to performing regression analysis

– Always graph the data before you do regression analysis. You’ll know immediately whether you’re dealing with linear regression, non-linear regression, or completely unrelated data that can’t be regressed.

22) Assuming that correlation equals causation

– This is, of course, not true. However, if you find a correlation, you should look hard for links between the two objects that are correlated. The correlation may be pure chance, but then again, it may not be. A correlation is a reason to look for underlying causes behind the behavior. Correlation is often a symptom of a larger issue, but it is not a guarantee of causality.


If you would like to create a link to this blog article, here is the link to copy for your convenience:

Statistical Mistakes You Don't Want To Make


Please post any comments you have on this article. Your opinion is highly valued!

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

Posted by at 4 comments:
Labels: , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)