Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    T-test 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.

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: T-test 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 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...
    - Ricky

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: T-test 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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: T-test in Excel (Excel xp)

    =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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: T-test in Excel (Excel xp)

    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.

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: T-test 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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: T-test 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 (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

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: T-test 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.jkp-ads.com
    Professional Office Developers Association

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: T-test 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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •