1 / 25

EGR 105 Foundations of Engineering I

EGR 105 Foundations of Engineering I. Excel Part I Basics and Simple Plotting Section 008 Fall 2013. Excel Part I Topics. Excel basics Cell referencing Math functions Charts (plots) Homework Assignment. Excel Basics. Workbook organization Cells (row/column) Worksheets (names)

vsharon
Download Presentation

EGR 105 Foundations of Engineering I

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. EGR 105 Foundations of Engineering I ExcelPart I Basics and Simple Plotting Section 008 Fall 2013

  2. Excel Part I Topics Excel basics Cell referencing Math functions Charts (plots) Homework Assignment

  3. Excel Basics • Workbook organization • Cells (row/column) • Worksheets (names) • Entering and formatting data • Copy and paste • Fill • Simple math • +, –, *, /, ^ • Entry starts with = • “autosum” • Relative copy for math operations • Data sort • Split window

  4. Excel Part I Topics Excel basics Cell referencing Math functions Charts (plots) Homework Assignment

  5. Referring to Individual Cells • Standard way: row column notation [r, c] • Actually, letter number (can be changed) • Updates when copying cells (“relative reference”) • Example: C27 • For fixed, add $ sign (“absolute cell reference”) • Example: $C$27 • Cell names – can “rename” cells by changing content of box on upper left • Useful for formulas

  6. Excel Part I Topics Excel basics Cell referencing Math functions Charts (plots) Homework Assignment

  7. More Complicated Math n = number of points What if you had to compute… or

  8. Built-in Functions in Excel • Syntax: • Entry always starts with an equal sign (=) • Next is the name of the function followed by parentheses containing parameters or values to be operated on (arguments) • Examples: = SUM ( A10:C20 ) or = SUM ( $A$10:$C$20 ) = SQRT ( B17 ) or = SQRT ( $B$17) • Many types available

  9. Built-in Functions in Excel • Some work on single arguments, some on groups of arguments, some on none • Composition of functions works also: = SQRT ( SUM ( A10:B17 ) ) • How to invoke: • Know the name of the function • Excel’s function wizard:f x

  10. Elementary Math = SQRT (x) = ABS (x) = FACT (x) = SUM (x1,x2,…) = GCD (x1,x2,…) = LCM (x1,x2,…) = CEILING (x1,x2,…) = ROUND (x) = SUM (x1,x2,…) = COUNT (x1,x2,…) = EXP (x) = LOG (x) = LOG10 (x) = POWER (x,y) = RAND ( ) …….many others

  11. Math and Trigonometry = ACOS (x) = ATAN2 (x,y) = CEILING (x,n) = COS (x)PI ( ) = DEGREES (x) = EXP (X) = FLOOR (x,n) = LCM (x1, x2, x3,…) = LOG10 (x) = RADIANS (x) = ROUNDUP (x,n) = SIN (x) = SINH (x) = TAN (x) = TRUNC (x,n) …….many others

  12. Statistics = MIN (c1:d10) = MAX (c1:d10) = AVERAGE (c1:d10) = MEDIAN (c1:d10) = STDEV (c1:d10) = VAR (c1:d10) …….many others

  13. Engineering = BESSELI (x,n) = CONVERT (x,from,to) = ERF (lower,upper) = DELTA (x1,x2) = DEC2BIN(x,places) = GESTEP (x,step) = HEX2BIN(x,places) = IMAGINARY (x,i) = IMCONJUGATE (x) = OCT2BIN (x,places) = …….many others

  14. Logical Functions • If test: = IF ( logical test, value if true, value if false) • Example: = IF (A10 >= B20,A10,0) • Others: AND, NOT, OR

  15. Interfacing to the World • Open and Save (default format) • Save As (choose format) • Importing data • Copy and paste • Excel’s import wizard • CSV • HTML • Text • Excel/Word interfaces

  16. Excel Part I Topics Excel basics Cell referencing Math functions Charts (plots) basics Homework Assignment

  17. Charts (Plots) • Excel’s terminology: • “Chart”(plot) • Engineers commonly use XY scatter, not line!! • “Category” is the independent variable, horizontal axis, abscissa • “Value” is the dependent variable, vertical axis, ordinate • Excel’s chart wizard (chart tab)

  18. Graphing Standards • Plotting • Experimental data • Data pairs (x,y) • Theoretical curves • An equation • Axes scales • Linear • Range to show • Semilog and log • Labeling • Axes names • Units • Multiple curves on one set of axes • Legends • Line types • Symbols (if needed)

  19. Simple Plotting Generate X and Y data to Plot

  20. Common Types of Plots Example: Y=3X2 Cartesian log-log : log y-log x Semi-log : log x

  21. Problem 1 Compute Final Grade TotalsUsing Excel compute final grade totals (based on 100) for 6 students with semester grades as shown below. Use percentages: Exam 1&2 – 25% each; Homework – 20% and Final Exam – 30%. Work this problem on Sheet 1 of your Excel spreadsheet, and place your complete name on the first row. Homework Assignment #3

  22. Problem 2 Computing and PlottingConsider the motion of a baseball moving freely only under the influence of gravity. If the baseball starts at the origin at time=0 with an initial velocity vo and angle , the horizontal and vertical positions of the ball are given by the relations Also given for this problem is a set of experimental data (bb.txt) for the positions sh and sv(in meters) that includes air resistance. This data was collected for the case  = 25o but the initial velocity was not recorded. Homework Assignment #3

  23. Problem 2 Continued - First cut and paste or use the import feature and move the data from bb.txt into Sheet 2 of your Excel Spreadsheet. Then make a plot of this data with sh on the horizontal axis and sv on the vertical axis. Plot this using scatter symbols. Next on the same Sheet compute the positions sh and svusing the given theoretical relations using  = 25o but with a variable initial velocity (use absolute referencing to an open cell). Plot these results using scatter solid line. Using trial and error, make several plotting attempts to closely match the experimental data points. Final plot should contain experimental data and theoretical predictions, and include axes labels, legend and your complete name in title (see example plot). Homework Assignment #3

  24. Homework Assignment #3 Problem 2 Example Plot

  25. Problem 2 Continued – Submission Procedures Submit your Excel Spreadsheet with each problem work on Sheets 1 and 2 as an attachment to an email to Prof. Sadd with the subject line egr105_3 (no spaces). Due Date: October 10. Homework Assignment #3

More Related