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

Figure 1-2,

page 1.04

• Tool bar

• Formula bar

• Worksheet window

• Columns and Rows

• Cells

• Pointer

• Sheet tabs

Use the keyboard

Arrow keys

Page Up

Page Down

Home

Crtl + Home

F5

Moving Around a Worksheet

• File Open...

Figure 1-8,

page 1.11

• Learn how to enter text, values, formulas, and functions

• Describe a range of cells

• Perform what-if analysis

• Demonstrate how to print a worksheet

• Text - letters, symbols, numbers, and spaces

• Values - numbers that represent a quantity

• Formulas - used to calculate values

• Functions - special pre-written formulas

• Range - a group of cells: A1:C4

Figure 1-16,

Page 1.19

• File Save As...

Figure 1-19,

Page 1.23

• Change a value in a cell

• Excel automatically recalculates the worksheet

• 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

• Microsoft Excel Help

• Contents

• Index

• Show/Hide the Office Assistant

• What’s This?

• Office on the Web

• File Print...

Figure 1-28,

Page 1.32

• File Close

• File Exit

• 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

• Clearly identifies its goal

• Presents information in a clear, well organized format

• Includes all necessary data to produce the intended results

• 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

• 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

• Establish the layout

• Enter the data

• Enter the formulas

• See 2-AirDensity.xls example

• 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

• Values can be:

• Numbers

• Formulas

• Functions

• The data is the information you need to perform the calculations

• 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

• 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

• Toolbar buttons

• Fill Handle

• 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

• Type the function by hand

• Use the Paste Functionbutton

• Do the example in 3-Functions.xls

Figure 2.17,

page 2.17

• Relative Cell Reference

= A5*B5

• Absolute Cell Reference

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

• Relative Cell References - cell references change when they are copied

• Absolute Cell References - cell references do not change when they are copied

• Part of the reference is absolute and part is relative:

\$A5

• Do the example in

4-AbsoluteVsRelative.xls

• 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.

• Double click on the worksheet tab

• Enter the new name

• 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

• 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

• The speed of sound in air depends on the temperature, humidity, and air pressure.

• What are the independent variables?

• …dependent variables?

• Label the axes on your graphs.

• Include units on the axes and on column headings.

• Use landscape graphs.

You can copy straight into

• 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.

• 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

2. Bar Graph

3. 3-D Graph

4. Line Graph

Body Temperature (0C

Distance (m)

Speed (m/s)

• 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.

• 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

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 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

During a 5 km Race

Example

• Select the data that you want to include in the chart by dragging through it with the mouse.

• Then click the Chart Wizard

• Choose XY (Scatter), with data connected by lines if desired.

• Click “Next”

• 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”

• Fill in Title and Axis information

• “Next”

• 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

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”

Result ranges.

### Graphical Analysis and Excel ranges.

Solving Problems Using

Graphical Analysis

RAT 5.1 ranges.

• 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 ranges.

• 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 ranges.

• Enter the following table in Excel

• You can make your tables look nice by formatting text and borders

1 km ranges.

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 ranges.

• A logarithmic scale is normally used to plot numbers that span many orders of magnitude

Creating Log Scales in Excel ranges.

• Exercise (2 min): Create a graph using only x and y1.

Creating Log Scales in Excel ranges.

• 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 ranges.

• 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) ranges.

• 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 ranges.

• 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) ranges.

• 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: ranges.

X Y

1 4

2 8

3 10

4 12

5 11

6 16

7 18

8 19

9 20

10 24

Example of this best-fit line?

• 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) of this best-fit line?

• 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 of this best-fit line?

• 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) of this best-fit line?

• 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 of this best-fit line?

• 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 of this best-fit line?

• 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 of this best-fit line?

• 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.