ENGG1811 Lab 12: Excel
After completing this lab, students should be able to do the following in Excel:
• Use named cells, formula, filling cells
• Use pivot table to extract information
This lab has three parts: Parts A to C. You need to show your tutors all three parts.
There is also an online multiple choice question which is worth 1 mark. We suggest that you attempt this question after completing Parts A-C.
Excel in CSE lab environment and workbook
In order to use Excel in the CSE lab environment, you need to do the following.
• First login as usual
• After logging in, click on the Windows 10 icon. This will transfer you to a Microsoft Windows environment.
• Click on the Firefox icon to start the browser. Near the top-left hand corner, you will see a bookmark tab labelled ENGG1811. Click on that to take you to the ENGG1811 website. Navigate to Lab 12.
• You will need to download and complete your work in this workbook. When you click on the link, a dialogue box will pop up, choose the option “Save File” to save the file on the desktop. If you double click on the file icon, Excel will start.
IMPORTANT NOTE: The CSE Microsoft Windows environment does NOT preserve your Excel workbook after logging out. This means if you log out from the Windows environment and log back in again the next time, the Excel file on the desktop will NOT be there anymore. We recommend that after you have completed your lab, you save your Excel notebook on a portable drive or online storage so that you can keep a copy.
Note: You cannot use the CSE vlab system to access the Windows environment. The above instructions only work for a computer located inside a physical CSE lab.
Part A: Freefall
In Week 3’s lecture, you used a formula to determine the speed of a falling object over time using Python. In this exercise, you will do the same using Excel.
The parameters of the problems are:
• Mass of the object m
• Acceleration due to gravity g
• Drag coefficient of air cair
The above three parameter values are given (shown) in the picture below.
The speed v(t) of the falling object at time t is given by the formula:
You are asked to complete your work in the worksheet Freefall. The steps are:
Step 1: There are four yellow cells where the user can specify physical parameters (m and cair), simulation parameter (Δt) and constant (g), see the sample output below. Name each yellow cell so that your formula becomes meaningful. You can do this by first selecting the cell and type a short name in the name box, which is next to the formula bar. Step 2: The simulation parameter Δt is the time increment between successive time points. Your task is to fill in the cells A10 to A51 with the values 0, 0.5, 1, 1.5, .., 20, which are multiples of the time increment. These are the time values which you will use later on to compute the freefall speed. You should not manually type these 41 values in. You should use the fill operation to help you.
Hint: The hint is to fill in the first value A10 with 0. Each cell after the first should be the sum of the previous cell using relative addressing plus the time increment. Note that you had chosen a name for the time increment which is the name for Cell C6 in Step 1, you should use that. Step 3: Determine the frefall speed using the formula and fill in the values in Column B. Don’t forget to use the names that you have used in Step 1. Step 4: Now create a scatter chart to show the speed profile over time. You should add titles to the axes. To do that, click on the chart and a tab on “Chart Design” will appear. The leftmost choice on the tab is “Add Chart Elements” and choose “Axis Title”. Step 5: Try modifying the mass and see how it changes the speed profile. You will see that the chart will be updated automatically.
Part B: Using Excel’s Pivot table
An engineering project management firm has collected a data set containing information on the last 500 completed projects that the firm has supervised. The information includes the
• Project manager
• Estimated Time (weeks)
• Actual Time (weeks)
• Estimated Cost
• Actual Cost
• Category (Commercial Construction, Residential Construction, etc)
• OnTime? – a calculated field that reports “Delayed” if the actual time is more than 5% over the estimated time
• OnBudget? – a calculated field that reports “Over Budget” if the actual cost is more than 2% over the estimated cost
• Prj – a column in the worksheet that simply contains a 1 for each project listed. This column could be used to count the number of projects that meet certain criteria. (Note that, this is not necessary; there are other ways to count projects.)
The worksheet Pivot contains 500 rows of data, one per completed project.
Complete the following 4 tasks. Please put the pivot table for each task in a new worksheet. You can name the worksheets as PT-Task1, PT-Task2 etc.
• Task 1 Which project managers had the most “On Time” Transportation projects?
• Task 2 Which project manager has handled the most Municipal Construction projects?
• Task 3 Which project manager has had the most “As Budgeted” Municipal Construction projects?
• Task 4 Which category has the fewest “Delayed” projects?
If you have difficulty doing this project, you should consult Examples 1 and 2 that we discussed in the lecture. You can check your answers here.
(The above example, the data set and some of the questions are from the chapter titled “Excel Pivot Tables” in the book “Engineering with Excel” by Larsen.)
Part C: Counting heart beats
The worksheet Heart_Beat contains 2 columns of data obtained from a pulse oximeter sensor. Column A contains the times (in seconds) at which the measurements were taken. Column B is the voltage (in Volts) measured at the corresponding time in Column A. For example, at Row number 16, the data were collected at time 1.4s (Cell A16) with a measured voltage of 2.52V (Cell B16).
In the lecture, we counted the number of beats by counting the number of peaks whose value exceeds a threshold. In this exercise, you will use a different method to determine the number of heart beats. We will describe the alternative method in Task 3. First, we do some preliminary processing in Tasks 1 and 2.
Task 1 The first step of data analysis is very often to plot the data. Plot the voltage (y-axis) versus time (x-axis). Choose a line type so that you can see the oscillatory pattern of the heart beats. After plotting the graph, you can see that number of heart beats is fairly large. You can manually count them (if you really want to) but it is easy to make a mistake. What you really want to is to get the computer to count them for you.
Task 2 We will be using a threshold voltage of 3V. We have specified this value in Cell H1. In this task, you should define a named constant called “threshold” for Cell H1. In Task 3, you should use the named constant “threshold” instead of the value of 3. This will make the context clearer.
In this exercise, you will use:
• Number of heart beats = number of times the voltage crosses the 3V threshold and is decreasing
For example, the following plot shows the voltage from 5s to 10s. The red squares in the plot indicates the time instances that the voltage crosses the 3V threshold and is decreasing. These are downward crossing of a threshold.
The first job that you need to do is to translate the condition “the voltage crosses the 3V threshold and is decreasing” into a logical condition that the spreadsheet can use to determine where these crossings have occurred. You may recall that we did something similar in Part C in Lab05 but you were using upward crossing there instead.
After you have derived the logical condition, implement it in the worksheet. Note that you may need to put some intermediate calculations in some columns. It is always a good practice to check what you have derived is correct. Please do so.
After you have satisfied that your logical condition is correct, determine the number of heart beats and place the answer in cell H3.
The answer is 75. If your answer is less than 75, then you might have missed the last beat in counting downward crossings; or you might have counted upward crossings or peaks instead of downward crossings.
At the End of the Lab
You should be able to show your tutor the exercises. You should get some familiarity with using Excel.
Finally, do not forget to complete your online multiple choice question if you have not done it yet and also save your Excel file on a portable drive or some on-line storage.
If you have completed everything, please do not forget to logout. You will need to log out TWICE: Once from the Windows environment and then from your CSE account. To log out from the Windows environment, click on the Windows logo at the bottom left corner, and choose Log Off. After that, double click on the “Log Out” icon.