Lecture 11b
Download
1 / 80

Lecture 11B - PowerPoint PPT Presentation


  • 112 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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
Lecture 11b

Lecture 11B

Using Excel

Chapter 8



Starting excel
Starting Excel

Figure 1-2,

page 1.04


The excel window
The Excel Window

  • Tool bar

  • Formula bar

  • Worksheet window

    • Columns and Rows

    • Cells

  • Pointer

  • Sheet tabs


Moving around a worksheet

Use the mouse

Use the keyboard

Arrow keys

Page Up

Page Down

Home

Crtl + Home

F5

Moving Around a Worksheet


Open 1 ohmslaw xls
Open 1-OhmsLaw.xls

  • File Open...

Figure 1-8,

page 1.11


Some basic excel stuff
Some 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
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—used in functions

  • Range - a group of cells: A1:C4

Figure 1-16,

Page 1.19


Saving a workbook
Saving a Workbook

  • File Save As...

Figure 1-19,

Page 1.23


Playing what if
Playing “What-if?”

  • Change a value in a cell

  • Excel automatically recalculates the worksheet


Correcting mistakes
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
Getting Help

  • Microsoft Excel Help

    • Contents

    • Answer Wizard

    • Index

  • Show/Hide the Office Assistant

  • What’s This?

  • Office on the Web

  • About Microsoft Excel


Printing the worksheet
Printing the Worksheet

  • File Print...

Figure 1-28,

Page 1.32


Closing and exiting
Closing and Exiting

  • File Close

  • File Exit


Intermediate objectives
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
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
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
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
Building the Worksheet

  • Establish the layout

  • Enter the data

  • Enter the formulas

  • See 2-AirDensity.xls example


Entering labels
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
Entering Data

  • Values can be:

    • Numbers

    • Formulas

    • Functions

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


Entering formulas
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
Order of Precedence

  • Predefined rules used to perform a calculation:

    3 + 4 * 5 = ?

  • Is the answer 35 or 23?


Order of precedence continued
Order of Precedence (continued)

  • Exponent: ^

  • Multiplication and Division: * and /

  • Addition and Subtraction: + and -

    3 + 4 * 5 = 23

    (3 + 4) * 5 = 35


Copying formulas
Copying Formulas

  • Menu Commands

  • Toolbar buttons

  • Fill Handle


Autosum button
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
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 vs Absolute Reference

  • Relative Cell Reference

    = A5*B5

  • Absolute Cell Reference

    = $A$5*$B$5


Relative vs absolute references when copying formulas
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
Mixed References

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

    $A5

  • Do the example in

    4-AbsoluteVsRelative.xls


Copying using copy paste
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
Renaming the Worksheet

  • Double click on the worksheet tab

  • Enter the new name


Excel plotting skills
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
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
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
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
Proper Use of Tables & Graphs

You can copy straight into

your final report!


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

1. Pie Chart

Graphs - Types

2. Bar Graph


Graphs types1
Graphs - Types

3. 3-D Graph

4. Line Graph

Body Temperature (0C

Distance (m)

Speed (m/s)


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

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.


Gradation

Scale Graduations,

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


Example

Velocity of Three Runners

During a 5 km Race

Example


Building a graph in excel
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
Building the Graph

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

  • Click “Next”


Building the graph1
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 graph2
Building the Graph

  • Fill in Title and Axis information

  • “Next”


Building a chart
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”


Creating a secondary axis

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 Axis


Editing adding labels

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


Result
Result ranges.


Graphical analysis and excel

Graphical Analysis and Excel ranges.

Solving Problems Using

Graphical Analysis


Rat 5 1
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
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
Exercise ranges.

  • Enter the following table in Excel

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


Axis formats scales

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
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
Creating Log Scales in Excel ranges.

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


Creating log scales in excel1
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 excel2
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
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
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
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
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



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


ad