METBD 050 Homework 16 – Lookup Tables 1. We want to be able to look up the size of a standard bar from a table of values and return the area, section modulus, moment of inertia and weight per foot for the bar. Using the area, a specified yield strength and design factor, we want to calculate the safe axial load for the bar. • The standard bar sizes are available in a text file from the web page. • Open the list of bar sizes from the text file in Excel. Make the table of necessary values for the bar sizes as shown here: Use the following formulas: Area, A = width * height Section Modulus, S = width * height2 / 6 Moment of Inertia, I = width * height3 / 12 Weight per foot = width * height * 12 * .28 • Note that the values for width and height are keyed into the table by you. These values are used with the cell formulas for area, section modulus, moment of inertia and weight per foot calculations. Write one cell formula for each column and copy them down. • Copy the first column to a new location to serve as the list for using validation. • Sort your data table by the first column in ascending order so the VLOOKUP function works properly. Your worksheet should be similar to this: NOTE: Allowable load = Area * Design Stress The first column of your data table must be in ASCENDING ORDER to use the VLOOKUP function properly. Bar Size width Height Area Section modulusMoment of InertiaWeight/Foot1/4 x 1/4 0.25 0.25 0.0625 0.0026 0.0003 0.21001/4 x 1/2 0.25 0.50 0.1250 0.0104 0.0026 0.42001/4 x 3/4 0.25 0.75 0.1875 0.0234 0.0088 0.63001/4 x 1 0.25 1.00 0.2500 0.0417 0.0208 0.8400Use cell validation to limit the selection of bar sizes to the sizes in your table. Your validation list should be a separate list from your data table. This list should be in the order provided. Use the VLOOKUP function to find these values from your data table (array). Design stress = Sy/Design Factor• Print the worksheet and the cell formulas. Be sure to show the gridlines and row and column headers when printing. 2. In this problem, the user should be able to specify the yield strength and design factor for the material as well an an applied load. The worksheet should then look up the required size bar to use. • Find the Required Area which is the smallest area that can safely carry the load. This number is the quotient of the Load / Design Stress. • Use the MATCH function to find the required area or the next largest area from the area column of the data table. The Lookup_array is the column containing the areas. The Match_type is -1 in this case since you want the exact area or the next largest area. For this case, the data must be sorted by area in DESCENDING order. • Use the INDEX function to determine which bar should be used. This is the bar size that corresponds to the row determined by the MATCH function above. The syntax of the function is =INDEX(array, row_number, column_number) The array can be one column (G) or two columns (G:H) if you like. The row number is the row number found with the MATCH function above. Choose the column number based on the size of the array specified. • The figure below shows what your worksheet may look like. • Print the worksheet and the cell formulas. Print the gridlines and row and column headers. • Staple the sheets in order: 1, cell formulas, 2, cell
View Full Document
Unlocking...