Hypothesis Testing for Means with Excel 2007

Hi Readers,

I am back with the topic. In this post we will be learning how to do a basic Hypothesis Testing Analysis for Mean of the Data. So first thing first, here are the definitions from Wikipedia:

Hypothesis Defined:

"A statistical hypothesis test is a method of making decisions using data from a scientific study. In statistics, a result is called statistically significant if it has been predicted as unlikely to have occurred by chance alone, according to a pre-determined threshold probability, the significance level."

What is Hypothesis Testing:

"Statistical hypothesis tests define a procedure which controls (fixes) the probability of incorrectly deciding that a default position (null hypothesis) is incorrect based on how likely it would be for a set of observations to occur if the null hypothesis were true."

So basically while examining a hypothesis we have either of the three Assuminptions

Null Hypothesis

$(H_{0})=\mu_{0}=\mu$

First Condition 

$\mu_{0}\neq \mu$

Second Condition 

$\mu_{0}\geq \mu$

Third Condition 

$\mu_{0}\leq \mu$

So Hypothesis is tested for either of the three alternatives, lets examine it on the basis of some examples:

Case I

A study of a sample of 196 bank accounts showed the average size of back accounts in Rs. 7542. From previous studies of bank accounts, it is known that the standard deviation is Rs. 2984. Test the hypothesis that µ = 8000 against the hypothesis that µ ≠ not equal to 8000. Use α = 0.01 level of significance.

Data: From the above example we can conclude the following data.

Mean	µ	8000
H1	≠	8000
Alpha	α	0.01
Std. Dev	σ	2984
Mean	X	7542
n		196
Tails		Two Tailed
Result		??

Manual Workout will first calculate the Standard Deviation Units

$$H_o=\mu=\mu_0=7542$$
$$H_o=\mu\neq\mu_0=7542$$
$$z=\frac{x-\mu}{\sigma\sqrt{n}}=\frac{8000-7542}{2984\sqrt{196}}=2.148$$

Conclusion: Since calculated value of Z falls with +/-2.58 we will Accept the Claim

Case II

A certain type of seed has always grown to a mean height of 850 inches. A sample of 30 seeds grown under new conditions has a mean height of 920 inches and a standard deviation of 80 inch. At the 1% significance level, test the hypothesis that the new conditions grow better plants.

Data:

Mean	µ	850
H1	>	850
Alpha	α	0.01
Std. Dev	σ	80
Mean	X	920
n		30
Tails		Two Tailed
Result		??

Solution:

$$H_o=\mu=\mu_0=850$$
$$H_o=\mu\neq\mu_0=850$$
$$z=\frac{x-\mu}{\sigma\sqrt{n}}=\frac{920-850}{80\sqrt{30}}=4.76$$

Conclusion: Since calculated value of Z greater then 2.33 we will Reject the Claim

Case III

An advertising company claimed that the average advertising budget for small firms is more than Rs. 45000 per year. To test its claim a random sample of 125 small firms showed an average of Rs. 42710 per year and standard deviation of Rs 13645 per year. Do we accept or reject the hypothesis at 0.05 level of significance.

Data:

Mean	µ	45000
H1	>	45000
Alpha	α	0.05
Std. Dev	σ	13645
Mean	X	42710
n		125
Tails		Two Tailed
Result		??

Solution:

$$H_o=\mu=\mu_0=45000$$

$$H_o=\mu>\mu_0=45000$$
$$z=\frac{42710-45000}{13645\sqrt{125}}=-1.87$$


Conclusion: Since calculated value of Z is less then -1.65 we will Reject the Claim

CALCULATION USING EXCEL FORMULA:

Now the center point of the post how can we do all this using excel formula it is simple

Case I

=IF(AND(((C5-C1)/(C4/SQRT(C6)))>NORMSINV(C3/2),((C5-C1)/(C4/SQRT(C6)))<(NORMSINV(C3/2)*-1)),"Accept Null Hypothesis","Reject Null Hypothesis")

Case II

=IF((C14-C10)/(C13/SQRT(C15))>ABS(NORMSINV(C12)),"Reject Null Hypothesis","Accept Null Hypothesis")

Case III

[Please see workbook for this formula]

So this concludes the post, hope that you will enjoy the post and give feed back. Download this sample workbook and Explore the procedure. 

With Regards,

Faseeh