1 / 80

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

chambray-braun
Download Presentation

Lecture 11B

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lecture 11B Using Excel Chapter 8

  2. Example of an Excel Worksheet

  3. Starting Excel Figure 1-2, page 1.04

  4. The Excel Window • Tool bar • Formula bar • Worksheet window • Columns and Rows • Cells • Pointer • Sheet tabs

  5. Use the mouse Use the keyboard Arrow keys Page Up Page Down Home Crtl + Home F5 Moving Around a Worksheet

  6. Open 1-OhmsLaw.xls • File Open... Figure 1-8, page 1.11

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

  8. Entering Information • Text - letters, symbols, numbers, and spaces • Values - numbers that represent a quantity • Formulas - used to calculate values • Functions - special pre-written formulas

  9. Range—used in functions • Range - a group of cells: A1:C4 Figure 1-16, Page 1.19

  10. Saving a Workbook • File Save As... Figure 1-19, Page 1.23

  11. Playing “What-if?” • Change a value in a cell • Excel automatically recalculates the worksheet

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

  13. Getting Help • Microsoft Excel Help • Contents • Answer Wizard • Index • Show/Hide the Office Assistant • What’s This? • Office on the Web • About Microsoft Excel

  14. Printing the Worksheet • File Print... Figure 1-28, Page 1.32

  15. Closing and Exiting • File Close • File Exit

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

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

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

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

  20. Building the Worksheet • Establish the layout • Enter the data • Enter the formulas • See 2-AirDensity.xls example

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

  22. Entering Data • Values can be: • Numbers • Formulas • Functions • The data is the information you need to perform the calculations

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

  24. Order of Precedence • Predefined rules used to perform a calculation: 3 + 4 * 5 = ? • Is the answer 35 or 23?

  25. Order of Precedence (continued) • Exponent: ^ • Multiplication and Division: * and / • Addition and Subtraction: + and - 3 + 4 * 5 = 23 (3 + 4) * 5 = 35

  26. Copying Formulas • Menu Commands • Toolbar buttons • Fill Handle

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

  28. Entering Functions • Type the function by hand • Use the Paste Functionbutton • Do the example in 3-Functions.xls Figure 2.17, page 2.17

  29. Relative vs Absolute Reference • Relative Cell Reference = A5*B5 • Absolute Cell Reference = $A$5*$B$5

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

  31. Mixed References • Part of the reference is absolute and part is relative: $A5 • Do the example in 4-AbsoluteVsRelative.xls

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

  33. Renaming the Worksheet • Double click on the worksheet tab • Enter the new name

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

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

  36. Question • The speed of sound in air depends on the temperature, humidity, and air pressure. • What are the independent variables? • …dependent variables?

  37. Table and Graph Requirements • Label the axes on your graphs. • Include units on the axes and on column headings. • Use landscape graphs.

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

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

  40. Table Example

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

  42. 1. Pie Chart Graphs - Types 2. Bar Graph

  43. Graphs - Types 3. 3-D Graph 4. Line Graph Body Temperature (0C Distance (m) Speed (m/s)

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

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

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

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

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

  49. Velocity of Three Runners During a 5 km Race Example

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

More Related