Results 1 to 9 of 9
Thread: Ttest in Excel (Excel xp)

20030305, 16:02 #1
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Ttest in Excel (Excel xp)
I need to highlight cells in a spreadsheet that are significantly different. I have 2 columns, Col A is data from year 2001. Col B is data from year 2002. If the sample size for both is 500 and the score for Question 1 on a 5 pt. scale for 2001 was 4.5 and in 2002 it was 4.1, i need to test if there is significance (95%) between the two years.
Is there a function for a Z or T test and how do i use it for my example? Thank you very much.

20030305, 16:13 #2
 Join Date
 Mar 2001
 Location
 Dallas, Texas, USA
 Posts
 1,680
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Ttest in Excel (Excel xp)
If using a third column(perhaps, C) is an option, it would be simple to use conditional formatting on the data cells. You could then, hide column C to maintain the proper appearance. Or you could use any outofsight column.
Conditional Formatting could be used to apply a light yellow background to a cell based on the result in that third column. If you think this will work for you, I can attach an example of how to use the Conditional Formatting... Ricky

20030305, 16:17 #3
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Ttest in Excel (Excel xp)
yes, i know how to do the cond formatting but i don't know how to use the T or Z test function. do you have a sample of that? thanks

20030305, 16:25 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Ttest in Excel (Excel xp)
=ABS(Mean1Mean2)/SQRT(((NumPts11)*Std1+(NumPts21)*Std2)/(NumPts1+NumPts22)*(1/NumPts1+1/NumPts2))>=TINV(10.95,NumPts1+NumPts22)
Calculates the Ttest result of comparing 2 means. If the above is true, then you reject the null hypothesis that they are the same.
You could use this formula in a conditional format equation directly (you would NOT need an extra column). You would need info on the Std Dev for each measurement and the number of points for each which you didn't give.
Steve

20030305, 16:40 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: Ttest in Excel (Excel xp)
The T and Z tests are available in Excel in the Analysis Toolpak. This is an addin that isn't loaded by default. If you want to use it, select Tools/Addins...
Sometimes, though, it's easier to do the computations yourself, see Steve (sdckapr)'s reply for an example.

20030305, 16:59 #6
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Ttest in Excel (Excel xp)
ok. Is the numPoints the sample size for each? I only have the mean for the sample size of 500. I got it from our data processing dept. Do i have to get them to give me the std dev for that question also? thanks

20030305, 18:31 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Ttest in Excel (Excel xp)
Yes, numPoints is sample size.
If you are comparing 2 samples, you need 6 pieces of data:
For each sample: MeanValue, StdDev (n1 weighted), Number of Points.
Then you also have to define the "confidence interval" which you did 95%.
Or do you want top compare a sample to yearly average?
Steve

20030306, 06:45 #8
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Ttest in Excel (Excel xp)
Excel's ATP is reknown for it's inaccuracy of the statistical functions in it. Furthermore, there are more statistical functions in Excel that may return dead wrong results.
If I have to do serious statistical work and have to be sure the results are exact, I never use Excel!Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030306, 10:32 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Ttest in Excel (Excel xp)
If you are comparing 2001 to 2002 (and you do NOT care about the individual values), but just making 1 comparison(Is 2001 different than 2002?) you have the info you need:
Mean1 = Average(2001Data) and Mean2 = Average(2002Data). NumPoints1 = Count(2001Data)=500, numPts2 = count(2002Data) = 500, Std1 = Stdev(2001Data), Std2 = Stdev(2002Data)
But in this case there is only the 2001 average and 2002 average to compare and highlight. So you would either HIGHLIGHT the 2002 average as different or NOT highlight.
Individual points are NOT being compared ONLY 2001 vs 2002.
If you want to compare INDIVIDUAL pieces of the 500 data points you need some idea of the confidence of EACH individual value (something to estimate its confidence interval: typically a Stdev and the number of points in addition to the mean)
Steve
Steve