Question

1. 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
2	excellent	491	0.427	491	0.427
3	very good	448	0.390	939	0.817
4	average	144	0.125	1083	0.942
5	poor	40	0.035	1123	0.977
6	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.
b. write the spreadsheet formula that was used to calculate the amount in cell c3.
c. write the spreadsheet formula that was used to calculate the amount in cell d5.
d. write the spreadsheet formula that was used to calculate the amount in cell e6.
e. describe what the amount in cell c5 means in the context of this problem.
f. describe what the amount in cell d4 means in the context of this problem.
g. describe what the amount in cell e3 means in the context of this problem.

Explanation:

Step1: Calculate total frequency sum

In a spreadsheet, to find the total (cell B7), we sum up all the frequencies in the frequency - column. The formula is =SUM(B2:B6).

Step2: Calculate relative frequency

To calculate the relative frequency (cell C3), we divide the frequency of the category by the total frequency. The formula is =B3/$B$7. The dollar signs are used to make the reference to B7 an absolute reference so that when the formula is copied down, the denominator remains the total frequency.

Step3: Calculate cumulative frequency

To calculate the cumulative frequency (cell D5), we sum up the frequencies of all previous categories and the current category. For cell D5, the formula is =SUM(B2:B5).

Step4: Calculate cumulative relative frequency

To calculate the cumulative relative frequency (cell E6), we divide the cumulative frequency of the category by the total frequency. The formula is =D6/$B$7.

Step5: Interpret cell C5

The amount in cell C5 represents the relative frequency of the "Poor" rating. It shows the proportion of the total number of hotel - experience ratings that are "Poor". In this case, it is 0.035, which means that 3.5% of the ratings are "Poor".

Step6: Interpret cell D3

The amount in cell D3 represents the cumulative frequency of the "Very Good" rating. It is the sum of the frequencies of the "Excellent" and "Very Good" ratings. So, 1083 ratings are either "Excellent" or "Very Good".

Answer:

a. =SUM(B2:B6)
b. =B3/$B$7
c. =SUM(B2:B5)
d. =D6/$B$7
e. The proportion of "Poor" ratings among all ratings is 3.5%.
f. The sum of "Excellent" and "Very Good" ratings is 1083.