T-test in Excel (Excel xp)

Topic

New Reply

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.

Reply | Quote

Replies

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 out-of-sight 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…

Reply | Quote

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

Reply | Quote

=ABS(Mean1-Mean2)/SQRT(((NumPts1-1)*Std1+(NumPts2-1)*Std2)/(NumPts1+NumPts2-2)*(1/NumPts1+1/NumPts2))>=TINV(1-0.95,NumPts1+NumPts2-2)

Calculates the T-test 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

Reply | Quote

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

Reply | Quote

Yes, numPoints is sample size.

If you are comparing 2 samples, you need 6 pieces of data:
For each sample: MeanValue, StdDev (n-1 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

Reply | Quote

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

Reply | Quote

The T and Z tests are available in Excel in the Analysis Toolpak. This is an add-in that isn’t loaded by default. If you want to use it, select Tools/Add-ins…

Sometimes, though, it’s easier to do the computations yourself, see Steve (sdckapr)’s reply for an example.

Reply | Quote

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!

Reply | Quote