Sammy the Sailor swears entirely too much. The following probability distribution shows the number of times Sammy swears per day and the corresponding probabilities: # of swear words: 2 5 9 14 20
Probability: 0.01 0.09 0.30 0.40 0.20
In an effort to reduce his amount of swearing, Sammy places $1.00 in a jar every time he swears. Further, if at the end of the day he swears more than 10 times, he places an extra $2.00 in the jar per swear word over 10. If Sammy swears less than 5 times, he takes out $0.50 for each of his swear words.
A B C D E F G
1 # of swear word Probability
2 Cost per swear word $1.00 2 0.01
3 Extra cost per swear
word over 10 $2.00 5 0.09
4 Refund per swear word
less than 5 $0.50 9 0.3
5 14 0.4
6 20 0.2
7
8
9 # Regular Extra cost Refund Total
swear swear if over 10 if under money
words word swear 5 swear in the jar
cost words words for the
day
10
Based off the partial simulation spreadsheet above, answer the following questions:
A) What formula should go into cell C10 to calculate the Regular Swear word cost?
B3*B4 SUMPRODUCT(B2:B4, B10) B4*B10 SUM(B2:B4) B2*B10 B3*B2 B3*B10
B) What formula should go into cell D10 to calculate the Extra Swear word cost?
=IF(B10>10,(B10-10)*B3,0) =IF(B10>10,(10-B10)*B3,0) =(B10-10)*B3 =IF(B10>10,0,(B10-10)*B3) SUMPRODUCT(B10,B3) B10*B3
C) What formula should go into cell E10 to calculate the Refund amount?
B10*B4 =IF(B10>5,(B10-5)*B4,0) =IF(B10<5,0,B10*B4) =IF(B10<5,B10*B4,0) SUMPRODUCT(B10:B4) =IF(B10<5,(B10-5)*B4,0)
D) What formula should go into cell F10 to calculate the total money in the jar?