Question

Please do it in Excel and show detailed work I'm really confused about how to put the Z- value on Excel. I will rate you!

1. The crab spider, Thomisus spectabilis, sits on flowers and preys upon visiting honeybees. Do honeybees distinguish between flowers that have crab spiders and flowers that do not? To test this, Heiling et al. (2003) gave 33 bees a choice between 2 flowers: one with, and one without a crab spider. In 23 of the 33 trials, the bees picked the flower that had the spider. In the other trials, the bees chose the spiderless flower.

   With these data, carry out the appropriate hypothesis test (one- or two-tailed), using the normal approximation to the binomial distribution to determine Z. For a one-tailed test, use the formula =(1-NORM.DIST(Z,0,1,TRUE) in Excel calculate P. For a two-tailed test, use the formula =2(1-NORM.DIST(Z,0,1,TRUE).

   State your answer for the value of P to three decimal places, and include the leading zero.

   • Do all of the math in Excel
   • DO NOT round the value of Z.
   • Substitute the cell (e.g. B1) for Z in the formula for P.

Answer 1

Problem statement:-A statistical experiment is described on honeybees. On studying the experiment we have answer few questions.

Given:- An experimental study on honeybees is described .It is also provided that this binomial distribution should be approximated to normal distribution.

For the binomial distribution described,

Probability (success)= Probability (honeybee choosing the flower with crab spider)=0.5=p

Probability (failure)= Probability (honeybee not choosing the flower with crab spider)=0.5=q

Approximating this distribution to normal ,we get

mean= number of samples * (probability (success))=33*0.5=16.5

standard deviation= (number of samples * (probability (success))* (probability (failure)))^{^0.5}=2.87

Also for this study,

Null hypothesis (H0): Honeybees cannot distinguish between flowers with or without crab spiders.

Alternate hypothesis (Ha): Honeybees can distinguish between flowers with or without crab spiders.

This results in a two-tailed hypothesis test.

Solution:-

Assume the following table to be excel sheet

A	B	C
Description	Value	Formula to be used in excel
number of samples	33	Given in the question
Probability of success-Binomial	0.5
Probability of failure-Binomial	0.5
mean-approximated to normal	16.5	=B2*B3
Standard deviation-approximated to normal	2.8722	=SQRT(B2*B3*B4)
Number of cases where honeybees satin flowers with crabspiders	23	Given in the question
Z-score calculation	3.307475	=(B7-B5)/(B6)
P-value for two-tailed test	0.000941	=2*(1-NORM.DIST(B8,0,1,TRUE))

The P-value obtained for this study is 0.000941. At 5% confidence we would reject the null hypothesis and conclude that Honeybees can distinguish between flowers with or without crab spiders.