********************. All problems must be solved using Excel formulas ****************

Question Set 1. You are in charge of quality control for computer keyboards at Dell. You have data on twenty-five batches of keyboards, tracking five types of keyboard defects: key brightness, dusting, scissor mechanics, dead connections, and sticky keys. These data are given in the table below. 1. For each defect type, find the average number of defects per batch. So, you should have an average defect rate for displays, another for color, and so on (2pts) 2. For each batch, find the total number of defects (the sum of all five types). So, you should have one number for batch 1, another for batch 2, and so on. (2pts) 3. Sort the five columns of defects by descending average defect rate. The lowest rate should be on the right. (4pts) 4. Sort the batches by descending total defects. The batch with the highest total should be at the top. This will not affect the column sorting from the part 3 above. (4pts) 5. Create a Pareto chart showing the average defect rate for each of the five defect types. This will be a column chart, in descending left-to-right order, with each column and the axes labeled. (Spts) Dusting Sticky Keyse 1 02 0 00 12 le 62 12 0 0 2e 04 02 32 1 22 1 0 14 14 02 le 04 le 1] 12 12 Defects Batch Key Brightness Dead Connections Butterfly Mechanics 1e 5e 22 02 2e 14 32 32 34 1 40 02 22 22 02 52 le 2e 14 be 02 12 za 22 22 14 8 12 0 9 0 02 10 02 22 0 2e 11 22 12 0 12 12 02 2 13 02 42 142 2 02 15 02 1 16 1 1 172 1e 02 02 18 22 192 02 12 32 34 20- 12 0 1 212 04 02 22 02 12 02 23 02 1 34 12 242 0 0 1 25 2 0 1 0 03 22 12 1 1 167 2e 1 1 04 2 02 LIE 2 02 32 1.1. 0 0 0 12 12 32 T.1 00 3 22 2 02 22 10 3