# Excel 2013 Statistical Analysis #59: 2 Tail Mean Hypothesis T Test: P-value & Critical Value

welcome to excel 2013 statistical analysis video number 59 if you want to download this workbook business 210 chapter 9 second file click on the link below the video and we're talking about hypothesis testing for the t-distribution this is our third example we're going to do a two-tailed test here's our situation our report stated that the national mean price for used cars is nine thousand five hundred bucks you want to determine if the seattle-area mean price of used cars is different from the national mean and an alpha of point zero one is the mean price for seattle used cars different from the national average alright as always we're going to talk about our point of view what we're considering in our goal the point of view Seattle area researcher who wants to compare national mean used-car prices to Seattle's we are considering the population of Seattle used car prices and our goal is to run a hypothesis test to provide statistical evidence to determine whether Seattle used auto prices are different from the national mean now we're determining different from so that means if it's below a significant amount or above a significant amount then we want to reject the null hypothesis so that means we're really interested in starting with null hypothesis now a lot of the examples we've done I think this is the sixth example we've always started with the alternative meaning that we start with a comparative operator but here this is different from so we just say hey well if it's different from then the null is going to be equal so the MU used auto price in Seattle will be equal to and we'll have our hypothesized mean and I'll do that down here about 9,500 and that means that this is equal this is gonna be not equal I'm going to put space and then I'm going to use the Excel symbol less than greater than that means not all right so there's our no and all alternative we're going to do alpha we're going to be really sure remember the lower the alpha the less chance we have of making a type 1 error which is rejecting the null hypothesis even though it's true all right let's go ahead and make some calculations we don't have Sigma so we're going to use our T distribution we have to calculate sample standard deviation stdev dot s shift enter it looks like we have a sample standard deviation of 2,400 almost six bucks our sample size will use count because we're counting numbers control shift down arrow shift enter 75 degrees of freedom that'll help us determine which T distribution we're going to use so we take n minus the number of samples or mean control shift down o shift enter alpha we have that from up here point 0 1 right and be real sure to have to test this going to be a two-tail our standard error we take our s divided by square root how about sq tab square root of our n that's the standard deviation for the sampling distribution of x-bar so when we calculate in the numerator our sampling error we divide it by that standard deviation and that gives us our test statistic our t-test statistic which tells us how many standard deviations above or below hypothesized mean we are so we're 1.5 to or above right now here's our picture right click show hi anything we haven't calculated our critical values but there's our alpha and it's very small Oh point zero 1/2 is point zero zero five so anything between these two markers we fail to reject the null hypothesis anything above or below we will reject the null and accept the alternative hypothesis alright let's come down here let's roll up just a little bit and we want to calculate our P now remember for p we have a one point five two so if we say p-value is going to say wherever it is that value or more because it's a two tail we have to double it all right so we ready equals and we're gonna have to do 1 minus t dot and we use the dis our test statistic is that remember we have to do 1 minus because well if we put this in it'll calculate from negative infinity all the way up to there and we were interested in that the upper part comma our degrees of freedom and it's cumulative now that gives us just the upper amount point 0 6 6 we have to double that and we have to do this subtraction first so we're gonna have to put this in parenthesis now I kind of like this way because it just it's we know we have to double it but this one minus the disk whether we use in T or the norm s this 1 minus is how we've been doing it so it's consistent however there is a new function in two thousand I mean this T dot dist is new because there's a dot but this is brand-new this didn't ever exist before 2010 it is the specifically for the T dist there's a two tail so this two tail it's going to require simply the test statistic and the degrees of freedom now there is a possibility when you're running a two-tail that this could be negative or positive and this function and in my notes and over here this T statistic has to be on the upper end so if you are running an experimenter you have a template this could be negative or positive so you could put the ABS function around it it just abs is just absolute value which means distance from zero comma and if we're not going to run into situation here because this is on the upper end but it just as well could have shown up on the lower end all right and then we have our degrees of freedom so one advantage of T dot dis dot to tail is that you just have to throw in the T from the upper and the degrees of freedom all right and you get the same thing you don't have to double it you don't have to remember to multiply it by two either way you do it we can clearly see the p-value is gigantic compared to the point zero one so we will fail to reject the null hypothesis right so we're in this region right here we can use the critical value we want to determine this value in this value equals T dot and we want the inverse we want the probability now we're given point zero one and I need half of that so I'm going to say the alpha divided by two now this gives me from negative infinity up to that point which is great because this will give us the lower critical value I forgot the degrees of freedom degrees of freedom tells the T function which of the many T distributions to bill look at to calculate the test statistic minus two point six four that's the sort 'l here and the hurdle on the upper end is two point six four so our test statistic is not past that now to list the one on the upper end the curves are symmetric I'm going to say minus that I notice I didn't type in equal sign but Excel will know what that equal sign in now this is fine this is consistent with the way we've been doing it so far in this class there is a T dot inverse to tail so equals and brand new and 2010 T dot inverse and there's a two tail this one the cool thing about this is you don't have to divide by two because the function explicitly is for two tails so the probability means please give me alpha and then the degrees of freedom so and this is going to calculate the positive so that's why I put it in the upper critical value and then to get the one on the low end since its symmetrical si- that exactly the same numbers let's look at our conclusion because our p-value is bigger than our alpha we fail to reject clearly that is gigantic compared to that our critical value because our test statistic is between our critical values there's our test statistic and it's between these we fail to reject the sample evidence does not suggest that the mean price for used cars in Seattle area is different from the national average set a different way at a point zero five oops that's oh one we wanted to be super sure significance level our sample mean of ninety nine nine thousand nine hundred and twenty three does not provide statistical evidence to show that the mean Seattle use price is different from the national mean and finally we do run this type two risk of a type two error not accepting the alternative hypothesis the mean is different from when in fact it is different from alright so those are last three videos three examples of the t distribution and the T functions in our next video we'll we have two more videos for this chapter see you next video 