Lab 1 Handout
pdf
keyboard_arrow_up
School
University of Wisconsin, Madison *
*We aren’t endorsed by this school
Course
461
Subject
Information Systems
Date
Feb 20, 2024
Type
Pages
3
Uploaded by BaronLapwingPerson1669
Lab 1: Practice in Analyzing Data in Excel Excel is a useful spreadsheet tool for organizing and analyzing data. In this lab you will learn to make tables and graphs, use basic functions in Excel, and prepare data for assessments. There are three exercises in this lab. The first will use a short data set so you can practice some skills in a simplified application. The second exercise will have you practice importing data into excel. The third and final exercise will provide you a data set from a study I have been conducting and allow you to practice the skills you have learned on a more complicated data set. Please follow the steps below and include the required information in your lab report as well as submit your excel file in Canvas. NOTE: the online version of excel CANNOT be used to complete this assignment as it has limited capabilities. Please go to the campus software library and download excel via Office 365. Exercise 1: Basic data organization and analysis in Excel In this exercise we will use a made up data sets to paste data into excel, organize data, use basic functions, and graph data. Please follow the steps below and report the required information in your lab report as well as submit your excel file in Canvas. 1.
Enter the data below in Excel (you could enter this data one cell at a time, but we will practice using another approach that proves useful when dealing with large data sets) Time (minutes): 0,2,3,5,7,8,12,16,25,40,45,60 Distance (meters): 0,2,4,6,8,12,20,27,50,65,70,85 a.
First open excel, save your file b.
Save this sheet (bottom of the page) as Exercise 1 c.
Copy the numbers only for time and paste into one cell d.
Select that cell, click on the data tab on the ribbon, select text to columns button e.
Select delimited, click next f.
Select comma, click finish g.
Copy the row of data (select all the data), then paste using the transpose function so that the data is now in a column h.
Give the column the proper heading (do not forget the units) i.
Use this same procedure to enter the distance, you should now what two columns of data for time and distance 2.
Make a table of the min, max, median, and average of both the time and distance (see example layout below, Table 1). Table 1: Layout of data Min Max Median Average Time (Minutes) Distance (Meters) a.
To calculate the min, max, median, and average, use the formulas tab in excel. b.
There are many ways to use formulas in excel, please try these two different methods to see what you prefer (there are many ways to insert functions): i.
Use the formula tab
1.
Click on formula tab; click on insert function; search for function (e.g., type “min” in search box); then select the function you want; select the range of cells you would like the function to use ii.
Enter the function directly; 1.
For the Min formula, enter in a cell “=min(select cells)” will look like “=MIN(A3:A14)” this indicate that you want the calculate the minimum of the values selected in column A from row 3 to row 14. iii.
Complete this for each of min, max, median, average for both time and distance 3.
Graph the data using a scatter plot where the distance is on the y-axis and time is on the x-axis. a.
Select all the cell for the data you want to graph including the headings b.
Select the insert tab at the top of the Excel ribbon c.
Select chart, then scatter d.
Excel will then graph your data e.
If you need to switch the x and y axis, select the graph, right click and select “Select Data”; this allows you to alter the data within the graph f.
Add axis titles (be sure to include units by; selecting the graph; click on chart design tab on the ribbon; use add chart element to add x and y axis titles 4.
Add a regression line in the graph and include the equation for the line a.
Click on a data point within the graph b.
Right click and select “add trendline” c.
Select linear regression trendline, be sure to check the boxes to display the equation of the line and the r-squared value In your lab report please include the table you have made and the graph. Be sure you have proper heading/axis titles with units and that the graph has the equation and r2 value presented. In your lab report be sure to label the figure and tables properly. Exercise 2: Importing data into Excel In this exercise you will learn to import a text data set into Excel. Then you will make a different type of graph using the skills you have learned above. 1.
At the bottom of the same excel file, add an additional sheet, title that sheet Exercise 2. 2.
Import data into Excel by selecting the “Data” tab, then selecting “get data” 3.
Select from file; select the file provided in Canvas titled “plastic life cycle data” 4.
Once you have the data, make a pie chart 5.
Make sure the graph includes a legend that identifies each pie section as well as displays the percent of each pie piece (you can do this by right clicking and selecting format add data labels, after you have done this right click again and select format data labels, select percentage and remove values) In your lab report answer the following question: Where does the largest amount of plastic end up in the plastic life cycle? Be sure to direct the reader to your figure when you answer the question.
Exercise 3: Applying developed Excel skills to an actual data set Emissions of hydrogen sulfide (H
2
S) from manure systems have recently resulted in a number of deaths in Wisconsin when the manure system is agitated. The Occupational Safety and Health Administration (OSHA) indicates a ceiling concentration of 20 ppm for H
2
S, as concentrations above this level can become dangerous to human health. To provide more guidance to producers, we measured the H
2
S concentrations at a manure agitation site in addition to measuring a variety of environmental conditions to determine those that lead to higher risk from H
2
S. Please use the data provided in Canvas to answer the following questions. 1.
Did the concentrations of H
2
S ever exceed the OSHA acceptable ceiling concentration? How many times (recommended you sort the H
2
S data so you can easily count the number of times the concentration was at or exceeded 20 ppm; search google or use the help function to learn how to sort data)? 2.
What was the maximum H
2
S concentration measured from this monitor on this day? Use the sorted data. Ater you find your answer I would recommend undoing this sort for the next step. 3.
Many times, data from the field can be difficult to handle. In this case, the time interval for the temperature data is different from the time interval for the H
2
S data. Graph the H
2
S data vs time as well as the temperature time in the same graph. a.
First convert the temperature from degrees C to degrees F (as you will need it in degrees F for comparison later). This is done most easily by selecting the cell next to the temp C data and entering a conversion formula. i.
Degree F = (Degrees C *9/5) +32 (note you will type this in excel as “=([cell with degree data]*9/5)+32” example =(A35*9/5)+32 remember that your cell may change depending upon how your excel file is set up ii.
Now drag this formula down so that you have converted every point. b.
Select the temperature and time data, graph it using some type of scatter plot graph. c.
One you have that graph, you can select the graph, right click on “select data” d.
You can select “add” you can now add the H
2
S data but specify the x axis data and y axis data separately so that you can have both of these graphed on the same graph even though they have different time steps for the x axis. Now review the data. In laboratory studies, it is suggested that higher temperatures increase H
2
S concentrations, Figure 1. Does this field data suggest that this trend may also be true in the real world? Be sure to direct the reader to your graphed data. Figure 1: Temp impacts on H
2
S production in laboratory conditions (Andriamanohiarisoamanana et al. 2015).
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help