What Every Engineer
Should Know About
WHAT EVERY ENGINEER SHOULD KNOW
A Series
Series Editor*
Phillip A. Laplante
Pennsylvania State University
1. What Every Engineer Should Know About Patents,
William G. Konold, Bruce Tittel, Donald F. Frei, and David S. Stallard 2. What Every Engineer Should Know About Product Liability,
James F. Thorpe and William H. Middendorf
3. What Every Engineer Should Know About Microcomputers: Hardware/Software Design, A Step-by-Step Example, William S. Bennett and Carl F. Evert, Jr.
4. What Every Engineer Should Know About Economic Decision Analysis, Dean S. Shupe
5. What Every Engineer Should Know About Human Resources Management, Desmond D. Martin and Richard L. Shell
6. What Every Engineer Should Know About Manufacturing Cost Estimating, Eric M. Malstrom
7. What Every Engineer Should Know About Inventing, William H. Middendorf
8. What Every Engineer Should Know About Technology Transfer and Innovation, Louis N. Mogavero and Robert S. Shane
9. What Every Engineer Should Know About Project Management, Arnold M. Ruskin and W. Eugene Estes
10. What Every Engineer Should Know About Computer-Aided Design and Computer-Aided Manufacturing: The CAD/CAM Revolution, John K. Krouse
11. What Every Engineer Should Know About Robots, Maurice I. Zeldman 12. What Every Engineer Should Know About Microcomputer Systems
Design and Debugging, Bill Wray and Bill Crawford
13. What Every Engineer Should Know About Engineering Information Resources, Margaret T. Schenk and James K. Webster
14. What Every Engineer Should Know About Microcomputer Program Design, Keith R. Wehmeyer
15. What Every Engineer Should Know About Computer Modeling and Simulation, Don M. Ingels
16. What Every Engineer Should Know About Engineering Workstations, Justin E. Harlow III
17. What Every Engineer Should Know About Practical CAD/CAM Applications, John Stark
18. What Every Engineer Should Know About Threaded Fasteners: Materials and Design, Alexander Blake
19. What Every Engineer Should Know About Data Communications, Carl Stephen Clifton
20. What Every Engineer Should Know About Material and Component Failure, Failure Analysis, and Litigation, Lawrence E. Murr
21. What Every Engineer Should Know About Corrosion, Philip Schweitzer 22. What Every Engineer Should Know About Lasers, D. C. Winburn 23. What Every Engineer Should Know About Finite Element Analysis,
John R. Brauer
24. What Every Engineer Should Know About Patents: Second Edition, William G. Konold, Bruce Tittel, Donald F. Frei, and David S. Stallard 25. What Every Engineer Should Know About Electronic Communications
Systems,L. R. McKay
26. What Every Engineer Should Know About Quality Control, Thomas Pyzdek
27. What Every Engineer Should Know About Microcomputers:
Hardware/Software Design, A Step-by-Step Example. Second Edition, Revised and Expanded, William S. Bennett, Carl F. Evert,
and Leslie C. Lander
28. What Every Engineer Should Know About Ceramics, Solomon Musikant
29. What Every Engineer Should Know About Developing Plastics Products, Bruce C. Wendle
30. What Every Engineer Should Know About Reliability and Risk Analysis, M. Modarres
31. What Every Engineer Should Know About Finite Element Analysis: Second Edition, Revised and Expanded, John R. Brauer
32. What Every Engineer Should Know About Accounting and Finance, Jae K. Shim and Norman Henteleff
33. What Every Engineer Should Know About Project Management: Second Edition, Revised and Expanded, Arnold M. Ruskin and W. Eugene Estes
34. What Every Engineer Should Know About Concurrent Engineering, Thomas A. Salomone
35. What Every Engineer Should Know About Ethics, Kenneth K. Humphreys
36. What Every Engineer Should Know About Risk Engineering and Management, John X. Wang and Marvin L. Roush
37. What Every Engineer Should Know About Decision Making Under Uncertainty, John X. Wang
38. What Every Engineer Should Know About Computational Techniques of Finite Element Analysis, Louis Komzsik
39. What Every Engineer Should Know About Excel, Jack P. Holman
CRC is an imprint of the Taylor & Francis Group, an informa business
What Every Engineer
Should Know About
J. P. Holman
Southern Methodist University
Boca Raton London New York
International Standard Book Number-13: 978-1-4200-0719-0 (eBook - PDF)
This book contains information obtained from authentic and highly regarded sources. Reasonable efforts have been made to publish reliable data and information, but the author and publisher cannot assume responsibility for the valid-ity of all materials or the consequences of their use. The authors and publishers have attempted to trace the copyright holders of all material reproduced in this publication and apologize to copyright holders if permission to publish in this form has not been obtained. If any copyright material has not been acknowledged please write and let us know so we may rectify in any future reprint.
Except as permitted under U.S. Copyright Law, no part of this book may be reprinted, reproduced, transmitted, or uti-lized in any form by any electronic, mechanical, or other means, now known or hereafter invented, including photocopy-ing, microfilmphotocopy-ing, and recordphotocopy-ing, or in any information storage or retrieval system, without written permission from the publishers.
For permission to photocopy or use material electronically from this work, please access www.copyright.com (http:// www.copyright.com/) or contact the Copyright Clearance Center, Inc. (CCC), 222 Rosewood Drive, Danvers, MA 01923, 978-750-8400. CCC is a not-for-profit organization that provides licenses and registration for a variety of users. For organizations that have been granted a photocopy license by the CCC, a separate system of payment has been arranged. Trademark Notice: Product or corporate names may be trademarks or registered trademarks, and are used only for identification and explanation without intent to infringe.
Visit the Taylor & Francis Web site at http://www.taylorandfrancis.com and the CRC Press Web site at http://www.crcpress.com
About the Author
J.P. Holman received a Ph.D. in mechanical engineering from Oklahoma State University. After research experience at the Air Force Aerospace Research Laboratories, he joined the faculty of Southern Methodist University, Dallas, Texas. Dr. Holman has published over 30 papers in several areas of heat transfer and is the author of three widely used books: Heat Transfer (9th edition, 2002), Thermodynamics
(4th edition, 1988), and Experimental Methods for Engineers
(7th edition, 2000). These books have been translated into Spanish, Chinese, Japanese, Korean, Portuguese, Thai, and Indonesian and are distributed worldwide.
A fellow of ASME, Dr. Holman is a recipient of the
Worcester Reed Warner Gold Medal and the James Harry Potter Gold Medal from ASME for distinguished contributions to the permanent literature of engineering. He is also the recipient of the American Society for Engineering Education’s George Westinghouse and Ralph Coats Roe Awards for distin-guished contributions to mechanical engineering education.
Preface
This collection of materials involving operations in Microsoft Excel is intended primarily for engineers, although many of the displays and topics will be of interest to other readers as well. The procedures have been generated somewhat randomly as individual segments, which were distributed to classes as the need arose. They do not take the place of the many excellent books on the subjects of numerical methods, statistics, engineering anal-ysis, or the information that is available through the Help/Index features of the software packages. Some of the suggestions offered herein will be obvious to an experienced user of the software but much less apparent or even eye-opening to others. It is this latter group for whom the collection was assembled.
Some of the materials were written for use in classes in engineering laboratory and heat-transfer subjects, so several of the examples are tainted in the direction of these applica-tions. Even so, topics such as solutions to simultaneous linear and nonlinear equations and uses of graphing techniques are pervasive and easily extended to other applications. The reader will notice that a basic familiarity with spreadsheets, the formats for entering equations, and a basic knowledge of graphs is assumed. A basic acquaintance with Microsoft Word is also expected, including simple editing operations.
The Table of Contents furnishes a fairly straightforward guide for selecting topics from the book. It must be noted that the topics are presented as stand-alone items in many cases, which do not necessarily depend on previous sections. Where previous topics are relevant they are noted in that section. The reader will find that some topics are repeated — such as instructions for formatting graphs and charts — where it was judged beneficial. In Chapter 1 the convention employed for sequential sets of operations is noted along with the background expected of the reader. The user will find the suggested custom keyboard setup in Section 2.3 to be very useful for typing equations and math symbols. While possibly of infrequent use, the application of photo inserts is discussed in Section 2.9. Increased use of scanners and digital cameras may add to the utility of these sections. Most engineering graphs are of the x-y scatter variety, and the combination of the information presented at Section 3.3 and suggested default settings at Section 3.22 should be quite helpful in application of these graphs. Most people do not think of using Excel to generate line drawings. The discussion in Section 4.2 illustrates the relative simplicity of making such drawings and embedding them in Excel and Word documents. Section 4.3 and Section 4.5 illustrate methods for inserting and combining symbols, equations, and graphics in both Excel and Word.
Chapter 5 presents methods for solving single or simultaneous sets of linear or nonlinear equations. Section 5.4 presents an iterative method that is particularly useful for solving linear nodal equations in applications with sparse coefficient matrices. Histograms, cumu-lative frequency distributions, and normal probability functions are discussed in Chapter 6 along with several regression methods. Three regression techniques are applied to an example that analyzes the performance of a commercial air-conditioning unit.
Because financial analysis is frequently a part of engineering design, Chapter 7 presents an abbreviated view of the built-in Excel financial functions. Several examples of the use of these functions are also given. Optimization techniques are also a part of engineering design, so Chapter 8 gives a brief view of the use of the Solver feature of Excel for analyzing such problems.
Pivot tables are employed for arranging and categorizing small or large sets of data into different formats. In the presentation in Chapter 9, the approach has been to employ their use not only for rearranging tabular information but also for inserting calculated results of interest. This presentation then becomes a vehicle to supplement the creation of data tables and charts by other means.
Contents
1
Introduction...11.1 Getting the Most from Excel ...1
1.2 Conventions ...2
1.3 Outline of Contents ...3
2
Miscellaneous Operations in Excel and Word...52.1 Introduction...5
2.2 Print Screen or Screen Dump...5
2.3 Custom Keyboard Setup for Symbols in Word ...7
2.4 Viewing or Printing Column and Row Headings and Gridlines in Excel...8
2.5 Assorted Instructions...8
2.6 Moving Objects in Small Increments (Nudging)...10
2.7 Formatting Objects in Word, Including Wrapping ...11
2.8 Formatting Objects in Excel ...11
2.9 Use of Photo-Editing Software in Word, Including Wrapping ...11
2.10 Copying Cell Formulas: Effect of Relative and Absolute Addresses...14
2.11 Copying Formulas by Dragging the Fill Handle ...15
2.12 Shortcut for Changing the Status of Cell Addresses ...17
2.13 Switching and Copying Columns or Rows, and Changing Rows to Columns or Columns to Rows ...17
2.14 Built-In Functions in Excel ...18
2.15 Creating Single-Variable Tables Using the DATA/TABLE Command ...19
2.16 Creating Two-Variable Tables Using the DATA/TABLE Command ...21
Problems ...24
3
Charts and Graphs...273.1 Introduction...27
3.2 Moving Dialog Windows...27
3.3 Excel Chart Wizard Window Showing Choice of x-y Scatter Charts ...28
3.4 Selecting and Adding Data for x-y Scatter Charts ...29
3.5 Changing and Adding Data for Charts Using the SOURCE DATA Command ...30
3.6 Adding Data to Charts Using the ADD DATA Command ...30
3.7 Adding Trendlines and Correlation Equations to Scatter Charts...31
3.8 Equation for R2...31
3.9 Correlation of Experimental Data with Power Relation ...32
3.10 Use of Logarithmic Scales ...34
3.11 Correlation with Exponential Functions ...35
3.12 Use of Different Scatter Graphs for the Same Data ...36
3.12.1 Observations...41
3.13 Plot of a Function of Two Variables with Different Chart Types ...41
3.13.1 Changes in Gap Width and Chart Depth on 3-D Displays...44
3.14 Plots of Two Variables with and without Separate Scales...44
3.15 Charts Used for Calculation Purposes or G&A Format...45 7326_C000.fm Page 13 Wednesday, March 29, 2006 5:50 AM
3.15.1 G&A Chart ...48
3.16 Stretching Out a Chart from a Single Chart Page...48
3.17 Alternate Chart Sizing Procedure Using MS Word ...49
3.18 Calculation and Graphing of Moving Averages...50
3.18.1 Standard Error...54
3.19 Bar and Column Charts ...55
3.20 Chart Format and Cosmetics ...56
3.21 Surface Charts...58
3.22 Suggested Scatter Graph Setting as Default Chart ...59
3.23 An Exercise in 3-D Visualization...63
3.24 Editing Excel Charts Using Word ...63
3.25 Editing Excel Tables Using Word ...65
3.26 Alternate Procedure...67
3.27 Editing Excel Charts Directly in Word by Using Grouping ...69
Problems ...72
4
Line Drawings and Embedded Objects in Excel...774.1 Introduction...77
4.2 Constructing, Moving, and Inserting Straight Line Drawings ...77
4.2.1 Drawing Line Segments in Precise Angular Increments ...78
4.3 Inserting Items in Excel with Symbols, Subscripts, and Superscripts...83
4.4 Inserting Equations or Symbols in Word Using Equation Editor ...85
4.5 Inserting Equations and Symbols in Excel Using Equation Editor...86
4.6 Construction of Line Drawings from Plotted Coordinates...88
Problems ...92
5
Solution of Equations...935.1 Introduction...93
5.2 Solutions to Single Nonlinear Equations Using Goal Seek ...93
5.3 Solutions to Single Nonlinear Equations Using Solver...95
5.4 Iterative Solutions to Simultaneous Linear Equations ...98
5.5 Solutions of Simultaneous Linear Equations Using Matrix Inversion ...99
5.5.1 Error Messages...103
5.6 Solutions of Simultaneous Nonlinear Equations Using Solver...103
5.7 Solver Results Dialog Box ...110
5.8 Comparison of Methods for Solution of Simultaneous Linear Equations ... 111
5.9 Copying Cell Equations for Repetitive Calculations ...112
5.10 Creating and Running Macros...113
Problems ...118
6
Other Operations... 1216.1 Introduction...121
6.2 Numerical Evaluation of Integrals...121
6.3 Use of Logical IF Statement ...125
6.4 Histograms and Cumulative Frequency Distributions ...128
6.5 Normal Error Distributions ...132
6.6 Calculation of Uncertainty Propagation in Experimental Results...138
6.7 Fractional Uncertainties for Product Functions of Primary Variables ...142
6.9 Multivariable Exponential Regression...150
Problems ...158
7
Financial Functions and Calculations... 1617.1 Introduction...161
7.2 Nomenclature ...161
7.3 Compound Interest Formulas...162
7.4 Investment Accumulation with Increasing Annual Payments...168
7.5 Payout at Variable Rates from an Initial Investment...168
Problems ...171
8
Optimization Problems... 1758.1 Introduction...175
8.2 Graphical Examples of Linear and Nonlinear Optimization Problems ...176
8.3 Solutions Using Solver ...179
8.4 Solver Answer Reports for Examples...182
8.5 Nomenclature for Sensitivity Reports ...185
8.6 Nomenclature for Answer Reports ...186
8.7 Nomenclature for Limits Reports...186
Problems ...186
9
Pivot Tables... 1919.1 Introduction...191
9.2 Other Summary Functions for Data Fields ...204
9.3 Restrictions on Pivot Table Formulas ...207
9.3.1 Ordering of Data Fields and Resultant Graphs ...208
9.4 Calculating and Charting Single or Multiple Functions f(x) vs. x Using Pivot Tables...212
9.5 Calculating and Plotting Functions of Two Variables ...216
9.5.1 Display of Formulas and Solve Order ...219
Problems ...220
References... 223
Index... 225 7326_C000.fm Page 15 Wednesday, March 29, 2006 5:50 AM
1
1
Introduction
1.1 Getting the Most from Excel
Microsoft Excel is a deceptive software package in that it offers computation and graphics capabilities far beyond what one would expect in a spreadsheet tool and also because its capabilities remain unknown to many engineers and technical persons. This book is written for the person who is casually familiar with Excel but is unaware of its broad potential. Although a novice will find the material useful, it will be most attractive to those who have the following:
1. A basic knowledge of both Excel and Word, including procedures for entering equations in Excel, editing fundamentals, and some experience with creating graphs
2. A basic knowledge of differential and integral calculus
3. For some sections, a familiarity with solution techniques for single and simulta-neous equations
4. For some sections, a familiarity with basic statistics, including the concepts of standard deviation and probability
Many of the sections in this book resulted from small instructional sets that were written as stand-alone packages for engineering students enrolled in a mechanical engineering curriculum. In addition, some of the sets and example problems are related to applications in the thermal and fluids areas of mechanical engineering. Although these application examples have been retained, they have been presented as part of more general procedures that will be applicable to other disciplines.
Unless a person works with a software package such as Excel on a continual basis, it is easy to forget some of the shortcuts and nuances of operation that accomplish calculation or presentation objectives, viz., procedures for viewing all equations on a worksheet, stretching graphs to multipage proportions, inserting symbols in equations, etc. Such hints have been presented in compact form for the convenience of the reader.
The title of this book refers to Excel, but the reader will find several applications that call for a combination of features of Microsoft Word in conjunction with the capabilities of Excel. Most users of Excel will have the complete Microsoft Office Professional Suite, so no problem should arise. Microsoft PowerPoint is also a powerful tool for presentations but is not covered in this book.
The Help/Index features of both Excel and Word are of obvious practical utility in working with the software. When appropriate, the reader’s attention is directed to specific index items for further information. There are many books written on Excel and many 7326_C001.fm Page 1 Tuesday, March 7, 2006 6:16 AM
specialized references that pertain to particular engineering examples. A list of all refer-ences for this book is given in the appendix, and callouts to this list are made at appropriate times in the book. Separate reference lists are not provided at the conclusion of each chapter.
Many worked examples are presented throughout the book. For the reader’s conve-nience, each example is given a title. In some cases, the example title also specifies the calculation principle or technique that is being demonstrated. Extensive use is made of graphs and figures, as well as of printouts of specific spreadsheet segments employed in the examples. Screen dumps that show the worksheet and dialog window contents in perspective are also displayed.
The reader will find that many sections in the book can be used independently. This stand-alone nature results from the way many of the topics were generated initially, as well as from an expectation that many readers want information in a compact self-contained form without having to move back and forth from section to section. To further achieve a compact presentation, explanatory notes are sometimes displayed as embedded text on the pertinent worksheet. When a topic relates to other sections, appropriate notes and references are given.
1.2 Conventions
As described earlier, many of the presentations herein are in a compact form, which allows for more rapid or convenient use. When specifying a procedure that consists of a sequence of operations, we will use the following convention
VIEW/TOOLBARS/DRAWING/AUTOSHAPES/choose Freeform instead of the more cumbersome set of instructions:
1. Click View. 2. Click Toolbars. 3. Click Drawing. 4. Select AutoShapes. 5. Select Freeform.
Another example in Excel is
TOOLS/OPTIONS/VIEW/check Formulas which is equivalent to the following:
1. Click on Tools. 2. Click Options. 3. Select the View tab.
4. Check Formulas box to display all formulas.
Embedding of text boxes and descriptive Word statements in the example Excel work-sheets is freely employed to express the instructions in a compact form. In many cases this results in a font size smaller than the main body of the text, but is usually not objectionable.
Introduction 3 In these examples, the font selected for almost all of the text and graphics nomenclature is Times New Roman. Equations requiring math or Greek symbols have mostly been typed in Word, using symbol shortcuts described at Section 2.3 of Chapter 2. A few complicated formulas have been assembled using Equation Editor. Most of the charts are presented without pattern fill and, of course, without color. A few charts were produced in color and printed in grayscale.
1.3 Outline of Contents
Chapter 2 presents a potpourri of miscellaneous topics in Word and Excel that are appli-cable to the other chapters. Chapter 3 describes a number of graphing techniques that may be employed in engineering applications. Chapter 4 discusses use of line drawings and other graphics in Word and Excel. Chapter 5 presents a variety of Excel techniques for solving single and multiple linear or nonlinear equations, along with numerical exam-ples of each technique. Chapter 6 presents some other numerical applications, including histograms and multivariable regression analysis, whereas Chapter 7 is devoted to dis-cussion and use of financial functions built into Excel. Chapter 8 presents some optimi-zation techniques that may be exploited with Excel Solver, and finally, Chapter 9 presents some basic operations with pivot tables.
5
2
Miscellaneous Operations in Excel and Word
2.1 Introduction
This chapter contains a collection of hints and reminders for miscellaneous edit, format, and shortcut operations. The reader should take particular note of Section 2.3, which offers detailed suggestions for customizing the keyboard for direct typing of math and Greek symbols. Use of these shortcuts will greatly simplify typing of most equations and math-ematical expressions. For those that require more elaborate inserts, directions for employ-ing Equation Editor are given. For those who choose to have digital photo inserts, brief instructions for their use are given in Section 2.9.
Some of the format and edit instructions will be repeated from time to time when they are needed in a particular example or discussion.
2.2 Print Screen or Screen Dump
The following are instructions to print the current window or entire screen: 1. To activate the current window, press Alt + Print Screen keys.
2. To activate entire screen, press Print Screen key. 3. Click OK (or CLOSE, depending on the screen).
4. Move to a desired document or worksheet by opening the document (START/ DOCUMENTS, etc.).
5. Click the desired cell location in worksheet or location in document. 6. Click EDIT/PASTE; the screen will appear at the desired location.
7. Adjust size and location of screen by dragging, or click FORMAT/PICTURE/ SIZE, etc.
8. Click FILE/PRINT PREVIEW to check the final arrangement.
9. Activate the screen if it alone is to be printed. Make sure screen is not activated if the entire worksheet or document is to be printed.
10. Print as per the usual procedure. See Figures 2.1 and Figure 2.2. 7326_C002.fm Page 5 Tuesday, March 7, 2006 6:17 AM
FIGURE 2.1
Miscellaneous Operations in Excel and Word 7
2.3 Custom Keyboard Setup for Symbols in Word
The following procedure may be used to customize a keyboard setup for symbols: 1. Open new document.
2. Click INSERT/SYMBOL.
3. Select Font: Symbol or any other desired style. 4. Click on the desired symbol.
5. Click Shortcut key.
6. Press alternative keys or combination of keys. 7. Click ASSIGN.
8. Click CLOSE.
9. Repeat for as many symbols and characters as desired. 10. Close to return to document.
The customized keyboard can then be applied to all new documents. The symbol font is shown in Figure 2.3, and a suggested custom setup for shortcut keys is shown in Figure 2.4. For convenient use, we suggest that the setup be saved as Word Symbol Template in any desired file and then sent to the desktop as a shortcut. For use in a shared computer, the template can be saved on a floppy disk and then accessed when needed.
FIGURE 2.3
2.4 Viewing or Printing Column and Row Headings and Gridlines in Excel
To view or print column and row headings and gridlines:
1. Click FILE/PAGE SETUP/SHEET/PRINT, check Gridlines and Row and Column Headings. See Figure 2.5 and 2.6.
2. Click OK.
2.5 Assorted Instructions
For the following functions, the instructions are as follows (some are repeated in the examples):
Plotting of empty cells
TOOLS/OPTIONS/CHART/choose empty cells not plotted, or zero. Listing of recently used Word or Excel files
TOOLS/OPTIONS/GENERAL/choose number to list. Moving and sizing charts and text boxes on a worksheet
To move the entire chart or text box, activate the chart by clicking on CHART AREA, not PLOT AREA, and drag to the new location. Do not drag by side handles.
To resize the chart, activate the chart, click on the corners or side handles until a double arrow appears, then drag to desired proportions.
Miscellaneous Operations in Excel and Word 9 To move the entire text box in small increments, activate the text box. Hold down
the Ctrl button and move in small increments with the arrow keys. Adding or removing fill to cells or text boxes
Activate the object or area, click on the Fill icon in the Drawing toolbar, select Fill color or pattern or No Fill.
Adding or removing line border to text box
Activate the object, click on the Line icon on the Drawing toolbar, and select the desired option.
Changing border or drawing line weights
Activate the object, click on the Line Weight icon on the Drawing toolbar, and make a selection.
Editing charts
Activate the chart. Click CHART/CHART OPTIONS/select from Titles, Axes, Gridlines, Legend, and Data Labels tabs.
Displaying formulas in cells
TOOLS/OPTIONS/VIEW/WINDOW OPTIONS/check Formulas. Adding (or deleting) sheet and page numbers
FILE/PAGE SETUP/HEADER-FOOTER/choose the desired format. Printing portrait or landscape page orientation
FILE/PAGE SETUP/PAGE/choose the desired format. Deleting in Word
Previous word delete: Ctrl + Backspace. Previous line delete: Alt + Backspace. Word forward delete: Ctrl + Delete. Subscripts and superscripts in Word
Subscript: Ctrl + equal sign.
Reverse subscript: Ctrl + equal sign. Superscript: Ctrl + plus sign (using Shift).
Reverse superscript: Ctrl + plus sign (using Shift). Protecting Worksheets
To prevent accidental typing over formulas or objects in a worksheet, it is possible to lock the material in place by clicking TOOLS/PROTECTION/PROTECT SHEET. This action locks all the cell contents in the worksheet. To exclude some cells from the locking process:
1. Activate the cells (or row or column) to be excluded. 7326_C002.fm Page 9 Tuesday, March 7, 2006 6:17 AM
2. Click FORMAT/CELLS/PROTECTION/remove the check mark from Locked. This exclusion must be made before the locking process for the worksheet.
To reverse the protection action, click TOOLS/PROTECTION/UNPROTECT SHEET. FIGURE 2.5 FIGURE 2.6 1 2 3 4 5 6 7 8 9 10 11 A B C D E F G TEXT or FORMULA
To change font type or size for entire worksheet, click box in upper left hand corner between row 1 and column A. Make changes. Then click A1 cell to activate changes.
NOTE: This will only change fonts in cells; it will not change font in a text box like this. That must be changed by clicking box, etc.
Miscellaneous Operations in Excel and Word 11
2.6 Moving Objects in Small Increments (Nudging)
To move an object by small increments: 1. Select the object by clicking.
2. Press the arrow keys to move object in desired direction.
3. Hold down the Ctrl key while pressing the arrow keys to move the object by one-pixel increments.
An alternate procedure, which is more complicated, is to click Draw on the Drawing toolbar and then click the Nudge selection for the particular direction.
2.7 Formatting Objects in Word, Including Wrapping
Charts, graphs, drawing objects, pictures, and text boxes may all be copied to Word from other sources, viz., Excel, and then adjusted in size, position, or wrapped with text. The procedure for making these adjustments is as follows:
1. Activate the object, chart, drawing, or picture by clicking.
2. Click FORMAT/Object, AutoShape, Picture, or Text Box. The dialog window will appear as in Figure 2.7a for AutoShape (Drawing) object.
3. Select the tab of interest. In Figure 2.7a the wrapping tab is selected with a choice of Top and Bottom. The same window with selection of the Size tab is shown in Figure 2.7b, which may be used to adjust the size of the object.
4. For a Picture object, the window appears as in Figure 2.7c, and the opportunity to adjust brightness and contrast is offered. If the picture is imported from digital-photo-editing software, these adjustments will probably have already been made. 5. Changing the position, filling colors, or line color may also be accomplished by
choosing the appropriate tab.
2.8 Formatting Objects in Excel
Drawing objects and pictures may be altered in size in Excel by dragging the edges to the desired size or by first activating the object and then clicking FORMAT/ AutoShape(Drawing Object) or Picture. For pictures, the window of Figure 2.8a will appear, which allows modification of picture size and adjustment of brightness and contrast. These latter factors may have already received attention if the picture is imported from digital-photo-editing software. The dialog window for AutoShape is shown in Figure 2.8b, and it too allows for modification of drawing-object size. This text box may also be formatted and sized by activating and then clicking FORMAT/ Text Box. The dialog window appears as in Figure 2.8c.
Miscellaneous Operations in Excel and Word 13
(a)
(b)
FIGURE 2.8
2.9 Use of Photo-Editing Software in Word, Including Wrapping
Digital-photo-editing software may be employed to edit digital photos, which can subse-quently be copied to a Word document as shown in Figure 2.9. In Figure 2.9a, the digital photo is shown as it was originally recorded. In Figure 2.9b, the photo has been cropped and a texture effect added. In Figure 2.9d, the photo is cropped and adjustments made in contrast and brightness by using the sliders shown in the dialog window of Figure 2.9c. The effects are exaggerated to show in the printing process.
2.10 Copying Cell Formulas: Effect of Relative and Absolute Addresses Copying a cell formula has different results depending on whether absolute cell references are used or not. In cell B4 of Figure 2.10, the formula calls for the square of the value in cell F1. The same result is called for in the formula of cell C4. $F$1 is an absolute cell reference to the value in F1, whereas F1 is called a relative cell reference. The results of copying these two formulas are shown on the worksheet. When B4 is copied to C8, the formula does not change because of the absolute cell reference $F$1. When C4 is copied, an entirely different set of results can be obtained:
(c)
Miscellaneous Operations in Excel and Word 15
1. When C4 is copied to D8, F1 becomes G5 (1 column to the right, thus F becomes G, and 4 rows down, thus row 1 becomes 5).
2. When C4 is copied to E8, F1 becomes H5 (2 columns to the right, thus F becomes H, and 4 rows down, thus row 1 becomes 5).
3. When C4 is copied to E4, F1 becomes H1 (2 columns to right, thus F becomes H, and the row remains the same, so the row number remains 1).
4. A formula may be copied for successive rows or columns as shown in column A. The formula is clicked, then click EDIT/COPY, and then the cell is dragged down for the desired number of rows, followed by pressing Enter. Note how the formula retains the absolute reference but changes the relative cell locations.
5. Moving a formula does not change the cell addresses in the formula. See “Moving, Formulas” under Help/Index for details.
FIGURE 2.9
2.11 Copying Formulas by Dragging the Fill Handle
Many engineering situations arise in which tabulation or plots of a function are needed for uniform increments in the argument of the function. This operation is very easy to perform in Excel by using the Fill Handle and dragging. In Figure 2.11 we show how this is accomplished for the simple function y = x2 in increments of Δx = 0.1 over the range 1 < x < 2.
The start of the range for x is entered in cell A4 as 1. Then, the next value of x is entered in cell A5 as 1.1. Cells A4 and A5 are activated, producing the situation shown in Figure 2.11a. Then, the Fill Handle is clicked and dragged down for the desired number of increments, producing the result shown in Figure 2.11b.
The formula for x2 is entered in cell B4 as shown in Figure 2.11a. This cell is activated and the Fill Handle dragged down to copy the formula as shown in Figure 2.11b. In this figure, all the formulas are retained in view by clicking TOOLS/OPTIONS/VIEW/For-mulas. Removing the check from the Formulas box produces the final numerical results shown in Figure 2.11c.Display of the formulas is not necessary in the drag process, and the result in Figure 2.11c can be produced by drag-copying cell B4 while in the numerical display mode.
Copying of cell formulas could also be accomplished by activating the cell, clicking EDIT/COPY, and then dragging for the number of cells desired, followed by Enter. The use of the Fill Handle is easier.
Graphs of the functions may be constructed as described in Chapter 3.
2.12 Shortcut for Changing the Status of Cell Addresses
The F4 key may be used to quickly change the absolute or relative status of a cell address. The procedure as applied to the formula in cell B4 of Figure 2.11 is as follows:
FIGURE 2.10 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 A B C D E =$F$1*(4.2*B1^2-5.6*C1) =$F$1*(4.2*B2^2-5.6*C2) =$F$1*(4.2*B3^2-5.6*C3) =$F$1^2 =F1^2 =H1^2 =$F$1*(4.2*B4^2-5.6*C4) =$F$1*(4.2*B5^2-5.6*C5) =$F$1*(4.2*B6^2-5.6*C6) =$F$1*(4.2*B7^2-5.6*C7) =$F$1^2 =G5^2 =H5^2 =$F$1*(4.2*B8^2-5.6*C8) 1 2 3
4. Formula copied for multiple rows by dragging
Miscellaneous Operations in Excel and Word 17
1. Activate cell B4 containing the formula. 2. Activate the A4 cell reference in the formula.
3. Press the F4 key until the desired type of cell reference is obtained. Repeated pressing of the F4 key will cycle through the four possible cell references as A4, $A4, A$4, and $A$4.
4. Press Enter.
2.13 Switching and Copying Columns or Rows, and Changing Rows to Columns or Columns to Rows
Sometimes the position of data in a column or row needs to be switched in order to provide for a different orientation on a chart. When using x-y scatter graphs (Section 3.3), Excel treats the left column or top row of data as the x or abscissa coordinate. The position of the column on the worksheet may be changed by copying one of the columns (or rows) to a new location by the following procedure. Pivot tables may also be employed for ordering the presentation of data, as described in Section 9.3.
1. Select (activate) the columns or rows of cells to be copied. 2. Click EDIT/COPY.
3. Click the cell that will be the top cell of the new column or the left cell of the new row.
FIGURE 2.11
4. Click EDIT/PASTE SPECIAL. The window shown in Figure 2.12 will appear. Under Paste, choose Values if new formulas are not to be created. See the earlier discussion on relative and absolute cell locations.
5. If a column is to be switched to a row or a row switched to a column, click Transpose.
6. Click OK.
2.14 Built-In Functions in Excel
Excel has hundreds of built-in functions that may be accessed by the function name followed by the syntax that applies to that function. The reader who needs to apply these functions in worksheet formulas will usually be aware of the abbreviations assigned to the functions.
For a listing of functions, consult Help and obtain further details by entering such items as the following:
Engineering functions
Math and trigonometry functions Statistical functions
Or, for business users, Financial functions
For later reference, the user may wish to print out the lists of functions. A complete description of each function can be called up by the function name from Help, which will
Miscellaneous Operations in Excel and Word 19
display all syntax requirements. Some examples are given in Table 2.1. Financial functions are discussed in Chapter 7.
2.15 Creating Single-Variable Tables Using the DATA/TABLE Command Copying formulas in successive cells is one way to create a data table as described in Section 2.11. An alternative, and sometimes simpler, procedure makes use of the DATA/ TABLE command with the following steps:
TABLE 2.1
Abbreviated List of Built-In Functions
Function Syntax Arguments
Absolute value ABS(x) x = real number
Arccosine ACOS(x) −1 < x < + 1, returns −π/2 to π/2
Hyperbolic arccosine ACOSH(x) x = number
Arcsine ASIN(x) −1 < x < + 1, returns −π/2 to π/2
Hyperbolic arcsine ASINH(x) x = number
Arctangent ATAN(x) x = number, returns −π/2 to π/2
Hyperbolic arctangent ATANH(x) −1 < x < + 1
Bessel Function Jn(x) BESSELJ(n,x) n = order(integer), x = number
Bessel function Yn(x) BESSELY(n,x) n = order(integer), x = number
Cosine COS(x) x = angle in radians
Hyperbolic cosine COSH(x) x = number
Error function ERF(x) x ≥ 0, returns value of 0 to 1.0
Exponential EXP(x) x = number, returns ex
Natural logarithm LN(x) x > 0
Logarithm to base b LOG(x,b) x > 0, b = base (default b = 10)
Logarithm to base 10 LOG10(x) x > 0
Matrix inversion MINVERSE See Section 5.5
Matrix multiplication MMULT See Section 5.5
Pi PI( ) Returns numerical value of π
Sine SIN(x) x = angle in radians
Hyperbolic sine SINH(x) x = number
Square root (positive) SQRT(x) x ≥ 0
Square root of Pi SQRTPI Returns π1/2
Summation SUM(x1,x2, … 30 values) Sum of 30 values or array
Sum of squares SUMSQ(x1,x2, … 30 values, or array) Sum of squares of 30 values or
array
Tangent TAN(x) x = angle in radians
Hyperbolic tangent TANH(x) x = number
Arithmetic average AVERAGE(x1,x2, … 30 values) Average of 30 values or array
Sum of squares of deviations from arithmetic mean
DEVSQ(x1,x2, … 30 values, or array) = ∑(xi− xmean)2
xmean = arithmetic mean
Maximum, median, or minimum
MAX( ), MEDIAN( ) or MIN(x1, x2, … 30 values)
Returns values for 30 values or array
Normal distributions NORMDIST, NORMINV, NORMSDIST, NORMSINV
See Section 6.5
R-squared RSQ See Section 3.8
Sample standard deviation STDEV(x1,x2, … 30 values) Returns sample standard
deviation of 30 values or array Population standard deviation STDEVP(x1,x2, … 30 values) Returns population standard
deviation of 30 values or array.
Financial functions See Chapter 7
1. Set aside rows or columns in a worksheet for labeling variables.
2. Choose a column to contain the numerical values of the input variables. Insert input values in this column. Increments may be set as described in Section 2.11 or by direct entry.
3. Type the formula to be calculated in the column to the right of the column in step 2 and one row above. The formula should be written in terms of an input cell that is located apart from the body of cells that will house the final table. Selection of the input cell is rather arbitrary. The only requirement is that it must be located outside the cell range assigned for the table.
4. Select (activate) cells containing values of the input variable, formula to be eval-uated, and cells that will contain the results.
5. Click DATA/TABLE.
6. Enter the input cell location for a column table in the dialog window. 7. Click OK. The table will appear.
8. If additional result functions need to be evaluated, enter the formulas for each in the cells adjacent to the formulas in step 3, and repeat steps 5 through 7.
9. The procedure may also be executed using rows for data input. In this case, the formulas are typed in the column to the left of the initial value and one cell below.
Example 2.1: Construction of Table for Simple Functions of a Single Variable
We will construct a table for the following three functions of x over the range 0 < x < 5 in increments of 1.0:
y1 = x + 1 y2 = x + 2 y3 = x + 3
The worksheet is shown in Figure 2.13. Cell A2 is used for the x label. The three formulas for the functions are listed in cells B2, B3, and B4, respectively, and the cell range to house the table is A2:D7. An input cell apart from this region is chosen as F2 and the formulas written in terms of this cell as shown in Figure 2.13a. The formulas are displayed on the worksheet by clicking TOOLS/OPTIONS/VIEW/Formulas. The table area is selected as shown in Figure 2.13b and DATA/TABLE clicked, producing the window shown at Figure 2.13c. Input column cell F2 is inserted in this window and OK is clicked. The final formula table also appears as shown in Figure 2.13b. Removing the formulas from view produces the final table as shown in Figure 2.13d.
A scatter chart of the data table may be constructed using Chart Wizard and will appear as shown below the table in Figure 2.13d. Appropriate titles and nomenclature may be added to the final data table and chart as desired.
Miscellaneous Operations in Excel and Word 21
2.16 Creating Two-Variable Tables Using the DATA/TABLE Command Two-variable tables may be constructed with a procedure similar to that employed for one-variable tables. Two examples of formulas involving two input variables are:
z = (x2 + y2)1/2 and
z = (x + 1)(y + 2)
FIGURE 2.13
The procedure for creating the data table is as follows:
1. Select two input cells apart from the block of cells that will house the data table. These cells will serve as the variables in the formulas.
2. Choose a cell on the worksheet and enter the formula for the function in terms of the two input cells.
3. Enter a list of input values for one variable in the same column as the formula, but below the formula.
4. Enter a list of input values for the second variable in the same row containing the formula, but to the right of the formula.
5. Select (click and drag) the range of cells that are to contain the formula, input values of both variables, and data table.
6. Click DATA/TABLE.
7. The dialog window will appear. Enter the row and column input cells used in writing the formula in step 2 and those corresponding to the input values entered in steps 3 and 4.
8. Click OK. The table will appear.
Example 2.2: Two-Variable Data Table
To illustrate the method, we will construct a data table for the function: z = (x2 + y2)1/2 for 1 < x < 5 and 1 < y < 5 (d) FIGURE 2.13 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 A B C D E F 2 3 4 5 3 4 5 6 4 5 6 7 5 6 7 8 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5
Miscellaneous Operations in Excel and Word 23 Increments of x and y are chosen as 1.0. The worksheet is set up as shown in Figure
2.14. Cells A1 and A2 are chosen as input cells for x and y, respectively, and the formula for z is written in cell C3 as shown in Figure 2.14a. The C column is chosen for x, with the five input values entered. Likewise, row 3 is chosen for y, with 5 corresponding input values. Smaller or larger increments in x and y could be chosen and entered either directly or as described in Section 2.11.
Next, the table range C3:H8 is selected by click-dragging. DATA/TABLE is clicked and A1 entered as the input cell for y along with cell A2 as the input cell for x. The entries are shown in the window of Figure 2.14b. OK is clicked and the data table appears as shown in Figure 2.14c, with the formulas displayed. Removal of the formulas gives the final table shown in Figure 2.15.
A 3-D wire surface chart of the function is displayed below the final data table. Both the final table and chart may be titled and formatted as needed for the final presentation.
FIGURE 2.14
Problems
2.1. In Excel, click TOOLS/OPTIONS. Copy the Options window to a Word document by pressing Alt + Print Screen, then opening a new Word document, followed by EDIT/PASTE. Adjust size of inserted window by clicking FORMAT/OBJECT/ SIZE. Move the window to new positions by pressing cursor arrows or by drag-ging.
2.2. Customize the keyboard in Word as shown in Section 2.3 and type the following equations:
A = x0/{[1 − (ω/ωn)2] + [2(ω/ωn)(c/cc)]2}1/2 θ/θ∞ = e−(hA/ρcV)τ
2.3. Open a new Excel worksheet. Type in a few comments or equations. Change the font for the worksheet to a different type and size (make your own selections).
FIGURE 2.15 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 C D E F 1 1.414214 2.236068 3.162278 4.123106 5.09902 2 2.236068 2.828427 3.605551 4.472136 5.385165 3 3.162278 3.605551 4.242641 5 5.830952 4 4.123106 4.472136 5 5.656854 6.403124 5 5.09902 5.385165 5.830952 6.403124 7.071068 1 2 3 4 5 S1 S3 S5 0 2 4 6 8
Miscellaneous Operations in Excel and Word 25 2.4. If convenient to do so, insert a digital photo obtained either from a digital camera
or scanner into a Word and Excel sheet. Edit the photo using photo-editing soft-ware available to you
2.5. Perform the copying operations shown in Figure 2.10. 2.6. Perform the drag-copying process shown in Figure 2.11. 2.7. Open an Excel worksheet and evaluate the following functions:
e−0.5 Cosh(2.3) Tanh−1(0.5) Numerical value of π
2.8. Using the DATA/TABLE command, construct a table of values of the function sin(nx) for n = 1, 2, and 3 and x = 1 to 1.5. Choose appropriate increments in x for the calculations.
2.9. Using the DATA/TABLE command, construct a table of the three functions y = x1/2
y = x + 0.3 y = x2 over the range 0 < x < 5.
2.10. Using the DATA/TABLE command, construct a table of the Bessel function J(n,x) for n = 1, 2, 3 and 0 < x < 3. Choose increments in x as desired.
2.11. Using the EDIT/COPY command, transpose the x-y column data in columns A and B into the row data shown:
2.12. Enter the following values in an Excel worksheet:
1, 1.2, 1.1, 1.05, 0.96, 0.95, 1.06, 1.15, 1.21, 0.94, 1.01 and using built-in functions evaluate:
y = {[∑(x – xm)2]/n}1/2 1 2 3 4 5 6 A B C D E F G H I x y x 1 2 3 1 2 y 2 3 4 2 3 3 4 4 5 5 6
where
xm = (∑x)/n and n = number of values
2.13. Compare the result of Problem 2.12 with the application of the worksheet functions STDEV and STDEVP to the data points.
27
3
Charts and Graphs
3.1 Introduction
The preparation, publication, and presentation of graphs and charts represent a significant portion of engineering practice. In Excel, a majority of such displays are given the desig-nation of x-y scatter graphs. For this reason, we will concentrate our discussion on that type of graphical presentation. Bar graphs and column graphs are discussed briefly in Section 3.19, and surface (3-D) charts are discussed in Section 3.21. Obviously, the inter-ested reader may explore these other graphical possibilities.
The display and discussion in Section 3.3 categorizes the five types of scatter graphs available in Excel, along with a general statement of an application for each type. Examples of data presentations using scatter charts are given in this chapter as well as in the application sections of other chapters. Treatment of math and other symbols in graphical displays is discussed in this chapter and in sections of Chapter 4 connected with embedded drawing objects. An important part of the present chapter is concerned with the display and correlation of data using trendlines and the built-in least-squares analysis features of Excel. Examples are given for correlation equations using linear, power, and exponential functions.
Section 3.20 discusses formatting and cosmetic adjustments that are available for the various graphs, and Section 3.22 offers a suggestion for default settings of scatter graphs, which are normally the most common type of display.
As we have mentioned before, many of the sections are essentially self-contained and can be studied as stand-alone items. To provide for this capability, charts in some sections have been embedded with text along with a reduction in type size. As appropriate, cross-references are made to related sections of this and other chapters.
3.2 Moving Dialog Windows
A brief set of data is shown in the screen of Figure 3.1a. INSERT/CHART is clicked, producing the Chart Wizard dialog box shown in Figure 3.1b. The dialog box obscures the data, but may be moved out of the way by clicking on the title bar and dragging it to a new position as shown in Figure 3.1c.
3.3 Excel Chart Wizard Window Showing Choice of x-y Scatter Charts Different data series may be designated by various shape or styles of data markers, as shown in Figure 3.2:
1. Data plotted with data markers but no connecting line segments: this type of plot is employed for experimental data with considerable scatter but may be fitted with a computed trendline.
2. Data plotted with the data markers connected by smoothed lines as determined by the computer: this type of plot is employed for either calculated points or experimental data with rather smooth variations from point to point.
3. Data plotted as in item 2 but without data markers: this type of plot is most frequently employed for calculated curves and is almost never used for presen-tation of experimental data because the data points are not displayed.
Charts and Graphs 29
4. Data points plotted without markers but with points connected by straight line segments: this type of plot is frequently used when points are obtained from a numerical analysis that assumes linear behavior between calculated points. 5. The same as item 4, but with points designated by data markers: this type of plot
is sometimes employed for calibration curves in which linear interpolation between data points is assumed.
3.4 Selecting and Adding Data for x-y Scatter Charts
In setting up scatter charts, the x-axis will be either the left column or top row of data, depending on whether columns or rows are chosen for the data series. The y-axis will be the remaining columns or rows. After the chart is established, the addition of data will be as new y-axes regardless of their location relative to the column or row taken as the x-axis. The data selection procedure is as follows:
1. Click-drag cells for the x-axis while holding down the Ctrl button.
FIGURE 3.2
2. Release click on the mouse, move the pointer to the column for the first y-axis data while still holding down the Ctrl button and then click-drag cells for the first y-axis data.
3. Continue this procedure for successive y-axes data, still holding down the Ctrl button.
3.5 Changing and Adding Data for Charts Using the SOURCE DATA
Command
Data for charts can be added or changed as follows:
1. Activate the chart. For a separate chart sheet, click the tab for the chart sheet. 2. Click CHART/SOURCE DATA.
3. Select the worksheet containing data to be added.
4. To view worksheet cells and data, click the collapse button at the right end of DATA RANGE dialog box as shown on the screen.
5. Select the replacement data cells to be added as described in Section 3.4. These replacement cells may be chosen to include or omit the old data cells. To simply add a data series while retaining the old data, see the description of the ADD DATA command in Section 3.6.
6. Click the collapse (expand) button (see Figure 3.3) again at right end of the dialog box displayed at top of worksheet. The SOURCE DATA dialog box will reappear. 7. Click OK, which will install the new replacement data on the chart.
8. Make cosmetic and other adjustments to the chart as needed.
3.6 Adding Data to Charts Using the ADD DATA Command
Data can be added to charts as follows:
1. Activate the chart. For a separate chart sheet, click the tab for the chart sheet. 2. Click CHART/ADD DATA.
3. Select the worksheet containing data to be added.
4. If the ADD DATA dialog box obscures the view of data, click the collapse button at the right end of DATA RANGE text box or drag the dialog box into a new location away from the data columns.
5. Select the data cells to be added as the new y-axis data by clicking and dragging as described in Section 3.4.
6. Click the collapse (expand) button again at right end of the dialog box. The ADD DATA dialog box will reappear.
Charts and Graphs 31
7. Click OK, which will install new data on the chart. Note that if a replacement of all the chart data is desired, the procedure using the SOURCE DATA command is followed.
8. Make cosmetic and other adjustments to the chart as needed.
3.7 Adding Trendlines and Correlation Equations to Scatter Charts Click on the chart to activate it. Then click CHART/ADD TRENDLINE/TYPE/select the type of trendline. Then, click the OPTIONS tab to select Display Equation on Chart and R-squared. Adjust the location, size, and font of the equation and R2 on the chart as needed. See Section 3.9 and Section 3.10 for specific examples.
3.8 Equation for R2
The equation employed by Excel for calculation of R2 in the trendline fits is given by R2 = [n∑x
iyi− (∑xi)(∑yi)]2/[n∑xi2− (∑xi)2][n∑yi2− (∑yi)2] (3.1) FIGURE 3.3
R2 is called the coefficient of determination, whereas R is called the correlation coefficient. This particular equation expresses what is called the Pearson correlation coefficient, which
is callable by the PEARSON worksheet function. A calculation of R2 separate from the
trendline determinations may also be obtained by calling either the worksheet function RSQ or PEARSON. See Help/Index for the proper syntax for execution of these functions. The R2 displayed with the graphical trendline is expressed by
where SSE is the sum of the squares of the error from the correlating trendline, or SSE = ∑(yi− yic)2
and SST is the sum of squares of deviations from the arithmetic mean, ymean = (∑yi)/n, and may be expressed in the form
SST = (∑yi2) − (∑yi)2/n
where yic represents the value of y on the linear trendline fit. For a perfect match between
data points yi and the trendline, R2 = 1.0. For exponential, power, and polynomial
trendlines Excel uses a transformed regression model. Note that these calculations are equivalent to using a population standard deviation instead of a sample standard devia-tion. Still, a perfect fit will be obtained when yi = yic. SST may also be calculated in terms of the population standard deviation function STDEVP through the relation
SST = n×[STDEVP(yi)]2
3.9 Correlation of Experimental Data with Power Relation
A number of physical phenomena follow a power law relation between variables. Exam-ples are
Nu = CRen
for forced convection and
Nu = C(GrPr)m
for free convection heat transfer. The general power law relation has the form
y = axb (3.2)
Taking the logarithm of both sides of the equation gives
Log y = Log a + bLog x (3.3)
R SSE
SST 2= −
Charts and Graphs 33 which is a linear relation between Log y and Log x. When x and y are plotted on a log–log graph, b will be the slope of the line and Log a will be the intercept at x = 1.0 (see Section 3.10). When trying to fit experimental data with the power law relation, scatter in the data will normally occur and a least-squares analysis should be employed to determine the best fit. A correlation coefficient may also be calculated to indicate the goodness of fit.
Excel may be used to (1) display the data on a log–log plot, (2) calculate the values of the constants a and b using a least-squares analysis, (3) display the resultant correlation trendline, and (4) display the correlation equations on the plot.
The Excel procedure is as follows:
1. List the data in two columns. Label columns as appropriate. Consider discarding any data points that appear to be in gross error. This step may be deferred until after the data plot is obtained. See step 7.
2. Select the data to be plotted.
3. Click Chart Wizard or INSERT/CHART.
a. Select the scatter chart without connecting line segments (type 1 chart). b. Select the data range of cells.
c. Input the chart title and values for x- and y-axes. Under AXES click values for both x and y. Under GRIDLINES, select as appropriate. Under DATA LABELS, None is probably appropriate.
d. Click FINISH. The chart will appear.
4. Click the chart to be edited. Click either x- or y-value axis — FORMAT AXIS will appear. Under SCALE, click Logarithmic Scale. Select the minimum and maximum values for the scale (nonzero values for log scale) and the value for crossing the other axes. Repeat for other value axes. Then repeat FORMAT AXIS/PATTERNS to set tick marks for both major and minor axes, with labels next to the axis. FONT may also be adjusted at this point if desired.
5. After step 4 is completed, click the chart again. Then click CHART/ADD TRENDLINE/TYPE/POWER. Click OK. Then click the chart again, followed by CHART/ADD TRENDLINE/OPTIONS, and click Display Equation on Chart and Display R-squared Value on Chart. Click OK.
6. Inspect the final graph. Does the trendline appear to represent the data? If not, the power relation may not be correct for the physical application. This step is important! A correlation equation should NEVER be accepted without visual confirmation of agreement with the experimental data points. The computer will perform the trendline analysis as instructed, but cannot assure that the functional form selected is correct.
7. Examine the individual data points in the final plot. If some points appear to be widely scattered from the main body of data, consult the original data sheets for possible errors or erratic behavior in the experiment. Consider eliminating suspi-cious points.
8. If a decision is made to eliminate points as discussed in step 7, delete the respective entries in the data cells. The deletions will appear on the chart, and a new trendline and correlation equation will be displayed, based on the remaining data points. 9. Make final adjustments to the cosmetics of the chart, fonts, titles, etc. If a large
number of data points are involved, some adjustment of the size of data markers or line width for the trendline may be in order.
Two examples of power law correlation plots are shown in Figure 3.4. One has a rather good fit, whereas the other has a lot of scatter. In the latter case, one should suspect that either the data are bad or that a power law relation does not fit the physical situation.
3.10 Use of Logarithmic Scales
The data are first plotted on a linear graph as shown in Figure 3.5a, indicating a decaying exponential or inverse power relation. Logarithmic scales are then selected by clicking on each value axis, then click FORMAT AXIS/SCALE/check Logarithmic scale as shown in the screen of Figure 3.5b. The result is the graph in Figure 3.5c with the x-axis in an inconvenient position at the top of the graph. The y-axis value is again selected. Then click FORMAT AXIS/SCALE as shown in Figure 3.5d. The “Value (X) axis crosses at:” is changed to 0.01 (the lower edge of the graph), and the result is shown in the graph of Figure 3.5e. Next, a trendline is added by clicking on the data, then click GRAPH/ADD TRENDLINE/TYPE/highlight Power as shown in the screen of Figure 3.5f. Then, select the OPTIONS tab and check “Display equation on chart” and “Display R-squared value on chart” as shown in the screen of Figure 3.5h. The result is the graph in Figure 3.5g. A power relation does fit the data.
FIGURE 3.4
x y
1 1.5
2
3
3
5
4 15
5 27
6 34
7 48
y = 1.0554x1.8929 R2= 0.9498 1 10 100 0x y
1 20
2 6
3 56
4 25
5 42
6 12
7 87
8 99
y = 11.103x0.7598 R2= 0.299 1 10 100 0 (a) (b)Charts and Graphs 35
3.11 Correlation with Exponential Functions
The exponential function y = exp(−0.1x) is tabulated and shown first as a linear plot in Figure 3.6a with a linear trendline fit, which obviously does not fit. Second, a linear plot with exponential trendline fit is shown in Figure 3.6b with perfect correlation. Third, the function is plotted on a semilog graph that displays the function as a straight line in Figure 3.6c. Again, an exponential trendline is fitted with perfect correlation. Inspection of the visual display is needed to evaluate the trendline fit. For comparison, the final two plots of Figure 3.6d and Figure 3.6e show fits of second- and third-degree polynomials. The
FIGURE 3.5
third-degree one shows a perfect correlation. Polynomials may frequently be employed to obtain a good fit when the functional form is uncertain.
3.12 Use of Different Scatter Graphs for the Same Data
Figure 3.7 shows six scatter plots of a set of hypothetical experimental data displayed in the upper-left corner of the sheet. Figure 3.7a is a type 1 scatter graph, Figure 3.7b is a type 5 chart, and Figure 3.7c is a type 3 chart — all plotted with linear scales on both axes
Charts and Graphs 37
(see Section 3.3). Figure 3.7d, Figure 3.7e, and Figure 3.7f are the same types of plots, but with logarithmic scales on the axes (see Section 3.10). From the graph in Figure 3.7d, it can be seen that the data fall approximately on a straight line so a power law relation might be anticipated, and the corresponding trendline inserted as shown along with the correlation equation and value of R2 (see Section 3.9).
Inspecting the data plot in Figure 3.8a, four points are circled as shown in Figure 3.8a along with the first data point indicated by the arrow shown in Figure 3.8d of that page. These points appear out of place and are, hence, suspect. The first data point looks particularly odd. If these five points are eliminated as shown in Figure 3.9, a much better correlation results. FIGURE 3.6 (a c) exp(-0.1x) 1 0.9048374 0.8187308 0.7408182 0.67032 0.6065307 0.5488116 0.4965853 0.449329 0.4065697 0.3678794 0.3328711 0.3011942 0.2231302 0.1353353
Linear Chart-Linear Trendline
y = -0.0443x + 0.8674 R2= 0.9194 0 0.2 0.4 0.6 0.8 1 1.2 0 5 10 x 15 20 25 y = exp(-0.1x)
Semi-Log Chart-Exponential Trendline
y = e-0.1x R2= 1 0.1 1 0 5 10 15 20 25 x y = exp(0.1x) (b)
Linear Chart-Exponential Trendline
y = e-0.1x R2= 1 0 0.2 0.4 0.6 0.8 1 1.2 0 5 10 x 15 20 25 y = exp(-0.1x) (d e)
Second Degree Polynomial
y = 0.002x2- 0.0818x + 0.9777 R2= 0.9978 0 0.2 0.4 0.6 0.8 1 1.2 0 5 10 15 20 25 x y = exp(-0.1x)
Third Degree Polynomial
y = -7E-05x3+ 0.004x2- 0.0965x + 0.9977 R2= 1 0 0.2 0.4 0.6 0.8 1 1.2 0 5 10 15 20 25 x y = exp(-0.1x)
FIGURE 3.7 (a) x y 1 0.1 1.2 1.4 1.3 1.5 1.5 2.5 1.6 2 2 4.2 2.1 4.1 2.4 15 2.9 7 3.1 9.6 3.5 11.2 3.7 25 4.1 15 4.2 14.9 5.3 26 5.4 25.3 5.9 21 6.3 29 7 65 7.2 52 7.6 65 8.1 72 0 10 20 30 40 50 60 70 80 0 2 4 6 8 10 (d y = 0.6523x2.2761 R2= 0.881 0.1 1 10 100 0 0 10 20 30 40 50 60 70 80 0 2 4 6 (b 0 c) 0 10 20 30 40 50 60 70 80 0 2 4 6 0 e) 0.1 0.1 1 10 100 1 10 (f) 1 10 100 0
Charts and Graphs 39 FIGURE 3.8 b) 0 10 20 30 40 50 60 70 80 0 2 4 6 0 e) 0.1 1 10 100 0 x y 1 0.1 1.2 1.4 1.3 1.5 1.5 2.5 1.6 2 2 4.2 2.1 4.1 2.4 15 2.9 7 3.1 9.6 3.5 11.2 3.7 25 4.1 15 4.2 14.9 5.3 26 5.4 25.3 5.9 21 6.3 29 7 65 7.2 52 7.6 65 8.1 72 (a 0 10 20 30 40 50 60 70 80 0 2 4 6 8 10 (d 0.1 1 10 100 0 (c) 0 10 20 30 40 50 60 70 80 0 2 4 6 8 10 (f) 0.1 1 10 100 0
FIGURE 3.9 0 10 20 30 40 50 60 70 80 0 2 4 6 8 10 (e) 0.1 1 10 100 0 x y 0.1 1.2 1.4 1.3 1.5 1.5 2.5 1.6 2 2 4.2 2.1 4.1 15 2.9 7 3.1 9.6 3.5 11.2 25 4.1 15 4.2 14.9 5.3 26 5.4 25.3 21 6.3 29 65 7.2 52 7.6 65 8.1 72 0 10 20 30 40 50 60 70 80 0 2 4 6 8 10 0 10 20 30 40 50 60 70 80 0 2 4 6 0 (f) 0.1 1 10 100 0 (d)
Charts and Graphs 41
3.12.1 Observations
The charts in Figure 3.7c and Figure 3.7f do not convey much information about the data and do not give a reader any hint of what might be going on with the experiment. Looking at the other charts would certainly not give one the impression of a smooth variation of y as a function of x. The charts in Figure 3.7b and Figure 3.7e are somewhat better, but those in Figure 3.7a and Figure 3.7d give the best impression of the scatter of data. The chart in Figure 3.7d, because it indicates that the data are approximately on a straight line in a log–log plot, gives the clue that a power relation may apply if one deletes the first data point, which appears totally skewed. As we have stated before, one should never leave out the data markers when plotting experimental results. In some other chart exam-ples, involving plots of calculated points, we will see that the use of smooth curves as in Figure 3.9c and Figure 3.9f will be quite appropriate.
3.13 Plot of a Function of Two Variables with Different Chart Types This example illustrates how it is possible to present the plot of a function or data in different chart types to convey somewhat different impressions of the function. The Bessel function Jn (x) is chosen for presentation because of its attractive appearance as a damped sine wave. The function is callable in Excel as BESSELJ(x,n). The worksheet is set up as shown in Figure 3.10, with column A listing the values of the argument x to be incremented using Dx selected in cell H3. These increments may be selected as coarse or fine as desired. Columns B through F compute the Bessel functions as a function of the argument x and orders n = 0 to 4. The formulas are copied for as many rows as needed for the plot. In Figure 3.10, the copying is shown for just a few rows. Also shown is a printout of a few of the numerical values of the functions.
The different types of charts selected for presentation are shown in Figure 3.11a through Figure 3.11f. The chart in Figure 3.11a is a typical type 3 scatter graph with smooth curves
FIGURE 3.10 1 2 3 4 5 6 7 8 A B C D E F G H x J(x,0) J(x,1) J(x,2) J(x,3) J(x,4 x
0 =BESSELJ(A3,0) =BESSELJ(A3,1) =BESSELJ(A3,2) =BESSELJ(A3,3) =BESSELJ(A3,4) 0.3 =A3+$H$3 =BESSELJ(A4,0) =BESSELJ(A4,1) =BESSELJ(A4,2) =BESSELJ(A4,3) =BESSELJ(A4,4)
=A4+$H$3 =BESSELJ(A5,0) =BESSELJ(A5,1) =BESSELJ(A5,2) =BESSELJ(A5,3) =BESSELJ(A5,4) =A5+$H$3 =BESSELJ(A6,0) =BESSELJ(A6,1) =BESSELJ(A6,2) =BESSELJ(A6,3) =BESSELJ(A6,4) =A6+$H$3 =BESSELJ(A7,0) =BESSELJ(A7,1) =BESSELJ(A7,2) =BESSELJ(A7,3) =BESSELJ(A7,4) =A7+$H$3 =BESSELJ(A8,0) =BESSELJ(A8,1) =BESSELJ(A8,2) =BESSELJ(A8,3) =BESSELJ(A8,4)
1 2 3 4 5 6 7 8 9 A B C D E F x J(x,0) J(x,1) J(x,2) J(x,3) J(x,4) Dx 0 1 0 0 0 0 0.3 0.3 0.97763 0.14832 0.01117 0.00056 2.1E-05 0.6 0.912 0.2867 0.04367 0.0044 0.00033 0.9 0.80752 0.40595 0.09459 0.01443 0.00164 1.2 0.67113 0.49829 0.15935 0.03287 0.00502 1.5 0.51183 0.55794 0.23209 0.06096 0.01177 1.8 0.33999 0.58152 0.30614 0.0988 0.0232 7326_C003.fm Page 41 Tuesday, March 7, 2006 6:17 AM
