Difference between revisions of "Introduction to Microsoft Office"
(39 intermediate revisions by 5 users not shown) | |||
Line 1: | Line 1: | ||
<span style="color: red;">Important: Please note that you must register on the [http://eg.poly.edu | <span style="color: red;">Important: Please note that you must register on the [http://eg.poly.edu EG1004 website] before performing this lab.</span> 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 = | = Objective = | ||
The objective of this lab is to | 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 = | = 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 = | = Materials and Equipment = | ||
Line 24: | Line 12: | ||
= Procedure = | = Procedure = | ||
== 1. Case Study: Free-fall == | == 1. Case Study: Free-fall == | ||
In this exercise, common Excel equations and tools will be explored | 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 | 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 (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. | |||
<center> | <center> | ||
{| class="wikitable" | {| class="wikitable" | ||
|+ Table 1: | |+ Table 1: Distance between the ball and the ground at specific times. | ||
!Time (s)!!Distance (m) | !Time (s)!!Distance (m) | ||
|- | |- | ||
|<center>0</center>||<center>19. | |<center>0.00</center>||<center>19.62</center> | ||
|- | |- | ||
|<center>0. | |<center>0.20</center>||<center>19.42</center> | ||
|- | |- | ||
|<center>0. | |<center>0.40</center>||<center>18.83</center> | ||
|- | |- | ||
|<center>0. | |<center>0.60</center>||<center>17.85</center> | ||
|- | |- | ||
|<center>0. | |<center>0.80</center>||<center>16.48</center> | ||
|- | |- | ||
|<center>1. | |<center>1.00</center>||<center>14.71</center> | ||
|- | |- | ||
|<center>1. | |<center>1.20</center>||<center>12.55</center> | ||
|- | |- | ||
|<center>1. | |<center>1.40</center>||<center>10.00</center> | ||
|- | |- | ||
|<center>1. | |<center>1.60</center>||<center>7.06</center> | ||
|- | |- | ||
|<center>1. | |<center>1.80</center>||<center>3.72</center> | ||
|- | |- | ||
|<center>2. | |<center>2.00</center>||<center>0.00</center> | ||
|}</center> | |}</center> | ||
#Open the [[Media: Microsoft_Excel_Exercise_.xlsx|Microsoft Excel Template]] containing Table 1. | #Open the [[Media: Microsoft_Excel_Exercise_.xlsx|Microsoft Excel Template]] containing Table 1. | ||
# To calculate the instantaneous velocity at each point in time, Formula 1, as listed below is used. In (1), <i>v</i> is the instantaneous velocity, <i><math>x_2</math></i> is the second distance point, <i><math>x_1</math></i> is the first distance point, <i><math>t_2</math></i>is the time corresponding to the second distance point, and <i><math>t_1</math></i> is the time corresponding to the first distance point. | |||
#:<center><math>v = \frac{x_2 - x_1}{t_2 - t_1}\,</math></center> <p style="text-align:right">(1)</p> | #:<center><math>v = \frac{x_2 - x_1}{t_2 - t_1}\,</math></center> <p style="text-align:right">(1)</p> | ||
##Assuming the initial velocity is zero, '''enter 0 into cell C2.''' In cell C3, enter the formula <b>=(B3 - B2)/(A3 - A2)</b>. The velocity will be negative because the ball is traveling downwards. | |||
#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). | |||
#Reselect cell C3. Click and drag from the bottom-right corner of the cell 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). | #To calculate the average velocity of the ball across the 11 data points, select cell D2. Enter the formula <b>=AVERAGE(C2:C12)</b>. | ||
#To calculate the average velocity of the ball across the | #Create a scatter plot for the relationship between velocity and time. | ||
# | ##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 click '''Select Data'''. Inside the Legend '''entries (Series) table''', click on each entry and click the '''– button''' to delete.[[Image:Inserting_a_Scatter_Plot.png|500px|thumb|center|Figure 1: Inserting a Scatter Plot]] | ||
##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. | ##Right-click on the empty graph and click '''Select Data'''. | ||
##Under the '''Legends Entries(Series)''' tab, click on the '''Add option''', as shown in Figure 2. A new window called '''Edit Series''' will appear.[[Image:Lab_Select_Office.png|500px|thumb|center|Figure 2: Utilizing the Select Data Source Window]] | |||
##Right-click on the empty graph and | ##In the '''Series X values''' text box, delete the {1} and highlight cells A2 to A12. In the '''"Series Y"''' text box, delete the {1} and highlight cells C2 to C12. Click '''OK''' to generate the scatter plot. | ||
##Under the '''Legends Entries(Series)''' tab, click on the '''Add option''', as shown in Figure 2. A new window called '''Edit Series''' will appear. | ##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. [[Image:Add Axis Titles to the Graph.gif|600px|thumb|center|Figure 3: Add Axis Titles to the Graph]] | ||
# 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 of the data points in (2). | |||
##In the '''Series X''' | |||
##Add axis titles to the graph by clicking '''Axis Titles''' in the dropdown menu at the top right of the graph. Rename the axis and | |||
#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 | |||
#:<center><math>y = mx + b</math></center> <p style="text-align:right">(2)</p> | #:<center><math>y = mx + b</math></center> <p style="text-align:right">(2)</p> | ||
##In the dropdown menu at the top right of the graph, hover over '''Trendline''' and click on the black arrow. Select '''More Options'''. | ##In the dropdown menu at the top right of the graph, hover over '''Trendline''' and click on the black arrow. Select '''More Options'''. | ||
##Under the '''Format Trendline''' window, check off '''Display Equation''' on chart. | ##Under the '''Format Trendline''' window, check off '''Display Equation''' on chart and '''Display R-Squared Value''' on chart. The R-squared value is a measurement of how well the equation fits the data ranging from 0 to 1. [[Image:Add a trendline.gif|650px|thumb|center|Figure 4: Add a Trendline and Its Equation to the Graph]] | ||
##Check the slope of the graph and compare it to the acceleration due to gravity in New York, -9.802 <math>\frac{m}{s^2}</math>. Discuss | ##Check the slope of the graph and compare it to the acceleration due to gravity in New York, -9.802 <math>\frac{m}{s^2}</math>. Discuss possible reasons for the discrepancy. | ||
== 2. Case Study: Historical Energy Statistics == | == 2. Case Study: Historical Energy Statistics == | ||
This exercise provides an overview of creating line graphs and pie charts in Excel | 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 | The statistical data presented in this exercise is taken 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 2022 distribution in renewable energy. These graphs will be appended with data regarding fossil fuel energy consumption. | ||
=== Modeling | === Modeling an Annual Trend === | ||
#Download the [[Media: | #Download the [[Media: Lab_Excel_Raw_Data_Updated_2022.xlsx| Excel file]] containing the data for the yearly energy consumption of various renewable energy sources from 1949 to 2022. | ||
#The line graph | #The line graph contains multiple lines that represent the different renewable energy sources. The procedure below shows how to graph the renewable energy consumption for hydroelectric power, as an example. | ||
##On the Excel ribbon, click on the Insert tab, and click | ##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.[[Image:Inserting a line graph.png|500px|thumb|center|Figure 5: Inserting a Line Graph]] | ||
##Right-click on the empty graph and choose the '''Select Data''' option. A new window will appear where the graphed data can be inputted. | |||
##Right-click on the empty graph and choose the | ##Under the '''Legends Entries (Series)''' tab, click on the <b>“Add”</b> option, as shown in Figure 6. A new window called '''Edit Series''' will appear. [[Image:Lab10_excel_dataSource.png|500px|thumb|center|Figure 6: Utilizing the Select Data Source Window]] | ||
##Under the | ## In the '''Series Name''' textbox, type in the name of the power source, Hydroelectric Power. | ||
## In the '''Series X values''' textbox, highlight the years in column A from 1949 to 2022. | |||
##In the | ##In the '''Series Y values''' textbox, highlight the total energy consumption of hydroelectric power in column B that corresponds to 1949 to 2022. The '''Edit Series''' is shown in Figure 7.[[Image: Edit_Series.png|500px|thumb|center|Figure 7: Utilizing the Edit Series Window]] | ||
##In the | ## Click '''OK''' to create the graph of the Hydroelectric Power data. | ||
##In the | # 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. | ||
# Rename the axes and titles of the graph. Include a legend by selecting '''Legend''' in the dropdown menu located in the top right corner of the graph. See Figure 8 on how to navigate to '''Legend'''. | |||
##Click | #:[[Image:Zoomed_IN.png|500px|thumb|center|Figure 8: Add Legend to Chart]] | ||
# | |||
#Rename the | |||
=== Modeling the | === Modeling the 2022 Distribution === | ||
#To visualize the distribution of the various renewable energy sources in recent years, a pie chart will be created based on the | #To visualize the distribution of the various renewable energy sources in recent years, a pie chart will be created based on the 2022 year. This part will continue with the data obtained from the previous part. | ||
#On the Excel ribbon, click on the Insert tab, and click | #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 9.[[Image:Inserting a pie chart.png|500px|thumb|center|Figure 9: Inserting a Pie Chart]] | ||
#Right-click on the empty graph and choose the '''Select Data''' option. A new window will appear where the graphed data can be inputted. | |||
#Right-click on the empty graph and choose the | #Under the '''Legends Entries (Series)''' tab, click on '''Add'''. A new window called '''Edit Series''' will appear. | ||
#Under the | #In the '''Series Values''' textbox, highlight each renewable energy sources’ energy consumption for 2022. Completing this will automatically create five labels under the '''Horizontal (Category) Axis Labels''' tab, as shown in Figure 10. | ||
#In the | #:[[Image:Legend Entries (Series).png|500px|thumb|center|Figure 10: Horizontal Axis Labels]] | ||
# | #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) select '''OK'''. | ||
#Select the row labeled | #Rename the title to correspond to the data presented in the graph. | ||
#Rename the title | |||
= Assignment = | = Assignment = | ||
== File Submission == | |||
The Excel | The Excel files must be submitted as a zipped folder to the [https://eg.poly.edu/submit.php EG1004 website] by 11:59 PM the night before Lab 2. '''There is no lab report or presentation for Lab 1.''' | ||
Latest revision as of 16:12, 19 January 2024
Important: Please note that you must register on the EG1004 website before performing this 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 (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.
Time (s) | Distance (m) |
---|---|
- Open the Microsoft Excel Template containing Table 1.
- To calculate the instantaneous velocity at each point in time, Formula 1, as listed below is used. In (1), v is the instantaneous velocity,
(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.
is the second distance point, is the first distance point, is the time corresponding to the second distance point, and is the time corresponding to the first distance point.
- 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).
- To calculate the average velocity of the ball across the 11 data points, select cell D2. Enter the formula =AVERAGE(C2:C12).
- Create a scatter plot for the relationship between velocity and time.
- 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 click Select Data. Inside the Legend entries (Series) table, click on each entry and click the – button to delete.
- Right-click on the empty graph and click Select Data.
- Under the Legends Entries(Series) tab, click on the Add option, as shown in Figure 2. A new window called Edit Series will appear.
- In the Series X values text box, delete the {1} and highlight cells A2 to A12. In the "Series Y" text box, delete the {1} and highlight cells C2 to C12. Click OK to generate the scatter plot.
- 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.
- 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 of the data points in (2).
(2)
- In the dropdown menu at the top right of the graph, hover over Trendline and click on the black arrow. Select More Options.
- Under the Format Trendline window, check off Display Equation on chart and Display R-Squared Value on chart. The R-squared value is a measurement of how well the equation fits the data ranging from 0 to 1.
- Check the slope of the graph and compare it to the acceleration due to gravity in New York, -9.802 . Discuss possible reasons for 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 taken 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 2022 distribution in renewable energy. These graphs will be appended with data regarding fossil fuel energy consumption.
Modeling an Annual Trend
- Download the Excel file containing the data for the yearly energy consumption of various renewable energy sources from 1949 to 2022.
- The line graph contains multiple lines that represent the different renewable energy sources. The procedure below shows how to graph the renewable energy consumption for hydroelectric power, as an example.
- 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.
- Right-click on the empty graph and choose the Select Data option. A new window will appear where the graphed data can be inputted.
- Under the Legends Entries (Series) tab, click on the “Add” option, as shown in Figure 6. A new window called Edit Series will appear.
- In the Series Name textbox, type in the name of the power source, Hydroelectric Power.
- In the Series X values textbox, highlight the years in column A from 1949 to 2022.
- In the Series Y values textbox, highlight the total energy consumption of hydroelectric power in column B that corresponds to 1949 to 2022. The Edit Series is shown in Figure 7.
- Click OK to create the graph of the Hydroelectric Power data.
- 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.
- Rename the axes and titles of the graph. Include a legend by selecting Legend in the dropdown menu located in the top right corner of the graph. See Figure 8 on how to navigate to Legend.
Modeling the 2022 Distribution
- To visualize the distribution of the various renewable energy sources in recent years, a pie chart will be created based on the 2022 year. This part will continue with the data obtained from the previous part.
- 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 9.
- Right-click on the empty graph and choose the Select Data option. A new window will appear where the graphed data can be inputted.
- Under the Legends Entries (Series) tab, click on Add. A new window called Edit Series will appear.
- In the Series Values textbox, highlight each renewable energy sources’ energy consumption for 2022. Completing this will automatically create five labels under the Horizontal (Category) Axis Labels tab, as shown in Figure 10.
- 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) select OK.
- Rename the title to correspond to the data presented in the graph.
Assignment
File Submission
The Excel files must be submitted as a zipped folder to the EG1004 website by 11:59 PM the night before Lab 2. There is no lab report or presentation for Lab 1.