Lecture 11B

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

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 • Answer Wizard • Index • Show/Hide the Office Assistant • What’s This? • Office on the Web • About Microsoft Excel

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

Proper Use of Tables & Graphs You can copy straight into your final report!

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.

Table Example

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

1. Pie Chart Graphs - Types 2. Bar Graph

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.

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

Velocity of Three Runners During a 5 km Race Example

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

Lecture 11B

Lecture 11B

Presentation Transcript