107_H13_TextFunctions.docx Page 1 of 3 3/3/2014 MET 107 Homework 13 – More (Character Handling and Statistical) Functions 1. Explore the CHAR function in Excel. Do the following in the order given: a. Create 5 columns of numbers using 1 to 51 in the first column, 52 to 102 in the next, etc. Skip a column between the numbered columns. Use these numbers as the argument for 255 CHAR commands. b. Check out the resulting characters. c. Adjust the column width for your 10 columns to 5. Output from your spreadsheet should be similar to the following: Use the Grid and Headers macro to print your sheet of numbers and characters. You don’t have to document the cell formulas for this problem. 2. In the 107_H13_StartFile.xls for Homework 13, is a list of names. a. Sort the names in alphabetical order. Use text functions to get the first name of each individual in column B and their last names in column C after determining the location of the comma and the lengths of the first and last name. b. In column D, concatenate (&) the data from columns B and C to write the names with the first name first. You must use the Left, Right, Len and Find functions. You must utilize additional columns for “intermediate” calculations, ie Columns E, F, G and H are Total Length, Location of Comma, Length of First Name and Length of Last Name. Results are a function of these intermediate calculations, not vice versa. Your spreadsheet must automatically update the information in columns B, C and D if names in column A are modified. Note that the information in the First Name column does NOT have a space in front of it. Output from your spreadsheet should be similar to the following (note text justification): c. Change the widths of the columns to make all the data visible. d. Print the worksheet using the Grid and Headers macro. e. Print the cell formulas using the Copy Cell Formulas macro. Before printing select the File tab, under Print select the Page Setup button (left column on bottom), on the Page tab, change the “Fit to” be 1 page wide by 2 tall, otherwise it will be un-readable. Sort107_H13_TextFunctions.docx Page 2 of 3 3/3/2014 3. Exam Grade Evaluation: Download the file “107_H13_ExamScores.txt” from the webpage. This is a text file that contains a certain number of exam scores for a computer course given at a large university. a. In cell E10, use the COUNT function to determine the number of scores. b. In cells E12 through E15, find the quantities listed using the AVERAGE, MEDIAN, MAX and MIN functions. Be sure to use the range name in these functions. c. In cells E18:E22, write the cell formulas necessary to determine the number of A’s, B’s, C’s, D’s, and F’s that were earned. Remember, for MET courses, F is <65%. You must use the COUNTIF function. d. In cell F18, write a cell formula to determine the percentage of the class that earned an A. Copy this formula down the column to find the percentages for the other grades. Your spreadsheet should appear similar to the following (Average and Percentage to 1 decimal place) e. To print your spreadsheet, first execute the Grid and Header macro. Do not print at this time. Now, select the cells between A1 and H23 and select Print Area under the Page Layout tab. Do a print preview to see if only the main portion of the spreadsheet is going to print (not the data set). Now print your sheet. f. Print (Document) your cell formulas using the Copy Cell Formulas macro. IMPORTANT: Make sure that if you name your tabs, there are no spaces in the name.107_H13_TextFunctions.docx Page 3 of 3 3/3/2014 4. Axial Bar: Create the cell formulas necessary to determine the diameter of an axially loaded bar given the axial load and the allowable stress of the bar material. The transformation for this problem is shown below: Your spreadsheet should initially appear as follows: a. Change the force to 11500 pounds and the allowable stress to 22000 psi. b. Print your worksheet after running the Grid and Header macro. c. Print (Document) your cell formulas after running the Copy Cell Formulas macro. You should have 8 sheets of documentation total (Step 2e generates 2 sheets). 1. Get Allowable Stress (Sallow) and axial force (F). 2. Determine the required area (A) of the bar: A = F/Sallow 3. Determine the minimum diameter (Dmin): Dmin = SQRT( 4 * A /  ) 4. Select the next largest standard bar diameter (D) using CEILING function to round up to the next 1/16” increment. 5. Determine the actual stress, (Sact) Sact = F / (  * D ^ 2 / 4) 6.