Question

How to do on excel 1)Confidence Interval (both t-test and z-test and compare the results) ? 2) Establish a 95% and 90% confidence interval for the average heights for the female and male.

3) Establish the same above Confidence interval for the all the genders.
How to compare the Confidence interval with those of question 2?

https://sheet.zoho.com/sheet/editor.do?doc=52445b821f7b96571d5609586b4b98410aedfd15488aac2c4988b0891836c3ad0e1aca42e4aa1c0b11670202fa4203cbaf903f5fdcf73906623b6e2d2d2d980e

Answer 1

I copied the data into an Excel-compatible spreadsheet (Libre Office) and used the Statistical Functions to generate the desired parameters.

Explanation:

Once you have the values from the t- and F-tests you can do the desired comparisons. You should be able to "tweak" the parameters to get the same confidence intervals, although I think that they are already calculated with the original data calculations.

You can use the calculated values with this online confidence interval calculator to speed up the process:
https://www.danielsoper.com/statcalc/calculator.aspx?id=96

Answer 2

See Excel formulas below.

Explanation:

The following formulas will be demonstrated using the Female column of data.

Cell `"B43"` gets the sample mean `barx` of a group of cells:

`"=AVERAGE(B2:B41)"`

Cell `"B44"` gets the sample standard deviation `s` of a group of cells:

`"=STDEV.S(B2:B41)"`

Cell `"B45"` gets the count `n` of the number of data cells:

`"=COUNT(B2:B41)"`

Cell `"B47"` gets the `t_(alpha//2)`-value for a two-tailed test with total tail area `alpha = 0.05` and degrees of freedom `n-1`:

`"=T.INV.2T(0.05,B45-1)"`

Cell `"B48"` gets the lower bound on your confidence interval, which is `barx-t_(alpha//2)s/sqrtn:`

`"=B43-B47*B44/SQRT(B45)"`

... and cell `"B49"` gets the upper bound, which is `barx+t_(alpha//2)s/sqrtn:`

`"=B43+B47*B44/SQRT(B45)"`

For the Female column, you should get a 95% confidence interval of `(159.9, 164.6).`

To do a `z`-test instead of a `t`-test, replace cell `"B47"` with

`"=NORM.INV(0.975,0,1)"`

The 0.975 is from `1-alpha/2`, the 0 is for a mean of 0, and the 1 is for a standard deviation of 1 (i.e. the standard normal).

Since `n>30`, the confidence interval will be only slightly narrower for this `z`-test than for the `t`-test.

--

Repeat these steps for the different columns of data.