Introduction to Microsoft Office

Important: Please note that you must register on the EG1004 website before performing the lab. The information on the lab PCs cannot be accessed outside of the lab rooms. Email copies of all the files created on the lab PCs to a personal account and share it with all members of your group.

Objective

The objective of this lab is to solve engineering problems using Microsoft Excel. The first case study will evaluate the acceleration due to gravity of a free-falling body. The second case study will analyze historical trends for energy consumption in the United States.

Overview

Data collection, processing, and analysis is an integral part of studying and working in engineering. Spreadsheet software applications like Microsoft Excel (part of the Microsoft Office suite) allow engineers to perform these tasks efficiently. The software can be used to collect numerical and text data, visualize the data using tables and graphs, and perform calculations using various functions. Effective use of these tools allows engineers to identify relationships between variables in an experiment, observe trends, and more.

Materials and Equipment

• A lab PC
• Microsoft Office suite

Procedure

1. Case Study: Free-fall

In this exercise, common Excel equations and tools will be explored in the context of the following problem.

A ball is dropped from the roof of the Bern Dibner Library building. The distance between the ball and the ground is measured and recorded as a function of time with a Lidar scanner (Table 1).

The change in distance over time, known as instantaneous velocity, average velocity, and acceleration due to gravity of the ball will be calculated using Excel. The standard abbreviation for the units will be used for all calculations.

Table 1: Data from Lidar Scanner

Time (s)	Distance (m)
0.00	19.62
0.20	19.42
0.40	18.83
0.60	17.85
0.80	16.48
1.00	14.71
1.20	12.55
1.40	10.00
1.60	7.06
1.80	3.72
2.00	0.00

1. Open the Microsoft Excel Template containing Table 1.
2. To calculate the instantaneous velocity at each point in time, a formula (1) is used. in (1), v is the instantaneous velocity, $x_{2}$ is the second distance point, $x_{1}$ is the first distance point, $t_{2}$is the time corresponding to the second distance point, and $t_{1}$ is the time corresponding to the first distance point.

   $v={\frac {x_{2}-x_{1}}{t_{2}-t_{1}}}\,$

   (1)

   1. Assuming the initial velocity is zero, enter 0 into cell C2. In cell C3, enter the formula =(B3 - B2)/(A3 - A2). The velocity will be negative because the ball is traveling downwards.
3. Reselect cell C3. Click and drag from the bottom-right corner of the cell (a black addition sign should appear) down to cell C12. This will copy the formula for the other cells in the column using the data in column A and column B (A3, A4, B3, B4 for cell C4, and so on).
4. To calculate the average velocity of the ball across the 11 data points, select cell D2. Enter the formula =AVERAGE(C2:C12).
5. Create a scatter plot for the relationship between velocity and time.
   1. On the Excel ribbon, click on the Insert tab, and click Scatter on the Scatter option under the Charts group. This should create a blank graph, as shown in Figure 1. If data was automatically inputted, right-click on the graph and select Select Data. Inside the Legend entries (Series) table, click on each entry and click the – button to delete.

      

      Figure 1: Inserting a Scatter Plot

   2. Right-click on the empty graph and select Select Data.
   3. Under the Legends Entries(Series) tab, click on the Add option, as shown in Figure 2. A new window called Edit Series will appear.

      

      Figure 2: Utilizing the Select Data Source Window

   4. In the Series X values text box, delete the {1} and highlight cells A2 to A12. In the "Series Y" values text box, delete the {1} and highlight cells C2 to C12. Click OK to generate the scatter plot.
   5. Add axis titles to the graph by clicking Axis Titles in the dropdown menu at the top right of the graph. Rename the axis title titles by clicking on the vertical and horizontal titles.
      1. Directions for Mac Users: Select Chart Design from the tools bar on the top of the Excel window. Click Add Chart Element and select Axis Titles from the dropdown. Be sure to add in both the horizontal and vertical axis title. See Figure 3 below to locate the Axis Title element.

         

         Figure 3: Add Chart Element from the Excel Ribbon

6. Acceleration is the rate of change of the velocity, or the slope of the velocity vs. time graph. For roughly linear data on Microsoft Excel, a line of best fit is used to approximate the relationship between the data points in (2).

   $y=mx+b$

   (2)

   1. In the dropdown menu at the top right of the graph, hover over Trendline and click on the black arrow. Select More Options.
      1. Directions for Mac Users: Select Chart Design from the tools bar on the top of the Excel window. Click Add Chart Element and hover over Trendline from the dropdown and select Linear. See Figure 4 below to locate the Trendline element.

         

         Figure 4: Add Trendline from the Excel Ribbon

   2. Under the Format Trendline window, check off Display Equation on chart.
      1. Directions for Mac Users: On the side popup tab, click Trendline Options, and on the bottom check off Display Equation on chart. See Figure 5 below to display the trendline equation.

         

         Figure 5: Display Equation on chart through Trendline Options

   3. Check the slope of the graph and compare it to the acceleration due to gravity in New York, -9.802 ${\frac {m}{s^{2}}}$. Discuss and explain the discrepancy.

2. Case Study: Historical Energy Statistics

This exercise provides an overview of creating line graphs and pie charts in Excel to analyze the historical trends of renewable energy consumption in the United States.

The statistical data presented in this exercise is derived from the U.S. Energy Information Administration (EIA). Using the data, four graphs will be constructed: a line graph showing the yearly trend for hydroelectric power, geothermal power, solar power, wind power, and biomass power, and a pie chart representing the 2019 distribution in renewable energy. These graphs will be appended with data regarding fossil fuel energy consumption.

Modeling a Yearly Trend

1. Download the Excel file containing the data for the yearly energy consumption of various renewable energy sources from 1949 to 2019.
2. The line graph will contain multiple lines that represent the different renewable energy sources. The procedure to graph the renewable energy consumption for hydroelectric power is provided. Complete a similar procedure for the remaining renewable energy sources.
   1. On the Excel ribbon, click on the Insert tab, and click Scatter with Straight Line on the Scatter option under the Charts group. This should create a blank line graph, as shown in Figure 6. If data was automatically inputted, right-click on the graph and select Select Data. Inside the Legend entries (Series) table, click on each entry and click the – button to delete.
      

      Figure 6: Inserting a Line Graph
   2. Right-click on the empty graph and choose the Select Data option. A new window will appear where the graphed data can be inputted.
   3. Under the Legends Entries (Series) tab, click on the “Add” option, as shown in Figure 7. A new window called Edit Series will appear. For Mac users, refer to Figure 8.
      

      Figure 7: Utilizing the Select Data Source Window
      1. Directions for Mac Users:

         

         Figure 8: Utilizing the “Select Data Source” Window on Mac

   4. In the Series N textbox, type in the name of the power source, Hydroelectric Power. Refer to Figure 8 above to locate the name textbox.
   5. In the Series X values textbox, highlight the years in column A from 1949 to 2019.
   6. In the Series Y values textbox, highlight the total energy consumption from hydroelectric power in column B that corresponds to 1949 to 2019. The Edit Series should appear similar to Figure 9.
      

      Figure 9: Utilizing the Edit Series Window
   7. Click OK to graph the Hydroelectric Power data.
3. Next the data for the remaining renewable energy sources must be added to the same graph. To add the data, repeat steps 2b to 2g for each renewable energy source.
4. Rename the axes and titles for the energy source in the graph. Include a legend by checking off Legend by going to Chart Design in the Excel ribbon and clicking Add Chart Element. See Figure 10 below on how to navigate to Legend.

   

   Figure 10: Add Legend to Chart

Modeling the 2019 Distribution

1. To visualize the distribution of the various renewable energy sources in recent years, a pie chart will be created based on the 2019 year. This part will continue with the raw data obtained from the previous part.
2. On the Excel ribbon, click on the Insert tab, and click 2-D Pie on the Pie Chart option under the Charts group. This should create a blank line graph, as shown in Figure 11.
   

   Figure 11: Inserting a Pie Chart
3. Right-click on the empty graph and choose the Select Data option. A new window will appear where the graphed data can be inputted.
4. Under the Legends Entries (Series) tab, click on the Add option. A new window called Edit Series will appear.
5. In the Series Values textbox, highlight each renewable energy sources’ total energy consumption for 2019. Completing this will automatically create five labels under the Horizontal (Category) Axis Labels tab, as shown in Figure 12 (Mac users see figure 12.1).

   

   Figure 12: Horizontal Axis Labels

   

   Figure 12.1: Horizontal Axis Labels for Mac Users

6. Select the row labeled 1 and click on the Edit option under Horizontal (Category) Axis Labels tab. In the Axis-label range textbox, select the names of each renewable energy source (B2:F2) type in Hydroelectric Power and select OK. Repeat this step with the following rows with their corresponding energy sources.
   1. Directions for Mac Users: Under Select Data Source, select the names of each renewable energy source (B2:F2) type inside Horizontal (Category) axis labels and click OK. See Figure 13 below for reference.

      

      Figure 13: Adding Horizontal (Category) Axis Labels

7. Rename the title appropriately.

Fossil Fuel Energy Modeling

1. Copy the data in the Fossil Fuel data sheet (located at the bottom left of the Excel spreadsheet) to the main data sheet. Ensure that the data on the main sheet is not overwritten by the Fossil Fuel data.
2. Following the same steps as the Modeling a Yearly Trend procedure and Modeling a 2019 Distribution, create a new scatter plot and a new pie chart representing both renewable energy sources and fossil fuel energy sources.
   1. The renewable energy sources scatter plot should have five series: Hydroelectric, Geothermal, Solar, Wind, & Biomass.
   2. The fossil fuel energy sources should have three series: Coal, Natural Gas, & Petroleum.
3. There should be eight series.

At the end of this case study there should be four graphs:

• A scatter plot displaying the renewable energy sources
• A pie chart showing the 2019 distribution in renewable energy
• A scatter plot displaying both renewable energy sources and fossil fuel energy sources
• A pie chart showing the 2019 distribution in renewable energy sources and fossil fuel energy sources

Assignment

The Excel files must be zipped in a folder and submitted to the EG1004 website by 11:59 PM the night before your next lab. There is no individual lab report or team presentation for this part of Lab 1.