Engr/Math/Physics 25
1 / 63

Bruce Mayer, PE Licensed Electrical & Mechanical Engineer [email protected] - PowerPoint PPT Presentation

  • Uploaded on

Engr/Math/Physics 25. MS Excel Tables/Plots. Bruce Mayer, PE Licensed Electrical & Mechanical Engineer [email protected] Learning Goals. Construct Formatted Tables in Excel Use the Cell Formatting Commands Construct Charts and Graphs Comparison Charts → Bar, Col, Radar

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' Bruce Mayer, PE Licensed Electrical & Mechanical Engineer [email protected]' - chapa

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Engr/Math/Physics 25

MS Excel


Bruce Mayer, PE

Licensed Electrical & Mechanical [email protected]

Learning goals
Learning Goals

  • Construct Formatted Tables in Excel

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

Using tables charts
Using Tables & Charts

  • Engineers record and present data in two primary formats: Tables and Graphs

Tables in reports
Tables in Reports

  • When using Tables in Reports and Presentations

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

Table construction demo
Table Construction Demo

Time For

Live Demo

Charts and graphs
Charts and Graphs

  • Carefully The Select the TYPE of Chart

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

Charts graphs cont
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

Ms excel charts
MS Excel Charts

  • Area Chart

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

Ms excel charts1
MS Excel Charts

  • Bar Chart

    • A 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 showthe relationship of individual items to the whole.

Ms excel charts2
MS Excel Charts

  • Column Chart

    • A column chart shows data changes over a period of time or illustrates comparisons among items. Categories are organized horizontally, values vertically, to emphasize variationover time. Stacked column charts show the relationship of individual items to the whole.

Ms excel charts3
MS Excel Charts

  • Line Chart

    • A 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, rather than the amount of change or the magnitude of values.

Ms excel charts4
MS Excel Charts

  • Pie Chart

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

Ms excel charts5
MS Excel Charts

  • XY (Scatter) Chart

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

Ms excel charts6
MS Excel Charts

  • Doughnut Chart

    • Like a pie chart, a doughnut chart shows the relationship of parts to a whole, but it can contain more than one data series. Each ring of the doughnut chart represents a data series

    • Basically nestedPie-Charts

Ms excel charts7
MS Excel Charts

  • Radar Chart

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

Ms excel charts8
MS Excel Charts

  • Surface Chart

    • A 3D surface chart is useful when you want to find optimum combinations between two sets of data. As in a topographic map, colors and patternsindicate areas thatare in the samerange of values.

Ms excel charts9
MS Excel Charts

  • Bubble Chart

    • A bubble chart is a type of xy (scatter) chart. The size of the data marker indicates the value of a third variable.

Ms excel charts10
MS Excel Charts

  • Stock Chart

    • The stock chart is often used to illustrate stock prices. This chart can also be used for scientific data, for example, to indicate temperature changes

Ms excel charts11
MS Excel Charts

  • Cone, Cylinder, and Pyramid Chart

    • The cone, cylinder, and pyramid data markers can lend a dramatic effect to 3-D column and bar charts.

Graph construction demo
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)

Do in



  • Construct a Scatter Chart to Find the Clapeyron Eqn Constants m & bfor Stannic Chloride

Chart construction result
Chart Construction Result


Slope intercept cmds

  • Find Explicitly the Least-Sqsm&b for

S i cmds vs plot trendline
S&I Cmdsvs Plot TrendLine

All done for today
All Done for Today


Engr/Math/Physics 25


Time For

WhtBd Demo

Time For

Live Demo

Bruce Mayer, PE

Licensed Electrical & Mechanical [email protected]

Table construction demo 1


Table Construction Demo (1)

  • Merge Cells Vertically

  • Top & Bot Rows 20High

  • Middle Rows 16high

Table construction demo 2

Format Alignment of the “Year 3 Cell

Table Construction Demo (2)

  • Result so Far

  • Format Dialog Box

Table construction demo 3

Set Column Widths

2, 30, 11, 5, 30, 11, 5

Table Construction Demo (3)

  • Format Top & Bot Cells for VERTICAL Centering

  • Format Fonts

    • All RED

    • Top, Bot, & Lt-End Rows Bold

Table construction demo 4

Set Border Color to Blue

Table Construction Demo (4)

  • Grid INSIDE blue

Table construction demo 5

Double-Line Blue Border Outside

Table Construction Demo (5)

  • Double-Line Blue Border Lt-Vert Cell

Table construction demo 6

Double-Line Blue Border Remainder

Table Construction Demo (6)

Table construction demo 7

Center Columns: Course No., Units

Table Construction Demo (7)

Chart construction demo 1


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

Chart construction demo 2
Chart Construction Demo (2)

  • Result after Transpose Paste

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

Chart Construction Demo (3)

Chart construction demo 4

Place in cols A & B

1000/T; T in Kelvins


Chart Construction Demo (4)

  • After Filling A & B

  • Formula for Col-B

    • =LN(E8)

Chart construction demo 5

Now need to Sort the Data with the indep var (1000/T) in ASCENDING ORDER


Chart Construction Demo (5)

Chart construction demo 6

Highlight/Select Data to Plot ASCENDING ORDER

Invoke Chart Wizard

Chart Construction Demo (6)

Chart construction demo 7

Continue with ASCENDING ORDERChart Wizard

Chart Construction Demo (7)

Chart construction demo 8


Chart Construction Demo (8)

  • Insert As NEW Sheet

    • Give Descriptive Name

  • Remove Legend

Chart construction demo 9
Chart Construction Demo (9) ASCENDING ORDER

  • Chart Wizard Result

  • Change

    • X-axis Scale: 2.5-4

    • Shorten Title

    • Clear BackGround

    • Lager, Sq Data Markers

    • GridLine & Text Colors

Chart construction demo 10

Select Chart Area ASCENDING ORDERThenRight-Clik

Chart Construction Demo (10)

  • Select X-axis, Ther Right-Clik

Chart construction demo 11

Select Grid Lines, Rt-Clik, Chg Colors ASCENDING ORDER

Chart Construction Demo (11)

  • Select Data Series, Rt-Clik, Chg Marker

Chart construction demo 12

Position Labels at Page Edges → Stretch-Out Plot Area ASCENDING ORDER

Chart Construction Demo (12)

Chart construction demo 13
Chart Construction Demo (13) ASCENDING ORDER

  • Chart Fine-Tuning Result

  • Add TrendLine to find Clapeyron m &b Constants

Chart construction demo 14

Select Data Series, Rt-Clik, ASCENDING ORDERAdd TrendLn

Chart Construction Demo (14)

  • Select: Linear, Display Parameters

Chart construction demo 15

Fine Tune TrendLine Form & Display ASCENDING ORDER

Chart Construction Demo (15)

  • Done with Plot; and have determined m & b by Trendline

    • Note that the Fit is Excellent; R2 = 99.92%

Chart construction demo 16

Add Fitted Data to table ASCENDING ORDER

Chart Construction Demo (16)

Calc Error=(G4-E4)/E4

Calc Using m & b

Analysis of Fit Characteristics

Copy & Paste from Chart

  • Put Fitted Data on Chart ASCENDING ORDER

    • On Table: Select & Copy Data

    • On chart: EDIT → PASTE SPECIAL → dialog Box above

Chart construction demo 17

Fine Tune Two-Variable Display ASCENDING ORDER

Chart Construction Demo (17)

  • To Make Error Data More Visible Show using SECONDARY Axis at Right

Error Data Series

Chart construction demo 19

Fine Tune Two-Axes Display ASCENDING ORDER

Chart Construction Demo (19)

Chart construction demo 20
Chart Construction Demo (20) ASCENDING ORDER


Nice chart

Coefficient of correlation
Coefficient of Correlation ASCENDING ORDER

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

Interpretation of r
Interpretation of R ASCENDING ORDER

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

Goodness of fit