New version page

# PSU MET 107 - Lookup Functions

Pages: 2
Documents in this Course
Unformatted text preview:

107_H20-LookupTablesandKeys.docx Page 1 of 2 11/8/2012 MET 107 Homework 20 – Lookup Functions A key is used to transmit torque from a rotating shaft to an element (gear, sheave or sprocket) attached to the shaft. You are to build the input portion of a worksheet that will ultimately design a key for a given shaft diameter, torque and hub length. Keys can be square or rectangular or a variety of other specialized shapes. Your worksheet will only work for square and rectangular keys. The keys can be made from one of three possible materials. The general key sizes and materials are shown in the figure below and can be found in a text file that can be downloaded from the web page. The input area will appear as follows: Use validation in cells C11:D11 and C12:D12 to allow the user to choose square or rectangular shapes and the materials from the provided list. Based on the input in cell C12, the worksheet should automatically look up the yield strength (Sy) of the material and display that value in cell C14. The design shear stress is computed as half of the yield strength divided by the design factor (C15). The design bearing stress is the yield strength divided by the design factor in C15. Use a lookup function (VLOOKUP) to find the material strengths. Based on the selected shape (C11) and the shaft diameter (C19) the worksheet should look up the width and height of the first trial key. Use the MATCH and INDEX functions to do this for the width and the height. The width and height should eventually be concatenated together in a single text string in C23. The table of sizes must be sorted as required by the MATCH function. When using the Vlookup, Match and Index function, these should be written as singular functions in separate cells off to the side, or below the main portion of the spreadsheet for easy checking/debugging purposes. This cell should automatically update indicating if the Key Shape was selected as Square or Rectangular107_H20-LookupTablesandKeys.docx Page 2 of 2 11/8/2012 Remember, you are not writing this just for yourself, but for others that might have to check or modify this spreadsheet. Print the output for the following parameters (using the Grid/Header macro): Shaft Diameter = 1.75 in, 1020 CDS Square Design Factor: 3 Shaft Diameter = 2.50 in, 1040 CDS Square Design Factor: 3 Shaft Diameter = 1.00 in, 1050 CDS Rectangular Design Factor: 3 Shaft Diameter = 1.375 in, 1020 CDS Rectangular Design Factor: 3 Print your cell formulas using the Copy Cell Formulas Macro – only one version of the cell formulas are required (Case 1). Do hand calculations for the four test cases defined above. Note: the astute student would do the hand calculations BEFORE producing spreadsheet formulas for trying to find a Match etc., so as to be able to quickly ascertain if their solution was correct. At a minimum determine what the key size should be given a Shaft Diameter and Key Shape. You can do the stress calculations later. Staple the sheets in the following order: Grid/Header for dia = 1.75, dia = 2.50, dia = 1.00, dia = 1.375 Cell formulas for Case 1 Hand calculations for the four test

View Full Document Unlocking...