All posts must 100% original work. NO PLAGIARISM. Post results must be provided using the Excel attached. Make sure you interpret your results on a Word Document.
Using the data set you collected in Week 1, excluding the super car outlier, you should have calculated the mean and standard deviation during Week 2 for price data. Along with finding a p and q from Week 3 excel. Using this information, calculate two 95% confidence intervals. For the first interval you need to calculate a T-confidence interval for the sample population. You have the mean, standard deviation and the sample size, all you have left to find is the T-critical value and you can calculate the interval. For the second interval calculate a proportion confidence interval using the proportion of the number of cars that fall below the average. You have the p, q, and n, all that is left is calculating a Z-critical value,
Make sure you include these values in your post, so your fellow classmates can use them to calculate their own confidence intervals. Once you calculate the confidence intervals you will need to interpret your interval and explain what this means in words.
Do the confidence intervals surprise you, knowing what you have learned about confidence intervals, proportions and normal distribution? Please the Week 5 Confidence T-Interval Mean and Unknown SD PDF and the Week 5 Confidence Interval Proportions PDF at the bottom of the discussion. This will give you a step by step example on how to help you calculate this using Excel.
Next, we are going to construct a 1-sample Proportion confidence interval using
̂ and �̂�
1 – sample Confidence Interval Estimate for Proportions. The equation will look
like this: �̂� ± ∗ (√
�̂�∗�̂�
)
Notice that we are using a Z – critical value for this confidence interval because
we are referring to a proportion.
Everything after the ± sign is called the Margin of Error.
Margin of Error (ME) = ∗ (√
�̂�∗�̂�
)
The Standard Error (SE) = (√
�̂�∗�̂�
)
You will need to calculate the Z- critical values for this confidence interval. To
calculate the Z-critical value we will use the =NORM.S.INV( ) function in Excel.
For a 95% confidence interval, you will take 1 – .95 = .05. α = .05. But since this is
a confidence interval and we will need to add AND subtract from the mean we
will take .05/2 = .025. The new α = .025. But remember how Excel puts functions
in a less than form. To find the value we will use in the Excel function we will take
1 – .025 = .975. This is the alpha value you use in the Excel function.
Z-Critical Values do not have degrees of freedom so all you need to do is plug in
.975 into the function and hit Enter.
In Excel hit the = sign, then NORM.S.INV( .975), then close the parentheses, and
hit Enter.
The Z-Critical Value for a 95% confidence interval is 1.96.
Let’s look at an example. Recall from Week 3 that we calculated a p and q. p was
the number of successes below the average. From my example we see that I had
7 observations below the average where:
̂ = .70 and
�̂� = 30
I want to calculate a 95% confidence interval the proportion of car prices that will
fall below the average.
Using this equation, we will plug in what we know.
�̂� ± ∗ (√
�̂� ∗ �̂�
)
. 70 ± 1.96 (√
. 70 ∗ .30
10
)
. 70 ± 1.96(. 1449137)
. 70 ± .28403
(.4159, .98403)
The 95% confidence interval is 41.6% to 98.4%
We are 95% confident that the proportion of cars sampled that will fall below the
average goes from 41.6% to 98.4%.
Next, we are going to construct a 1-sample T confidence interval using a Mean
and SD.
1 – sample Confidence Interval Estimate for the Mean and an unknown σ. The
equation will look like this: �̅� ± ∗ (
√
)
Notice that we are using a T – critical value for this confidence interval because
we are referring to a sample and not an entire population.
Everything after the ± sign is called the Margin of Error. (Hopefully this sounds
familiar because we introduced it during Week 2)
Margin of Error (ME) = ∗ (
√
)
The Standard Error (SE) = (
√
) (This is also a value you have seen during Week 2).
BUT, if you don’t have the Marin of Error you will need to calculate the T- critical
value. To calculate the T-critical value we will use the =T.INV( ) function in Excel.
For a 95% confidence interval, you will take 1 – .95 = .05. α = .05. But since this is
a confidence interval and we will need to add AND subtract from the mean we
will take .05/2 = .025. The new α = .025. But remember how Excel puts functions
in a less than form. To find the value we will use in the Excel function we will take
1 – .025 = .975. This is the alpha value you use in the Excel function.
Next, we need to find the degrees of freedom. The degrees of freedom (DF) = n –
1. Because the sample size is 10. DF = 10 – 1 = 9. Now that we have these two
values you can use the Excel function to find the T critical value.
In Excel hit the = sign, then T.INV( .975, 9), then close the parentheses, and hit
Enter.
The T-critical value is 2.262157
Now that we have all the values we need we can calculate the confidence
interval. But first let’s review the descriptive statistics we found during Week 2.
Highlighted in Yellow are the SE and the ME that were calculated using the
descriptive statistics tool using the Data Analysis ToolPak. We also see that the
ME is for a 95% confidence, BUT this can be changed and customized to the value
you want.
When you use the Data Analysis ToolPak and click on Descriptive Statistics when
the new window pops up and you check the box that says “Confidence Level for
Mean” the default is 95%, but if you wanted to change that to 90% you can.
Once you change this to the value you want click OK.
The new Margin of Error is highlighted in Yellow. This is a nice short cut you can
use to cut down on the amount of algebra you will be doing.
Going back to our 95% confidence interval using this equation.
�̅� ± ∗ (
√
)
The mean is still $25,650 and now we know the Margin of Error is $2,495.50331.
Plugging these into the equation we get
25,650 ± 2.262157∗ (
3488.47308
√10
)
25,650 ± 2495.50331
25,650 – 2495.50331 = $23,155 -> rounded to the nearest dollar
25,650 + 2495.50331 = $28,146 -> rounded to the nearest dollar
The 95% confidence interval is ($23,155, $28,146).
We are 95% confidence that the sample price of the cars will be between $23,155
and $28,146.
Sheet1
Type Year Make Model Price MPG(CITY) MPG (HIGHWAY) Weight
variable type: qualitative variable type: quantitative variable type: qualitative variable type: qualitative variable type: quantitative variable type: quantitative variable type: quantitative variable type: quantitative
SUV 2021 Mazda CX-30 $22,795 25 33 3232
compact crossover 2021 Toyota rav4prime $29,458 36 40 5,530
SUV 2021 Chrysler Voyager $28,730 19 28 4,330
minivan 2020 kia Sedona $28,720 18 24 6,085
minivan 2020 Dodge grand caravan $29,025 17 25 4,510
passenger wagon 2020 Ford transit connect $28,315 24 26 3,689
SUV 2020 Volkswagen Tigwan $25,965 22 29 3,847
SUV 2019 kia Sorento $28,110 22 29 3,810
SUV 2020 Honda Odyssey $32,110 19 28 4,593
SUV 2021 Hyundai Palisade $33,665 19 24 4,284
sports car 2020 Bugatti La Voiture $3,250,000 9 14 4,400
SUMMARY BEFORE ADDING OUTLIER
mean $28,689 $22 $29 $4,391
standard deviation 2977.7926966873 5.5467708324 4.8579831206 863.2415652643
median $28,725 $21 $28 $4,307
SUMMARY AFTER ADDING OUTLIER
mean $321,536 $21 $27 $4,392
standard deviation $971,266 $7 $6 $819
median $28,730 $19 $28 $4,330
DISCUSSION
standard deviation 2977.7926966873
mean= 28689.3
new sd= 1488.8963483437
PROBABILITIES
P(X<(28689.3-500))=P(X<28189.3) 36.85% the probability that the price for the next 4 cars that are sampled will be less than $ 500 below the mean is 36.85%
P(X>(28689.3+1000)=1-P(X<29689.3) 25.09% the probability that the price for the next 4 cars that are sampled will be higher than $1000 above the mean is 25.09%
P(X=28689.3) 0.03% the probability that the price for the next 4 cars that are sampled will be equal to the mean is 0.3%
P(28689.3-1500