Question

Please show in EXCEL how to express with function formula.

As we discussed during Week 3, researchers decide to ask 4 tie-purchasing customers whether they bought a bow tie or normal tie. According to national data, 3% of all ties purchased are bow ties.

Not surprisingly, this problem can be easily framed as a Binomial Distribution problem, as it meets all of 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 fixed probability of .97 as a failure. In other words:

p = .03, q=(1-p) = .97, n=4.

1.         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 the probability of x = 0, 1, 2, 3, 4 in each of the cells. In other words, 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 in class.)

              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 all others in this Exercise, please round the numbers to 4 digits using Excel. You can do this after the fact from the toolbar, as we’ve shown before in class.)

Finally, in cell A46, use the SUM function to sum the individual probabilities you’ve computed in (a).

2.         In cell A48, compute the probability that at least one of the four persons purchased a bow tie by adding up each of the relevant probabilities, 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 least one of the four persons purchased a bow tie by taking the complement of the event, X = 0. (Note: again, do not type in any specific values. Reference the relevant cells from the values you’ve already computed.) Round to 4 digits. In cell B49, type in “1-P(X=0)”.

3.         In cells B40:B44, compute the cumulative probabilities for x = 0, 1, 2, 3, 4, respectively, by entering appropriate commands that reference your computed pdf probabilities in cells A40 through A44. Again, round to 4 digits. Label the column “CDF by hand” in cell B39.

              Then, in cells, C40, C41, …, C44 type in “x=0, x=1, x=2, x=3, x=4”, respectively.

4.         Now, in cell D39 type PDF BINOM. Now use the BINOM.DIST function to populate cells D40:D44 with the PDFs of x = 0, 1, 2, 3, 4. (Remember, the PDF requires the “FALSE” entry.) Make sure you round your answers to four digits.

5.         Finally, using BINOM.DIST, populate cells E40:E44 with the CDFs of x = 0, 1, 2, 3, 4, rounding your answers to four digits. (Remember, the CDF requires the BINOM.DIST “TRUE” entry. Further, you must use BINOM.DIST, i.e., do not reference the output found in any other column.) As in (d), label the column by typing “CDF BINOM” in cell E39.

              In cell E46, reference via an “=” sign the appropriate cell that identifies the probability that fewer than 2 persons 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 they bought a bow tie or normal tie. According to national data, 3% of all ties purchased are bow ties.

Not surprisingly, this problem can be easily framed as a Binomial Distribution problem, as it meets all of 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 fixed probability of .97 as a failure. In other words:

p = .03, q=(1-p) = .97, n=4.

1.         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 the probability of x = 0, 1, 2, 3, 4 in each of the cells. In other words, 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 in class.)

              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 all others in this Exercise, please round the numbers to 4 digits using Excel. You can do this after the fact from the toolbar, as we’ve shown before in class.)

Finally, in cell A46, use the SUM function to sum the individual probabilities you’ve computed in (a).

2.         In cell A48, compute the probability that at least one of the four persons purchased a bow tie by adding up each of the relevant probabilities, 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 least one of the four persons purchased a bow tie by taking the complement of the event, X = 0. (Note: again, do not type in any specific values. Reference the relevant cells from the values you’ve already computed.) Round to 4 digits. In cell B49, type in “1-P(X=0)”.

3.         In cells B40:B44, compute the cumulative probabilities for x = 0, 1, 2, 3, 4, respectively, by entering appropriate commands that reference your computed pdf probabilities in cells A40 through A44. Again, round to 4 digits. Label the column “CDF by hand” in cell B39.

              Then, in cells, C40, C41, …, C44 type in “x=0, x=1, x=2, x=3, x=4”, respectively.

4.         Now, in cell D39 type PDF BINOM. Now use the BINOM.DIST function to populate cells D40:D44 with the PDFs of x = 0, 1, 2, 3, 4. (Remember, the PDF requires the “FALSE” entry.) Make sure you round your answers to four digits.

5.         Finally, using BINOM.DIST, populate cells E40:E44 with the CDFs of x = 0, 1, 2, 3, 4, rounding your answers to four digits. (Remember, the CDF requires the BINOM.DIST “TRUE” entry. Further, you must use BINOM.DIST, i.e., do not reference the output found in any other column.) As in (d), label the column by typing “CDF BINOM” in cell E39.

              In cell E46, reference via an “=” sign the appropriate cell that identifies the probability that fewer than 2 persons purchased a bow tie. In cell F46 type in “P(X≤1)”.

Answer 1