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?
B3B4 SUMPRODUCT(B2:B4, B10) B4B10 SUM(B2:B4) B2B10 B3B2 B3B10
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) B10B3
C) What formula should go into cell E10 to calculate the Refund amount?
B10B4 =IF(B10>5,(B10-5)B4,0) =IF(B10<5,0,B10B4) =IF(B10<5,B10B4,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?

A) B2*B10: cell B2 and B10 have the values regular swear costs and number of swears respectively and we need to multiply these two values to get our answer

B) =IF(B10>10,(B10-10)*B3,0): Sam is supposed to pay an extra $2 for swear words over 10 and so we check if his swear words are above 10 and if they are we find out how many they are by subtracting 10 from them and then we multiply the value gotten by the cost for extra swear words($2)

C) =IF(B10<5,B10*B4,0): here we check if swear words are less than 5 and if they are we multiply number of swears words less than by 5 by the cost ($0.50)

D) F10=C10+D10+E10: to calculate total money in jar(F10), we simply add up regular cost(C10), extra cost(D10) and refund(E10)