- 112 Views
- Uploaded on

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

**An Image/Link below is provided (as is) to download presentation**

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

The Excel Window

- 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 WorksheetSome Basic Excel Stuff

- Learn how to enter text, values, formulas, and functions
- Describe a range of cells
- Perform what-if analysis
- Learn how to use online Help
- 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

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
- Answer Wizard
- Index

- Show/Hide the Office Assistant
- What’s This?
- Office on the Web
- About Microsoft Excel

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

- Menu Commands
- 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.

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

- 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

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

Scale Graduations,

Smallest Division=1

Acceptable

Not Acceptable

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

This is useful when the data sets cover very different ranges.

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 AxisNow you can go back to the “chart options” to add labels ranges.

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”

Editing/Adding LabelsResult ranges.

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”

Result: Graph is straight line. ranges.

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.

Which data points should be used to determine the equation of this best-fit line?

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.

Download Presentation

Connecting to Server..