Project Description: You have been hired to analyze the effectiveness of a local
ID: 3357761 • Letter: P
Question
Project Description:
You have been hired to analyze the effectiveness of a local restaurant’s drive-through service. As part of your analysis, you will evaluate customer satisfaction. You hypothesize that customer satisfaction decreases as wait time increases. You also believe there is a correlation between the quality of the food and overall satisfaction. You have collected a cross-section sample of 30 customers’ wait time and satisfaction level surveys based on the range of 1 to 5, with 1 being least satisfied and 5 being most satisfied. You have also pulled a random sample of 10 surveys to evaluate wait time satisfaction.
Instructions:
For the purpose of grading the project you are required to perform the following tasks:
Step
Instructions
Points Possible
1
Download and open the file named exploring_e08c1CustomerService, and then save the file as exploring_e08_grader_h1_LastFirst, replacing LastFirst with your name.
0.000
2
In the Survey worksheet, enter a conditional function in cell M3 to calculate average satisfaction of service for store 251 (cell L3). Format the results with the Number Format with two decimal points.
10.000
3
Use the fill handle in cell M3 to copy the function down to complete the calculations. Be sure that you have used the appropriate mixed or absolute referencing in M3 before copying.
5.000
4
Enter a function in cell M11 to calculate the number of survey responses in which restaurant 251 (in cell L11) received a 2 or less (in cell L10) in wait time and service.
9.000
5
Use the fill handle in cell M11 to copy the function down to complete the calculations. Be sure that you have used the appropriate mixed or absolute referencing in M11 before copying.
4.000
6
Use the RANK.AVG function in cell N3 to calculate the rank of store 251 based on the averages in the range M3:M5.
6.000
7
Use the fill handle to copy the function down the column. Be sure to include the appropriate absolute or mixed cell references in N3 before you copy.
2.000
8
Click the Wait Time worksheet and enter a function in cell F6 that calculates the wait time value of the first quartile.
9.000
9
Copy the function down to complete the column and return to the Survey worksheet.
3.000
10
Select the range J4:J8. Enter a function in the range J4:J8 to calculate the frequency of service ratings from 1 to 5 received by all restaurants.
6.000
Link to excel file
https://sheet.zoho.com/sheet/editor.do?doc=52445b821f7b96571d5609586b4b98416396a84a1919b355790a179f3181d5ea26a1402fc5e792cc2541533aba7a5e64e0514f8ea6815e71250e5d98d55a48a0
Step
Instructions
Points Possible
1
Download and open the file named exploring_e08c1CustomerService, and then save the file as exploring_e08_grader_h1_LastFirst, replacing LastFirst with your name.
0.000
2
In the Survey worksheet, enter a conditional function in cell M3 to calculate average satisfaction of service for store 251 (cell L3). Format the results with the Number Format with two decimal points.
10.000
3
Use the fill handle in cell M3 to copy the function down to complete the calculations. Be sure that you have used the appropriate mixed or absolute referencing in M3 before copying.
5.000
4
Enter a function in cell M11 to calculate the number of survey responses in which restaurant 251 (in cell L11) received a 2 or less (in cell L10) in wait time and service.
9.000
5
Use the fill handle in cell M11 to copy the function down to complete the calculations. Be sure that you have used the appropriate mixed or absolute referencing in M11 before copying.
4.000
6
Use the RANK.AVG function in cell N3 to calculate the rank of store 251 based on the averages in the range M3:M5.
6.000
7
Use the fill handle to copy the function down the column. Be sure to include the appropriate absolute or mixed cell references in N3 before you copy.
2.000
8
Click the Wait Time worksheet and enter a function in cell F6 that calculates the wait time value of the first quartile.
9.000
9
Copy the function down to complete the column and return to the Survey worksheet.
3.000
10
Select the range J4:J8. Enter a function in the range J4:J8 to calculate the frequency of service ratings from 1 to 5 received by all restaurants.
6.000
Explanation / Answer
2.
use this formula =+AVERAGEIF($B$4:$B$33,L3,$E$4:$E$33)
average the e4:e33 range whenever the b4:b33 range has l3 (251) value.
3.
This will work for other 372 and 679 if you drag it down.
4.
=+COUNTIFS($B$4:$B$33,L11,$C$4:$C$33,"<=2",$E$4:$E$33,"<=2")
count the number of responses when the b4:b33 has value l11(251) and c4:c33 range less than or equal to 2 and e4:e33 range has value less than or equal to 2.
5.
Drag it down. it will work for the other two cells as well.
6.
=+RANK.AVG(L3,$L$3:$M$5)
Find the rank of l3 in the array l3:m5
7.
Drag it down it will work for the other 2 cells as well
8.
=+QUARTILE.INC($B$3:$B$12,E6)
find the quartile number e6 in the range b3:b12. since e6 is 1, it will find 1st quartile
9.
Drag it down it will give results 0.0451 and 0.0307
10.
select j4:j8, press f2
enter this formula =+FREQUENCY(E4:E33,G4:G8)
press ctrl+shift and enter.
It will give the results, it is an array formula which finds the frequency of each of g4:g8 values in the range e4:e33
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.