Lecture 11B

1 / 80

# Lecture 11B - PowerPoint PPT Presentation

Lecture 11B. Using Excel Chapter 8. Example of an Excel Worksheet. Starting Excel. Figure 1-2, page 1.04. The Excel Window. Tool bar Formula bar Worksheet window Columns and Rows Cells Pointer Sheet tabs. Use the mouse Use the keyboard Arrow keys Page Up Page Down Home

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about ' Lecture 11B' - chambray-braun

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

### Lecture 11B

Using Excel

Chapter 8

Starting Excel

Figure 1-2,

page 1.04

The Excel Window
• Tool bar
• Formula bar
• Worksheet window
• Columns and Rows
• Cells
• Pointer
• Sheet tabs
Use the mouse

Use the keyboard

Arrow keys

Page Up

Page Down

Home

Crtl + Home

F5

Moving Around a Worksheet
Open 1-OhmsLaw.xls
• File Open...

Figure 1-8,

page 1.11

Some Basic Excel Stuff
• Learn how to enter text, values, formulas, and functions
• Describe a range of cells
• Perform what-if analysis
• Demonstrate how to print a worksheet
Entering Information
• Text - letters, symbols, numbers, and spaces
• Values - numbers that represent a quantity
• Formulas - used to calculate values
• Functions - special pre-written formulas
Range—used in functions
• Range - a group of cells: A1:C4

Figure 1-16,

Page 1.19

Saving a Workbook
• File Save As...

Figure 1-19,

Page 1.23

Playing “What-if?”
• Change a value in a cell
• Excel automatically recalculates the worksheet
Correcting Mistakes
• Before you press the Enter key
• Use the Backspace key
• After you pressed the Enter Key
• Double click on the cell to put it in Edit modeDon’t use the Space bar to clear a cell
Getting Help
• Microsoft Excel Help
• Contents
• Index
• Show/Hide the Office Assistant
• What’s This?
• Office on the Web
Printing the Worksheet
• File Print...

Figure 1-28,

Page 1.32

Closing and Exiting
• File Close
• File Exit
Intermediate Objectives
• List the components of a well designed worksheet
• Understand “Order of Precedence”
• Demonstrate several methods for copying information
• Understand Relative vs Absolute Cell Referencing
• Change the name of the worksheet
A Well Designed Worksheet
• Clearly identifies its goal
• Presents information in a clear, well organized format
• Includes all necessary data to produce the intended results
Developing the Worksheet
• Determine the worksheet’s purpose
• Enter the data and formulas
• Test the worksheet
• Correct errors and make modifications
• Document the worksheet
• Improve the appearance
• Save and print the completed worksheet
Planning the Worksheet
• What is the goal of the worksheet?
• What data is needed to calculate the results?
• What calculations are needed?
• EXAMPLE—Calculate density of air as a function of temperature from 0-50C in 5 degree steps
• r=P/RT
• P=stnd atmosphere 101.3kPa
• R=gas constant for air 286.9 J/kg-K
• T=temp in Kelvins
Building the Worksheet
• Establish the layout
• Enter the data
• Enter the formulas
• See 2-AirDensity.xls example
Entering Labels
• Helps to identify the cells where you will enter the data and formulas
• Alignment is left justified, and spill into empty cells to the right
Entering Data
• Values can be:
• Numbers
• Formulas
• Functions
• The data is the information you need to perform the calculations
Entering Formulas
• Formulas are equations that perform a calculation
• An = sign at the beginning of a cell indicates you are entering a formula
• If formulas contain more than one operator, Excel performs the calculation according to the standard order of precedence
Order of Precedence
• Predefined rules used to perform a calculation:

3 + 4 * 5 = ?

• Is the answer 35 or 23?
Order of Precedence (continued)
• Exponent: ^
• Multiplication and Division: * and /
• Addition and Subtraction: + and -

3 + 4 * 5 = 23

(3 + 4) * 5 = 35

Copying Formulas
• Toolbar buttons
• Fill Handle
AutoSum Button
• Automatically creates a formula that contains the SUM function
• Looks at the cells adjacent to the active cell and guesses which range of cells you want to sum
• Excel’s guess is displayed
Entering Functions
• Type the function by hand
• Use the Paste Functionbutton
• Do the example in 3-Functions.xls

Figure 2.17,

page 2.17

Relative vs Absolute Reference
• Relative Cell Reference

= A5*B5

• Absolute Cell Reference

= \$A\$5*\$B\$5

Relative vs Absolute References when Copying Formulas
• Relative Cell References - cell references change when they are copied
• Absolute Cell References - cell references do not change when they are copied
Mixed References
• Part of the reference is absolute and part is relative:

\$A5

• Do the example in

4-AbsoluteVsRelative.xls

Copying using Copy & Paste
• Select the cell or cells to be copied
• Select EditCopy
• Select the cell or cells you want to replace
• Select EditPaste

The copied cells are placed in the Clipboard, and can be pasted many times.

Renaming the Worksheet
• Double click on the worksheet tab
• Enter the new name
Excel Plotting Skills
• Learn to use tables and graphs as problem solving tools
• Learn and apply different types of graphs and scales
• Prepare graphs in Excel
• Be able to edit graphs
Plotting Data
• Independent Variables
• “The Cause”
• X-Axis on Graphs (abscissa)
• Left Columns on Tables
• Dependent Variables
• “The Effect”
• Y-Axis on Graphs (ordinate)
• Right Columns on Tables
Question
• The speed of sound in air depends on the temperature, humidity, and air pressure.
• What are the independent variables?
• …dependent variables?
Table and Graph Requirements
• Label the axes on your graphs.
• Include units on the axes and on column headings.
• Use landscape graphs.
Proper Use of Tables & Graphs

You can copy straight into

Tables
• Tables should always have:
• Title
• Column headings with brief descriptive name, symbol and appropriate units.
• Numerical data in the table should be written to the proper number of significant digits.
• The decimal points in a column should be aligned.
• Tables should always be referenced and discussed (at least briefly) in the body of the text of the document containing the table.
Graphs
• Proper graphing of data involves several steps:
• Select appropriate graph type
• Select scale and gradation of axes, and completely label axes
• Plot data points, then plot or fit curves
• Add titles, notes, and or legend
Graphs - Types

3. 3-D Graph

4. Line Graph

Body Temperature (0C

Distance (m)

Speed (m/s)

Graphs
• Each graph must include:
• A descriptive title which provides a clear and concise statement of the information being presented
• A legend defining point symbols or line types used for curves needs to be included
• Labeled axes
• Graphs should always be referenced/discussed in the body of the text of the document containing the table.
Titles and Legends
• Each graph must be identified with a descriptive title
• The title should include clear and concise statement of the information being presented
• A legend defining point symbols or line types used for curves needs to be included

Length (km)

Axis Labels
• Each axis must be labeled
• The axis label should contain the name of the variable and its units.
• The units can be enclosed in parentheses, or separated from the label by a comma.

Smallest Division=3.33

Smallest Division=1

Acceptable

Not Acceptable

• Scale gradations should be selected so that the smallest division of the axis is an integer power of 10 times 1, 2, or 5.
• Exception is units of time.
Data Points and Curves
• Data Points are plotted using symbols
• The symbol size must be large enough to easily distinguish them
• A different symbol is used for each data set
• Data Points are often connected with lines
• A different line style is often used for each data set
Building a Graph In Excel
• Select the data that you want to include in the chart by dragging through it with the mouse.
• Then click the Chart Wizard
Building the Graph
• Choose XY (Scatter), with data connected by lines if desired.
• Click “Next”
Building the Graph
• Make sure that the series is listed in columns, since your data is presented in columns.
• Click the Series tab to enter a name for the data set, if desired.
• Choose “Next”
Building the Graph
• Fill in Title and Axis information
• “Next”
Building a Chart
• Select “As new sheet” to create the chart on it’s own sheet in your Excel file, or “As object in” to create the chart on an existing sheet
• “Finish”

Right click on the line (data series) on the chart that you want to associate with a secondary axis.

Select “format data series”

Select the Axis tab, then “Plot series on secondary axis” as shown.

“OK”

Creating a Secondary Axis
Now you can go back to the “chart options” to add labels

Click the chart in a blank area, then either right click and select chart options or choose chart options from the “Chart” menu

Fill in or edit the axes labels, title, etc.

Click “OK”

### Graphical Analysis and Excel

Solving Problems Using

Graphical Analysis

RAT 5.1
• Close you books, notes and laptops. Turn off computer monitors.
• As a TEAM, you have 3 minutes to answer the following question.
• Name the type of function that plots as a straight line on:
• A) a semi-log graph
• B) a log-log graph
Learning Objectives
• Learn to use tables and graphs as problem solving tools
• Learn and apply different types of graphs and scales
• Prepare graphs in Excel
• Be able to edit graphs
• Be able to plot data on log scale
• Be able to determine the best-fit equations for linear, exponential and power functions
Exercise
• Enter the following table in Excel
• You can make your tables look nice by formatting text and borders

1 km

Linear scale:

Length (km)

1 km

10 km

Log scale:

Length (km)

Axis Formats (Scales)
• There are three common axis formats:
• Rectilinear: Two linear axes
• Semi-log: one log axis
• Log-log: two log axes
Use of Logarithmic Scales
• A logarithmic scale is normally used to plot numbers that span many orders of magnitude
Creating Log Scales in Excel
• Exercise (2 min): Create a graph using only x and y1.
Creating Log Scales in Excel
• Now modify the graph so the data is plotted as semi-log y
• This means that the y-axis is log scale and the x-axis is linear.
• Right click on the axis to be modified and select “format axis”
Creating Log Scales in Excel
• On the Scale tab, select logarithmic
• “OK”
• Next, go to Chart Options and select the Gridlines tab. Turn on (check) the Minor gridlines for the y-axis.
• “OK”
Exercise (10 min)
• Copy and Paste the graph twice.
• Modify one of the new graphs to be semi-log x
• Modify the other new graph to be log-log
• Note how the scale affects the shape of the curve.
Equations
• The equation that represents a straight line on each type of scale is:
• Linear (rectilinear): y = mx + b
• Exponential (semi-log): y = bemx or y = b10mx
• Power (log-log): y = bxm
• The values of m and b can be determined if the coordinates of 2 points on THE BEST-FIT LINE are known:
• Insert the values of x and y for each point in the equation (2 equations)
• Solve for m and b (2 unknowns)
Equations (CAUTION)
• The values of m and b can be determined if the coordinates of 2 points on THE BEST-FIT LINE are known.
• You must select the points FROM THE LINE to compute m and b. In general, this will not be a data point from the data set. The exception - if the data point lies on the best-fit line.
Consider the data set:

X Y

1 4

2 8

3 10

4 12

5 11

6 16

7 18

8 19

9 20

10 24

Example
• Points (0.1, 2) and (6, 20) are taken from a straight line on a rectilinear graph.
• Find the equation of the line.
• Solution:

2 = m(0.1) + b a)

20 = m(6) + b b)

Solving a) & b) simultaneously:

m = 3.05, b = 1.69

Thus: y = 3.05x + 1.69

Pairs Exercise (10 min)
• FRONT PAIR:
• Points (0.1, 2) and (6, 20) are taken from a straight line on a log-log graph.
• Find the equation of the line.
• BACK PAIR:
• Points (0.1, 2) and (6, 20) are taken from a straight line on a semi-log graph.
• Find the equation(s) of the line.
Interpolation
• Interpolation is the process of estimating a value for a point that lies on a curve between known data points
• Linear interpolation assumes a straight line between the known data points
• One Method:
• Select the two points with known coordinates
• Determine the equation of the line that passes through the two points
• Insert the X value of the desired point in the equation and calculate the Y value
Individual Exercise (5 min)
• Given the following set of points, find y2 using linear interpolation.

(x1,y1) = (1,18)

(x2,y2) = (2.4,y2)

(x3,y3) = (4,35)

A Baseball Problem
• A runner is on 3rd base, 90 ft from home plate. He can run with an average speed of 27 ft/s. A ball is hit to the center fielder who catches it 310 ft from home plate. The center fielder can throw the ball no harder than 110 ft/s. The runner tags up and runs for home plate.
• Can the center fielder throw him out? To do so, he must get the ball to the catcher at an appropriate height before the runner can get to home plate.
• If so, at what angle and what velocity does he need to throw the ball in order to put the runner out?
Solving with Excel
• Open an Excel Spreadsheet and create column heads like the example.
• Rows 1 - 6 are for constants. Remember to use the \$ notation when reference absolute address
Solution - Using a Chart
• One way to solve this problem is with a graphical representation.
• Generate data representing the position of the ball (x(t) and y(t)) and the position of the runner (r(t)).
• The next slide shows an example of a completed chart. Notice that the red line shows the ball’s position reaches 310 ft before the runner (blue line) has traveled 90 ft.