Lab Sorting and Filtering Data using Excel - Lists

ENGR1121 Z – Computing in Mechanical Engineering Lab Sorting and Filtering Data using Excel - Lists Lab due: end of lab Total points: 20 Labs are to be individual work. You may use the textbook, Excel’s help, and your notes. Lab work will be graded during lab time and working results must be shown to the lab instructor by the end of the scheduled lab time. All Excel Spreadsheets must be completed and submitted in accordance with the Computing Assignment Format. Submit an electronic copy of your lab work to the appropriate Dropbox Folder in GSU’s Folio by the end of lab. No hard copy needed unless specifically requested by the instructor. Grading Each problem will be graded according to: problem design (identifying inputs/givens, process, and outputs; algorithm) and program style (headers and comments, variable names, proper formatted input and output including units) 25%, and program correctness 75%. Lab problems not completed by the end of lab will receive a grade of zero. Lab Work Create an Excel Spreadsheet to do each of the following problems from the textbook. Sorting Data in Excel: 1. (10 points) Problem 8.3. • Create a separate worksheet for each part. • Appropriately name each worksheet. • Create a print out of the workbook (all worksheets) o Scale the data to fit on one page o Center the data horizontally on each page o Customize the header to include the file name, worksheet name and date o Customize the footer to include the page number and the total number of pages o Staple all 5 pages together and submit along with the electronic copy. Problem 8.3: A group of students has measured the time required for a series of droplets to vaporize after being dropped onto a very hot surface. The students have collected the following data: Note that the list contains six sets of data, where each data set corresponds to a particular fluid and specified initial drop size. The third column (temperature difference) represents the difference between the temperature of the hot surface and the boiling point temperature of the fluid. (Can you figure out why it takes so long for the droplets to vaporize?) Enter the data into an Excel worksheet. Be sure to fill all of the cells within the list, even if that requires repeating information from other cells. Then carry out the following sorting operations: (a) Sort the data alphabetically with respect to the fluid (i.e., rearrange the list so that the data for benzene is followed by the data for ethyl alcohol, and then for water). (b) Restore the original list. Then sort each set of data in the order of descending temperature differences, maintaining the descending drop sizes. (c) Restore the original list. Then sort the data sets in the order of ascending drop size, maintaining the ascending temperature differences for each data set (i.e., each drop size). (d) Restore the original list. Then sort the data alphabetically with respect to the fluid. For each fluid, sort the data sets in the order of ascending drop size. For each drop size, sort the data in the order of descending temperature differences. 1 Instructor: Dr. Priya Goeser ENGR1121 Z – Computing in Mechanical Engineering Filtering Data in Excel: 2. (10 points) Problem 8.6. • Create a separate worksheet for each part. • Appropriately name each worksheet. Problem 8.6: Carry out the following filtering operations on the list of droplet vaporization times given in Problem 8.3. (a) List the vaporization times with their corresponding drop sizes for ethyl alcohol. (b) List the fluid and vaporization times for those droplets whose initial size is 0.0177 mL. (c) Determine which droplets vaporize in 10 seconds. (d) Determine which droplets vaporize in between 10 and 18 seconds. (e) Determine which droplets vaporize in 12 seconds or less. (f) Determine which droplets vaporize in more than 75 seconds. 2 Instructor: Dr. Priya Goeser