Book: Succeeding in Business with Microsoft Excel 2013
Chapter 2 page 121
Steps To Success: Level 2
The QC group wants to extend the QC data analysis to skibinding. The group wants to determine the rankings and examine thetail ends of the data (both high and low values) and the number ofbindings that will be rejected. The latest data being analyzed islocated in a workbook named Binding2.xlsx. Your task is to completethis analysis.
The criteria for rejecting bindings are based on the followingtwo quality tests:
Minimum pressure at which the binding mechanism willautomatically unlock, measured in pounds (lbs). The higher thepressure at which the binding unlocks, the better the binding.Bindings that unlock at less than 10 lbs of pressure arerejected.
Temperature at which the materials of construction fail, giventhat cold temperatures affect the binding performance, measured indegrees Celsius (°C). The lower the failure temperature, the betterthe ski binding. The maximum allowable failure temperature is–59°C.
Complete the following:
1. Open the workbook named Binding2.xlsx located in the Chapter2 folder, and then save the file as QC Binding Data Analysis 2.xlsx.
2. Write a formula in cell G3 that can be copied down the columnto calculate the relative rankings of each data element for thepressure test. Set up your rankings so that the best ski is given aranking of 1 (based on the rejection criteria described in theproblem). Include an appropriate column heading in cell G2. In asimilar way, set up the rankings for the Temperature test in columnH so that the best ski is given a ranking of 1.
3. In cell D49, write a formula to determine the number of skibindings that failed to meet the minimum pressure requirements forquality testing. Write the formula so that if any of the datainputs change later, this value will be automatically updated. Foreasier viewing, use the split feature to divide the worksheet intotwo sections, with the top containing the title and columnheadings.
4. In cell E49, write a formula to determine the number of skibindings that failed to meet the minimum temperature requirementsfor quality testing. Write the formula so that if any of the datainputs change later, this value will be automatically updated.
5. In cell F49, write a formula to determine the total value ofthe ski bindings that have been rejected. Assume that no bindinghas failed both tests. Format this cell using the Currency Style.Include the label Rejection Summary in cell A49.
6. Arrange the worksheet to freeze panes so that rows 1 and 2are always visible.
7. Set up a separate worksheet named Tail in the workbook, andthen create a table on the Tail worksheet listing the five highestand lowest values, as shown in Table 2.11. Be certain to include atitle on this new worksheet and format the worksheet so that it iseasy to read.
8. Write a formula to calculate the highest pressure value sothat it will be aut omatically updated if the binding data is laterrevised. Write the formula so that it can be copied down the columnto determine the second highest pressure, the third highest pressure, and so on, and across the row to determine the highesttemperature.
9. Write a formula to calculate the lowest pressure value sothat it will be automatically updated if the binding data is laterrevised. Write the formula so that it can be copied down the columnto determine the second lowest pressure, the third lowest pressure,and so on, and across the row to determine the lowest temperaturevalue.
10. Save and close the QC Binding Data Analysis 2.xlsxworkbook.