Bruce Mayer, PE Licensed Electrical & Mechanical Engineer BMayer@ChabotCollege.edu

1. Engr/Math/Physics 25 MS Excel Tables/Plots Bruce Mayer, PE Licensed Electrical & Mechanical EngineerBMayer@ChabotCollege.edu

2. The 11 MS Excel Chart Types

3. Graph Construction Demo • Given 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) http://research.umbc.edu/~lkelly/DAExp.htm • Construct a Scatter Chart to Find the Clapeyron Eqn Constants m & bfor Stannic Chloride

4. Chart Construction Result Demo_Excel_Table-n-Chart_Build_Fa06.xls

6. All Done for Today ExcelPlotting

7. Engr/Math/Physics 25 Appendix Time For WhtBd Demo Time For Live Demo Bruce Mayer, PE Licensed Electrical & Mechanical EngineerBMayer@ChabotCollege.edu

8. Start Open File Demo_Excel_Table-n-Chart_Build_0511.xls Copy from Table from Slide-22 → Paste into Demo Sheet Need Vertical Data Chart Construction Demo (1) • Horizontal table starting in Col-H • Copy Table Cells and EDIT → PASTE SPECIAL → transpose

9. Chart Construction Demo (2) • Result after Transpose Paste

10. 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 Chart Construction Demo (3)

11. Place in cols A & B 1000/T; T in Kelvins Ln(Pv) Chart Construction Demo (4) • After Filling A & B • Formula for Col-B • =LN(E8)

12. Now need to Sort the Data with the indep var (1000/T) in ASCENDING ORDER DATA → SORT Chart Construction Demo (5)

13. Highlight/Select Data to Plot Invoke Chart Wizard Chart Construction Demo (6)

14. Continue with Chart Wizard Chart Construction Demo (7)

15. Add X-Grid Lines Chart Construction Demo (8) • Insert As NEW Sheet • Give Descriptive Name • Remove Legend

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

17. Select Chart Area ThenRight-Clik Chart Construction Demo (10) • Select X-axis, Ther Right-Clik

18. Select Grid Lines, Rt-Clik, Chg Colors Chart Construction Demo (11) • Select Data Series, Rt-Clik, Chg Marker

19. Position Labels at Page Edges → Stretch-Out Plot Area Chart Construction Demo (12)

20. Chart Construction Demo (13) • Chart Fine-Tuning Result • Add TrendLine to find Clapeyron m &b Constants

21. Select Data Series, Rt-Clik, Add TrendLn Chart Construction Demo (14) • Select: Linear, Display Parameters

22. Fine Tune TrendLine Form & Display Chart Construction Demo (15) • Done with Plot; and have determined m & b by Trendline • Note that the Fit is Excellent; R2 = 99.92%

23. Add Fitted Data to table Chart Construction Demo (16) Calc Error=(G4-E4)/E4 Calc Using m & b Analysis of Fit Characteristics Copy & Paste from Chart

24. Put Fitted Data on Chart • On Table: Select & Copy Data • On chart: EDIT → PASTE SPECIAL → dialog Box above

25. Fine Tune Two-Variable Display Chart Construction Demo (17) • To Make Error Data More Visible Show using SECONDARY Axis at Right Error Data Series

26. Chart Construction Demo (18)

27. Fine Tune Two-Axes Display Chart Construction Demo (19)

28. Chart Construction Demo (20) Q.E.F.

29. Nice Chart

30. Coefficient of Correlation • The 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:

31. Interpretation of R • If Ris 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.

32. Goodness of Fit