Question

since vacation travel is a discretionary expense, consumers want to make the most out of the money they have budgeted. they often check travel websites for hotel ratings before making any reservations. a popular travel website offers travelers the opportunity to rate a hotel experience based on five categories: excellent, very good, average, poor, and terrible. use the spreadsheet to answer the questions below.

a	b	c	d	e
1	excellent	491	0.427	491	0.427
2	very good	448	0.390	939	0.817
3	average	144	0.125	1083	0.942
4	poor	40	0.035	1123	0.977
5	terrible	27	0.023	1150	1.000
7	total	1150

a. write the spreadsheet formula that was used to calculate the amount in cell b7. =sum(b2:b6)
b. write the spreadsheet formula that was used to calculate the amount in cell c3. =b3/b7
c. write the spreadsheet formula that was used to calculate the amount in cell d5. (there are two possible formulas, include both in your answer pacing the word \or\ between them)
d. write the spreadsheet formula that was used to calculate the amount in cell e6.
e. what percentage of the reviews were \poor\ as indicated by the amount in cell c5?
f. what is the number of reviews that were \average\ or better as indicated by the amount in cell d4?
g. what percentage of the reviews were \very good\ or better as indicated by the amount in cell e3?

Explanation:

Step1: Formula for cell D5

The cumulative frequency in D5 can be calculated by adding the frequency of the current row (B5) to the cumulative - frequency of the previous row (D4). So one formula is =D4 + B5. Another way is to sum up the frequencies from the first row to the current row, so the second formula is =SUM(B2:B5).

Step2: Formula for cell E6

The relative cumulative frequency in E6 is the cumulative frequency in D6 divided by the total frequency in B7. So the formula is =D6/B7.

Step3: Percentage of "Poor" reviews

The relative frequency in C5 gives the proportion of "Poor" reviews. To convert it to a percentage, we multiply by 100. So the percentage is 3.5% (since C5 = 0.035).

Step4: Reviews "Average" or better

The cumulative frequency in D4 represents the number of reviews that are "Average" or better. So the number is 1083.

Step5: Percentage of "Very Good" or better

The relative cumulative frequency in E3 represents the proportion of reviews that are "Very Good" or better. So the percentage is 81.7% (since E3 = 0.817).

Answer:

a. =SUM(B2:B6)
b. =B3/B7
c. =D4 + B5 or =SUM(B2:B5)
d. =D6/B7
e. 3.5%
f. 1083
g. 81.7%