Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Tiana Morales is the director of the customer call center for Plerce Software. For the past three months, she has been keeping track of the
Tiana Morales is the director of the customer call center for Plerce Software. For the past three months, she has been keeping track of the support calls the center receives, and asks for your help in analyzing the data. Go to the Customer Call Log worksheet, which contains the Calls table where Tiana has been entering support call data. In the Call Type column, she needs to list the type of call corresponding to the code in the Call Code column. In cell ES, enter a formula using the HLOOKUP function to look up the call type according to the call code. Use a structured reference to look up the value in the Call Code column of the Calls table. Retrieve the value in row 2 of the CallTypes table (range A3:F4 in the Survey Questions worksheet) using a structured reference to the entire CallTypes table. Because each call type covers a range of values, find an approximate match. Fill the formula into the range E6:E143, If necessary. Pierce Software 2 Customer Call Log: January March 3 Quest Call Typ Average Total Calls Average Score Call Cod 7 1 17 16 21 18 7 1 4 2 5 4 4 4 3 2 3 4 5 Rep Barry Bruno Calder Darego Shanahan Tnh Volmann Wagner Zimmerman 2 8 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Call It 20191 20154 21170 21145 22103 22246 22137 22234 23228 23215 24117 24140 25224 23126 25210 20189 20126 26157 25201 26149 23222 24241 24152 24195 26242 27143 20155 21171 22227 20177 27196 22213 25200 Da Service Re 1/4/21 Wagner 14/21 Thanh 1/4/21 Darego 1/5/21 Calder 1/5/21 Shanahan 1/6/21 Bruno 1/7/21 Tran 1/7/21 Sanahan 1/7/21 Wagner 1/8/21 Calder 1/9/21 Darego 1/9/21 Bruno 11121 Thnh 1/12/21 Volmann 1/12/21 Barry 1/14/21 Wagner 1/14/21 Shanahan 1/15/21 Bruno 11621 Tran 1/16/21 Calder 1/16/21 Calder 1/16/21 Bruno 117/21 TT 1/17/21 Volmann 1/17/21 Darego 1/17/21 Volmann 1/18/21 Barry 11821 TH 1/18/21 Shanahan 1/20/21 Bruno 1/20/21 Zimmerman 1/20/21 Volmann 1/21/21 Barry 25 9 3 2 12 21 20 22 16 19 3 17 18 16 4 5 3 1 3 2 4 5 5 Customer Survey Scores Quest Quest Quest 2 4 5 2 4 1 3 1 5 5 5 3 4 5 3 3 3 3 1 1 1 2 2 1 3 3 2 5 3 5 3 3 5 5 4 5 2 2 1 1 1 1 4 4 2 3 2 2 3 3 4 4 5 5 5 5 5 3 3 4 4 5 3 3 5 2 2 4 3 3 3 5 3 5 3 3 2 2 2 1 3 3 3 4 3 4 5 4 4 5 5 5 4 4 4 5 2 1 30 31 32 33 34 35 35 37 15 5 22 18 15 4 5 4 38 39 6 3 5 4 5 5 4. 5 5 41 42 4 3 4 4 3 2 5 44 45 45 2 1 4 2 3 5 28144 28147 29215 26128 29136 29182 24126 27147 28117 21178 26242 30119 30194 21223 24132 30171 17 5 16 17 20 10 11 16 5 25 21 13 8 3 2. 5 5 3 3 1 4 49 50 3 1 5 5 3 4 5 3 2 1 3 5 5 4 3 2 4 5 3 2 1 4 3 5 4 3 4 3 1 5 4 14 23161 4 52 53 54 55 56 57 58 59 60 61 1/22/21 Calder 1/22/21 Zimmerman 12321 Thi 1/23/21 Bruno 1/24/21 Darego 1/25/21 Zimmerman 1/26/21 Bruno 1/27/21 Barry 1/28/21 Volmann 128/21 Thanh 1/29/21 Calder 173/21 Thnh 1/29/21 Barry 1/30/21 Zimmerman 1/30/21 Shanahan 1/31/21 Barry 1/31/21 Tham 2/4/21 Barry 2/4/21 Calder 2/4/21 Zimmerman 2/5/21 Thnh 2/5/21 Bruno 2/6/21 Darego 2/7/21 Zimmerman 2/7/21 Bruno 2/7/21 Barry 2/8/21 Calder 2/9/21 Darego 2/9/21 Bruno 2/12/21 Trari 2/12/21 Volmann 2/12/21 Barry 2/14/21 Wagner 2/14/21 Shanahan 2/15/21 Bruno 215/21 ThaI 21521 Thi 2/16/21 Darego 2/17/21 Calder 2/17/21 Shanahan 3 4 5 4 5 3 1 5 5 5 5 4 3 4 3 4 4 3 23162 23163 23170 24165 24166 24167 24168 24169 24170 24171 24172 24173 24174 25175 25176 25177 25178 25179 25180 26181 26182 26183 26184 65 66 4 4 3 2 5 3 4 3 2 3 5 3 2 3 4 5 4 3 4 4 5 21 20 22 16 19 3 17 18 16 4 5 16 17 20 Z 3 4 5 3 3 2 1 68 69 70 71 72 73 74 75 76 77 4 3 1 5 5 3 2 4 4 3 5 4 3 4 4 2 5 4 3 2 3 3 3 5 4 5 5 4 4 2 5 4 2 3 4 5 3 4 5 3 1 3 5 4 5 4 3 4 5 3 2 4 1 3 5 5 3 2 3 3 2 3 4 5 4 5 4 3 2 26185 26185 25187 26188 26189 27190 27191 27192 27193 27194 28195 28195 28197 28198 28199 28200 29201 29202 29203 29204 29205 29206 29207 29208 29209 29210 29211 30212 30213 30214 30215 30216 30217 31218 31219 31220 31221 31222 31231 31224 2/17/21 Bruno 21721 Tnh 2/17/21 Darego 2/18/21 Zimmerman 2/18/21 Bruno 2/18/21 Barry 2/20/21 Volmann 2/3/21 Thn 2/20/21 Calder 2/21/21 Thi 2/21/21 Barry 2/22/21 Zimmerman 2/23/21 Shanahan 2/23/21 Barry 22/21 Tham 2/25/21 Barry 2/26/21 Calder 2/26/21 Zimmerman 2/28/21 Sunahan 2/28/21 Barry 3421 THI 3/4/21 Barry 3/4/21 Calder 3/5/21 Zimmerman 3521 Thi 3/6/21 Bruno 3/6/21 Darego 3/7/21 Zimmerman 3/7/21 Bruno 3/8/21 Barry 33/21 THI 3/9/21 Shanahan 3/10/21 Bruno 3/12/21 Zimmerman 3/12/21 Volmann 3/13/21 Barry 3/14/21 Calder 3/15/21 Zimmerman 3/16/21 Wagner 3/16/21 Bruno 5 25 21 13 8 14 20 21 20 22 16 19 3 17 18 16 4 4 15 5 18 16 4 5 16 17 20 10 11 16 5 25 3 17 18 16 4 4 3 2 3 2 3 4 5 3 2 3 4 4 5 4 5 4 4 2 5 2 5 3 5 3 2 5 4 4 3 5 4 2 1 5 3 3 2 5 4 4 4 4 3 5 3 3 1 5 5 4 4 3 5 1 3 2 3 5 2 Z 1 5 5 3 2 5 3 31224 32225 32226 32227 32228 32321 32322 32323 4 15 5 22 18 15 23 5 4 3 3 1 5 33334 117 118 119 120 121 122 123 124 125 125 127 128 129 130 131 132 133 134 135 135 137 138 139 140 141 142 143 3 2 3 4 5 3 4 5 3 1 3 5 4 5 1 5 5 5 4 3/16/21 Bruno 3/16/21 Darego 3/16/21 Zimmerman 3/17/21 Bruno 3/17/21 Wagner 3/18/21 Volmann 318/21 Thn 3/18/21 Calder 3/20/21 Wagner 3/20/21 Barry 3/20/21 Zimmerman 3/23/21 Shanahan 3/22/21 Barry 32221 TraI 3/23/21 Bruno 3/23/21 Darego 3/24/21 Zimmerman 32521 Trai 3/26/21 Darego 3/27/21 Calder 3/28/21 Shanahan 3/28/21 Bruno 33/21 Tr 3/29/21 Darego 33/21 Tr 3/30/21 Barry 3/31/21 imman 4 3 5 3 4 3 3 1 2 3 3 3 4 4 5 3 2 5 2 3 33335 33335 33337 33339 33339 33240 33241 33242 33243 33244 34245 34246 34247 34248 34249 34250 34251 34252 3 2 6 3 17 5 16 8 14 20 21 20 22 16 19 3 4 5 3 4 4 3 2 3 4 5 3 3 4 5 3 1 3 5 4 5 3 1 3 5 4 5 1 5 3 5 Tiana Morales is the director of the customer call center for Plerce Software. For the past three months, she has been keeping track of the support calls the center receives, and asks for your help in analyzing the data. Go to the Customer Call Log worksheet, which contains the Calls table where Tiana has been entering support call data. In the Call Type column, she needs to list the type of call corresponding to the code in the Call Code column. In cell ES, enter a formula using the HLOOKUP function to look up the call type according to the call code. Use a structured reference to look up the value in the Call Code column of the Calls table. Retrieve the value in row 2 of the CallTypes table (range A3:F4 in the Survey Questions worksheet) using a structured reference to the entire CallTypes table. Because each call type covers a range of values, find an approximate match. Fill the formula into the range E6:E143, If necessary. Pierce Software 2 Customer Call Log: January March 3 Quest Call Typ Average Total Calls Average Score Call Cod 7 1 17 16 21 18 7 1 4 2 5 4 4 4 3 2 3 4 5 Rep Barry Bruno Calder Darego Shanahan Tnh Volmann Wagner Zimmerman 2 8 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Call It 20191 20154 21170 21145 22103 22246 22137 22234 23228 23215 24117 24140 25224 23126 25210 20189 20126 26157 25201 26149 23222 24241 24152 24195 26242 27143 20155 21171 22227 20177 27196 22213 25200 Da Service Re 1/4/21 Wagner 14/21 Thanh 1/4/21 Darego 1/5/21 Calder 1/5/21 Shanahan 1/6/21 Bruno 1/7/21 Tran 1/7/21 Sanahan 1/7/21 Wagner 1/8/21 Calder 1/9/21 Darego 1/9/21 Bruno 11121 Thnh 1/12/21 Volmann 1/12/21 Barry 1/14/21 Wagner 1/14/21 Shanahan 1/15/21 Bruno 11621 Tran 1/16/21 Calder 1/16/21 Calder 1/16/21 Bruno 117/21 TT 1/17/21 Volmann 1/17/21 Darego 1/17/21 Volmann 1/18/21 Barry 11821 TH 1/18/21 Shanahan 1/20/21 Bruno 1/20/21 Zimmerman 1/20/21 Volmann 1/21/21 Barry 25 9 3 2 12 21 20 22 16 19 3 17 18 16 4 5 3 1 3 2 4 5 5 Customer Survey Scores Quest Quest Quest 2 4 5 2 4 1 3 1 5 5 5 3 4 5 3 3 3 3 1 1 1 2 2 1 3 3 2 5 3 5 3 3 5 5 4 5 2 2 1 1 1 1 4 4 2 3 2 2 3 3 4 4 5 5 5 5 5 3 3 4 4 5 3 3 5 2 2 4 3 3 3 5 3 5 3 3 2 2 2 1 3 3 3 4 3 4 5 4 4 5 5 5 4 4 4 5 2 1 30 31 32 33 34 35 35 37 15 5 22 18 15 4 5 4 38 39 6 3 5 4 5 5 4. 5 5 41 42 4 3 4 4 3 2 5 44 45 45 2 1 4 2 3 5 28144 28147 29215 26128 29136 29182 24126 27147 28117 21178 26242 30119 30194 21223 24132 30171 17 5 16 17 20 10 11 16 5 25 21 13 8 3 2. 5 5 3 3 1 4 49 50 3 1 5 5 3 4 5 3 2 1 3 5 5 4 3 2 4 5 3 2 1 4 3 5 4 3 4 3 1 5 4 14 23161 4 52 53 54 55 56 57 58 59 60 61 1/22/21 Calder 1/22/21 Zimmerman 12321 Thi 1/23/21 Bruno 1/24/21 Darego 1/25/21 Zimmerman 1/26/21 Bruno 1/27/21 Barry 1/28/21 Volmann 128/21 Thanh 1/29/21 Calder 173/21 Thnh 1/29/21 Barry 1/30/21 Zimmerman 1/30/21 Shanahan 1/31/21 Barry 1/31/21 Tham 2/4/21 Barry 2/4/21 Calder 2/4/21 Zimmerman 2/5/21 Thnh 2/5/21 Bruno 2/6/21 Darego 2/7/21 Zimmerman 2/7/21 Bruno 2/7/21 Barry 2/8/21 Calder 2/9/21 Darego 2/9/21 Bruno 2/12/21 Trari 2/12/21 Volmann 2/12/21 Barry 2/14/21 Wagner 2/14/21 Shanahan 2/15/21 Bruno 215/21 ThaI 21521 Thi 2/16/21 Darego 2/17/21 Calder 2/17/21 Shanahan 3 4 5 4 5 3 1 5 5 5 5 4 3 4 3 4 4 3 23162 23163 23170 24165 24166 24167 24168 24169 24170 24171 24172 24173 24174 25175 25176 25177 25178 25179 25180 26181 26182 26183 26184 65 66 4 4 3 2 5 3 4 3 2 3 5 3 2 3 4 5 4 3 4 4 5 21 20 22 16 19 3 17 18 16 4 5 16 17 20 Z 3 4 5 3 3 2 1 68 69 70 71 72 73 74 75 76 77 4 3 1 5 5 3 2 4 4 3 5 4 3 4 4 2 5 4 3 2 3 3 3 5 4 5 5 4 4 2 5 4 2 3 4 5 3 4 5 3 1 3 5 4 5 4 3 4 5 3 2 4 1 3 5 5 3 2 3 3 2 3 4 5 4 5 4 3 2 26185 26185 25187 26188 26189 27190 27191 27192 27193 27194 28195 28195 28197 28198 28199 28200 29201 29202 29203 29204 29205 29206 29207 29208 29209 29210 29211 30212 30213 30214 30215 30216 30217 31218 31219 31220 31221 31222 31231 31224 2/17/21 Bruno 21721 Tnh 2/17/21 Darego 2/18/21 Zimmerman 2/18/21 Bruno 2/18/21 Barry 2/20/21 Volmann 2/3/21 Thn 2/20/21 Calder 2/21/21 Thi 2/21/21 Barry 2/22/21 Zimmerman 2/23/21 Shanahan 2/23/21 Barry 22/21 Tham 2/25/21 Barry 2/26/21 Calder 2/26/21 Zimmerman 2/28/21 Sunahan 2/28/21 Barry 3421 THI 3/4/21 Barry 3/4/21 Calder 3/5/21 Zimmerman 3521 Thi 3/6/21 Bruno 3/6/21 Darego 3/7/21 Zimmerman 3/7/21 Bruno 3/8/21 Barry 33/21 THI 3/9/21 Shanahan 3/10/21 Bruno 3/12/21 Zimmerman 3/12/21 Volmann 3/13/21 Barry 3/14/21 Calder 3/15/21 Zimmerman 3/16/21 Wagner 3/16/21 Bruno 5 25 21 13 8 14 20 21 20 22 16 19 3 17 18 16 4 4 15 5 18 16 4 5 16 17 20 10 11 16 5 25 3 17 18 16 4 4 3 2 3 2 3 4 5 3 2 3 4 4 5 4 5 4 4 2 5 2 5 3 5 3 2 5 4 4 3 5 4 2 1 5 3 3 2 5 4 4 4 4 3 5 3 3 1 5 5 4 4 3 5 1 3 2 3 5 2 Z 1 5 5 3 2 5 3 31224 32225 32226 32227 32228 32321 32322 32323 4 15 5 22 18 15 23 5 4 3 3 1 5 33334 117 118 119 120 121 122 123 124 125 125 127 128 129 130 131 132 133 134 135 135 137 138 139 140 141 142 143 3 2 3 4 5 3 4 5 3 1 3 5 4 5 1 5 5 5 4 3/16/21 Bruno 3/16/21 Darego 3/16/21 Zimmerman 3/17/21 Bruno 3/17/21 Wagner 3/18/21 Volmann 318/21 Thn 3/18/21 Calder 3/20/21 Wagner 3/20/21 Barry 3/20/21 Zimmerman 3/23/21 Shanahan 3/22/21 Barry 32221 TraI 3/23/21 Bruno 3/23/21 Darego 3/24/21 Zimmerman 32521 Trai 3/26/21 Darego 3/27/21 Calder 3/28/21 Shanahan 3/28/21 Bruno 33/21 Tr 3/29/21 Darego 33/21 Tr 3/30/21 Barry 3/31/21 imman 4 3 5 3 4 3 3 1 2 3 3 3 4 4 5 3 2 5 2 3 33335 33335 33337 33339 33339 33240 33241 33242 33243 33244 34245 34246 34247 34248 34249 34250 34251 34252 3 2 6 3 17 5 16 8 14 20 21 20 22 16 19 3 4 5 3 4 4 3 2 3 4 5 3 3 4 5 3 1 3 5 4 5 3 1 3 5 4 5 1 5 3 5
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started