1 Licensed Electrical & Mechanical Engineer [email protected]Engr/Math/Physics 25 MS Excel Tables/Plots Bruce Mayer, PE Licensed Electrical & Mechanical Engineer
2 Learning Goals Construct Formatted Tables in ExcelUse the Cell Formatting Commands Construct Charts and Graphs Comparison Charts → Bar, Col, Radar Analysis Charts → Scatter, Surface Curve Fits → Linear Regression Use tables and graphs as problem solving tools
3 Using Tables & Charts Engineers record and present data in two primary formats: Tables and Graphs
4 Tables in Reports When using Tables in Reports and PresentationsTables should always have: a Title Column headings with brief descriptive names, symbols 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/explained in the body of the text of the document containing the table
5 Table Examples
6 Budgeting Table
7 Table Construction DemoTime For Live Demo Demo_Excel_Table_Build_Start-n-Chk_Sp12.xlsx
8 Charts and Graphs Carefully The Select the TYPE of ChartDifferent Charts Convey Different Info Make Clear and Easy to Read Large Fonts Good Contrast Light-on-Dark or Dark-on-Light Include Legend Unless Info in Title Label All Axes, Including Units Same rules as used for MATLAB plots
9 Charts & Graphs cont Where Appropriate Annotate or Mark points/regions of Interest with Arrows, Ovals, or Text There are 11 different chart types in Microsoft Excel (and several variations of each type) This Covers 99.9% of the Chart Types That Most Engineers will need Consider Next the Criteria for application
10 The 10 MS Excel-2013 Chart Types
11 MS Excel Charts Area ChartAn area chart emphasizes the magnitude of change over time. By displaying the sum of the plotted values, an area chart also shows the relationship of parts to a whole.
12 MS Excel Charts Bar ChartA bar chart illustrates comparisons among individual items. Categories are organized vertically, values horizontally, to focus on comparing values and to place LESS emphasis on time. Stacked bar charts show the relationship of individual items to the whole.
13 MS Excel Charts Column ChartA column chart shows data changes over a period of time or illustrates comparisons among items. Categories are organized horizontally, values vertically, to emphasize variation over time. Stacked column charts show the relationship of individual items to the whole.
14 MS Excel Charts Line ChartA line chart shows trends in data at equal intervals. Although line charts are similar to area charts, line charts emphasize time flow and the rate of change (Kinks), rather than the amount of change or the magnitude of values.
15 MS Excel Charts Pie ChartA pie chart shows the proportional size of items that make up a data series to the sum of the items. It always shows only one data series and is useful when you want to emphasize ONE significant element PieChart of types of nuts in a can of “Mixxed Nuts”
16 MS Excel Charts XY (Scatter) ChartAn xy (scatter) chart either shows the relationships among the numeric values in several data series or plots two groups of numbers as one series of xy coordinates. It shows uneven intervals, or clusters, of data and is commonly used for scientific data. Used MANY Times in MATLAB
17 MS Excel Charts Radar ChartIn a radar chart, each category has its own value axis radiating from the center point. Lines connect all the values in the same series. A radar chart compares the aggregate values of a number of data series.
18 MS Excel Charts Surface ChartA 3D surface chart is useful when you want to find optimum combinations between two sets of data. As in a topographic map, colors and patterns indicate areas that are in the same range of values.
19 MS Excel Charts Stock ChartThe stock chart is often used to illustrate stock prices. This chart can also be used for scientific data, for example, to indicate temperature changes
20 MS Excel Charts Cone, Cylinder, and Pyramid ChartThe cone, cylinder, and pyramid data markers can lend a dramatic effect to 3-D column and bar charts.
21 Graph Construction DemoGiven Vapor Pressure Data TABLE I: Vapor Pressure Data Vapor pressures (mm Hg) of less than one atmosphere as a function of temperature. (All temperatures are in degrees Celsius) Do in Tutorial Chemical 1 5 10 20 40 60 100 200 400 760 mmHg Sodium, Na 439 511 549 589 633 662 701 758 823 892 oC 1,4-Dioxane C4H8O2 -35.8 -12.8 -1.2 12.0 25.2 33.8 45.1 62.3 81.8 101.1 Acetone (CH3)2CO -59.4 -40.5 -31.1 -20.8 -9.4 -2.0 7.7 22.7 39.5 56.5 Butyric Acid, C4H8O2 25.5 49.8 61.5 74.0 88.0 96.5 108.0 125.5 144.5 163.5 Stannic Chloride, SnCl4 -22.7 -1.0 10.0 22.0 35.2 43.5 54.7 72.0 92.1 113.0 Construct a Scatter Chart to Find the Clapeyron Eqn Constants m & b for Stannic Chloride
22 Chart Construction ResultDemo_Excel_Table-n-Chart_Build_Fa06.xls
23 SLOPE & INTERCEPT Cmds Find Explicitly the Least-Sqs m&b forE25_Slope_Intercept_Demo_1306.xlsx
24 SLOPE & INTERCEPT Cmds E25_Slope_Intercept_Demo_1306.xlsx
25 S&I Cmds vs Plot TrendLineSame Values in TrendLine and WorkSheet Commands
26 RSQ Command Same Values in TrendLine and WorkSheet Commands
27 Excel Plotting All Done for TodayGood Links in
28 Licensed Electrical & Mechanical Engineer [email protected]Engr/Math/Physics 25 Appendix Time For WhtBd Demo Time For Live Demo Bruce Mayer, PE Licensed Electrical & Mechanical Engineer
29 Table Construction Demo (1)Start Merge Cells Vertically Top & Bot Rows 20High Middle Rows 16high Demo_Excel_Table-n-Chart_Build_0511.xls
30 Table Construction Demo (2)Format Alignment of the “Year 3 Cell Result so Far Format Dialog Box
31 Table Construction Demo (3)Set Column Widths 2, 30, 11, 5, 30, 11, 5 Format Top & Bot Cells for VERTICAL Centering Format Fonts All RED Top, Bot, & Lt-End Rows Bold
32 Table Construction Demo (4)Set Border Color to Blue Grid INSIDE blue
33 Table Construction Demo (5)Double-Line Blue Border Outside Double-Line Blue Border Lt-Vert Cell
34 Table Construction Demo (6)Double-Line Blue Border Remainder
35 Table Construction Demo (7)Center Columns: Course No., Units
36 Chart Construction Demo (1)Start Open File Demo_Excel_Table-n-Chart_Build_0511.xls Copy from Table from Slide-22 → Paste into Demo Sheet Need Vertical Data Horizontal table starting in Col-H Copy Table Cells and EDIT → PASTE SPECIAL → transpose Demo_Excel_Table-n-Chart_Build_0511.xls
37 Chart Construction Demo (2)Result after Transpose Paste
38 Chart Construction Demo (3)Archive Data Make Scratch WorkSheet; Xfer horizontal Table to to this sheet Edit Worksheet Adjust Headings Delete Cols other Than SnCl4 Move Remaining to Right Demo_Excel_Table-n-Chart_Build_0511.xls
39 Chart Construction Demo (4)Place in cols A & B 1000/T; T in Kelvins Ln(Pv) After Filling A & B Demo_Excel_Table-n-Chart_Build_0511.xls Formula for Col-B =LN(E8)
40 Chart Construction Demo (5)Now need to Sort the Data with the indep var (1000/T) in ASCENDING ORDER DATA → SORT Demo_Excel_Table-n-Chart_Build_0511.xls
41 Chart Construction Demo (6)Highlight/Select Data to Plot Invoke Chart Wizard Demo_Excel_Table-n-Chart_Build_0511.xls
42 Chart Construction Demo (7)Continue with Chart Wizard Demo_Excel_Table-n-Chart_Build_0511.xls
43 Chart Construction Demo (8)Add X-Grid Lines Insert As NEW Sheet Give Descriptive Name Remove Legend Demo_Excel_Table-n-Chart_Build_0511.xls
44 Chart Construction Demo (9)Chart Wizard Result Change X-axis Scale: 2.5-4 Shorten Title Clear BackGround Lager, Sq Data Markers GridLine & Text Colors
45 Chart Construction Demo (10)Select Chart Area Then Right-Clik Select X-axis, Ther Right-Clik Demo_Excel_Table-n-Chart_Build_0511.xls
46 Chart Construction Demo (11)Select Grid Lines, Rt-Clik, Chg Colors Select Data Series, Rt-Clik, Chg Marker Demo_Excel_Table-n-Chart_Build_0511.xls
47 Chart Construction Demo (12)Position Labels at Page Edges → Stretch-Out Plot Area Demo_Excel_Table-n-Chart_Build_0511.xls
48 Chart Construction Demo (13)Chart Fine-Tuning Result Add TrendLine to find Clapeyron m &b Constants
49 Chart Construction Demo (14)Select Data Series, Rt-Clik, Add TrendLn Select: Linear, Display Parameters Demo_Excel_Table-n-Chart_Build_0511.xls
50 Chart Construction Demo (15)Fine Tune TrendLine Form & Display Demo_Excel_Table-n-Chart_Build_0511.xls Done with Plot; and have determined m & b by Trendline Note that the Fit is Excellent; R2 = 99.92%
51 Chart Construction Demo (16)Calc Error =(G4-E4)/E4 Add Fitted Data to table Demo_Excel_Table-n-Chart_Build_0511.xls Calc Using m & b Analysis of Fit Characteristics Copy & Paste from Chart
52 Put Fitted Data on ChartOn Table: Select & Copy Data On chart: EDIT → PASTE SPECIAL → dialog Box above
53 Chart Construction Demo (17)Fine Tune Two-Variable Display To Make Error Data More Visible Show using SECONDARY Axis at Right Demo_Excel_Table-n-Chart_Build_0511.xls Error Data Series
54 Chart Construction Demo (18)Demo_Excel_Table-n-Chart_Build_0511.xls
55 Chart Construction Demo (19)Fine Tune Two-Axes Display Demo_Excel_Table-n-Chart_Build_0511.xls
56 Chart Construction Demo (20)Q.E.F.
57 Nice Chart
58 Coefficient of CorrelationThe coefficient of correlation is an indication of how well the linear relationship determined by the method of least squares fits the data set. The equation for the coefficient of correlation is:
59 Interpretation of R If R is 0, the points are so scattered that the regression line does not help predict y for a given x. If R is +1 (positive slope) or –1 (negative slope), the points actually lie on a straight line so almost perfect predictions of y for a given x can be made using the regression line.
60 Goodness of Fit R Value Correlation 0.9 to 1.0 Very high positiveModerate positive 0.3 to 0.5 Low positive -0.3 to 0.3 Little, if any -0.5 to -0.3 Low negative -0.7 to -0.5 Moderate negative -0.9 to -0.7 High negative -1.0 to -0.9 Very high negative
61