Please show in EXCEL how toexpress with function formula.
As we discussed during Week 3,researchers decide to ask 4 tie-purchasing customers whether theybought a bow tie or normal tie. According to national data, 3% ofall ties purchased are bow ties.
Not surprisingly, this problem can beeasily framed as a Binomial Distribution problem, as it meets allof the conditions outlined in our Week 4 (Monday) class. Further,we can identify the purchase of a bow tie with fixed probability of.03 as a success; the purchase of a standard tie with fixedprobability of .97 as a failure. In other words:
p = .03, q=(1-p) = .97, n=4.
- On your Excel sheet,type in “PDF by hand” in cell A39. Then in cells A40 through A 44,use the COMBIN and POWER (or “^”) functions to compute theprobability of x = 0, 1, 2, 3, 4 in each of the cells. In otherwords, in cell A40, compute P(X=0); cell A41, P(X=1); and so on.(That is, you’re using Excel to apply the binomial formula shown inclass.)
Hint: For P(X=0), enter the following:
“=COMBIN(4,0)*POWER(0.03,0)*POWER(1-0.03,4-0)” or
=COMBIN(4,0)*.03^0* (1-.03)^(4-0)”
(Note: for this problem, and allothers in this Exercise, please round the numbers to 4digits using Excel. You can do this after the fact fromthe toolbar, as we’ve shown before in class.)
Finally, in cell A46, use the SUMfunction to sum the individual probabilities you’ve computed in(a).
- In cell A48, computethe probability that at least one of the fourpersons purchased a bow tie by adding up each of the relevantprobabilities, rounding to 4 digits. (Note: do not type in any values. To receive credit,you must reference the relevant cells in your equations.)
Then, in cell A49, compute the probability that at leastone of the four persons purchased a bow tie bytaking the complement of the event, X = 0. (Note: again, do nottype in any specific values. Reference the relevant cells from thevalues you’ve already computed.) Round to 4 digits. In cell B49,type in “1-P(X=0)”.
- In cells B40:B44,compute the cumulative probabilities for x = 0, 1,2, 3, 4, respectively, by entering appropriate commands thatreference your computed pdf probabilities in cells A40 through A44.Again, round to 4 digits. Label the column “CDF by hand” in cellB39.
Then, in cells, C40, C41, …, C44 type in “x=0, x=1, x=2, x=3, x=4”,respectively.
- Now, in cell D39type PDF BINOM. Now use the BINOM.DIST function to populate cellsD40:D44 with the PDFs of x = 0, 1, 2, 3, 4. (Remember, the PDFrequires the “FALSE” entry.) Make sure you round your answers tofour digits.
- Finally, usingBINOM.DIST, populate cells E40:E44 with the CDFs of x = 0, 1, 2, 3,4, rounding your answers to four digits. (Remember, the CDFrequires the BINOM.DIST “TRUE” entry. Further, you must useBINOM.DIST, i.e., do not reference the output found in any othercolumn.) As in (d), label the column by typing “CDF BINOM” in cellE39.
In cell E46, reference via an “=” sign the appropriate cell thatidentifies the probability that fewer than 2persons purchased a bow tie. In cell F46 type in“P(X?1)”.
As we discussed during Week 3,researchers decide to ask 4 tie-purchasing customers whether theybought a bow tie or normal tie. According to national data, 3% ofall ties purchased are bow ties.
Not surprisingly, this problem can beeasily framed as a Binomial Distribution problem, as it meets allof the conditions outlined in our Week 4 (Monday) class. Further,we can identify the purchase of a bow tie with fixed probability of.03 as a success; the purchase of a standard tie with fixedprobability of .97 as a failure. In other words:
p = .03, q=(1-p) = .97, n=4.
- On your Excel sheet,type in “PDF by hand” in cell A39. Then in cells A40 through A 44,use the COMBIN and POWER (or “^”) functions to compute theprobability of x = 0, 1, 2, 3, 4 in each of the cells. In otherwords, in cell A40, compute P(X=0); cell A41, P(X=1); and so on.(That is, you’re using Excel to apply the binomial formula shown inclass.)
Hint: For P(X=0), enter the following:
“=COMBIN(4,0)*POWER(0.03,0)*POWER(1-0.03,4-0)” or
=COMBIN(4,0)*.03^0* (1-.03)^(4-0)”
(Note: for this problem, and allothers in this Exercise, please round the numbers to 4digits using Excel. You can do this after the fact fromthe toolbar, as we’ve shown before in class.)
Finally, in cell A46, use the SUMfunction to sum the individual probabilities you’ve computed in(a).
- In cell A48, computethe probability that at least one of the fourpersons purchased a bow tie by adding up each of the relevantprobabilities, rounding to 4 digits. (Note: do not type in any values. To receive credit,you must reference the relevant cells in your equations.)
Then, in cell A49, compute the probability that at leastone of the four persons purchased a bow tie bytaking the complement of the event, X = 0. (Note: again, do nottype in any specific values. Reference the relevant cells from thevalues you’ve already computed.) Round to 4 digits. In cell B49,type in “1-P(X=0)”.
- In cells B40:B44,compute the cumulative probabilities for x = 0, 1,2, 3, 4, respectively, by entering appropriate commands thatreference your computed pdf probabilities in cells A40 through A44.Again, round to 4 digits. Label the column “CDF by hand” in cellB39.
Then, in cells, C40, C41, …, C44 type in “x=0, x=1, x=2, x=3, x=4”,respectively.
- Now, in cell D39type PDF BINOM. Now use the BINOM.DIST function to populate cellsD40:D44 with the PDFs of x = 0, 1, 2, 3, 4. (Remember, the PDFrequires the “FALSE” entry.) Make sure you round your answers tofour digits.
- Finally, usingBINOM.DIST, populate cells E40:E44 with the CDFs of x = 0, 1, 2, 3,4, rounding your answers to four digits. (Remember, the CDFrequires the BINOM.DIST “TRUE” entry. Further, you must useBINOM.DIST, i.e., do not reference the output found in any othercolumn.) As in (d), label the column by typing “CDF BINOM” in cellE39.
In cell E46, reference via an “=” sign the appropriate cell thatidentifies the probability that fewer than 2persons purchased a bow tie. In cell F46 type in“P(X?1)”.
