tTest in Excel
Preparing Data
• Assume we are considering whether a new marketing campaign has increased sales in a group of six sales territories. In this example, the sample size (n) equals 6, then degrees of freedom = n – 1 = 5.
Copyright By PowCoder代写 加微信 powcoder
• The data in Excel:
Operations in Excel
• In Excel, t-Test is described as follows (this is Excel 2003):
If [Data > Data Analysis] does not exists then do the following:
v[File > Options > Add-Ins > Analysis ToolPak > Go > (click both “Analysis ToolPak” and “Solver Add-in” then OK)]
[Data > Data Analysis > t-Test: Paired Two Sample for Means] This following dialogue box will appear:
Input the data as followings:
• Variable 1 Range: Select everything that is highlighted light blue, including the label “Sales After .” 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 .”
• Hypothesized Mean Difference: 0
• Labels: Check the box because you included the labels for Variables 1 and
• 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 output to appear in.
• Hit “OK” to run the analysis and the following Excel output will appears:
This output can be interpreted as follows:
• The t value is 2.359.
• One-tailed Test
– Thistvalueisgreaterthanthecriticaltvalueforaone-tailedtest (2.132). We can therefore state with 95% certainty that the mean sales has increased as a result of the new marketing campaign.
– TheaboveconclusioncanalsobereachedbecausethepValueforthe one-tailed test (highlighted in light blue on the Excel output) is 0.039. 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.
– ThepValuebeinglessthanalphaalsomeansthenullhypothesiscan be rejected.
• 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.359) is smaller than the critical t value for the two-tailed test (2.776). 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.078) is larger than alpha (0.05). This is an equivalent result to the above.
References
1. http://blog.excelmasterseries.com/2010/08/how-to-use-t-test-in-excel- to-find-out.html
2. http://www.ats.ucla.edu/stat/mult_pkg/faq/general/tail_tests.htm
3. http://en.wikipedia.org/wiki/One-_and_two-tailed_tests
4. http://www.addictivetips.com/windows-tips/excel-2010-data-analysis/
5. http://office.microsoft.com/en-au/excel-help/t-test-function- HP010335701.aspx
6. http://labstats.net/articles/pvalue.html
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com