1 / 32

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

Engr/Math/Physics 25. MS Excel WorkSheets. Bruce Mayer, PE Licensed Electrical & Mechanical Engineer BMayer@ChabotCollege.edu. Learning Goals. Enter Data into Excel Use Copy and Fill Perform Calculations Using Excel Writing “Formulas” Proper Syntax InterRelationship Between Cells

vilmaris
Download Presentation

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

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. Engr/Math/Physics 25 MS Excel WorkSheets Bruce Mayer, PE Licensed Electrical & Mechanical EngineerBMayer@ChabotCollege.edu

  2. Learning Goals • Enter Data into Excel • Use Copy and Fill • Perform Calculations Using Excel • Writing “Formulas” • Proper Syntax • InterRelationship Between Cells • Invoking and Using Built-in Functions • Explain the difference between ABSOLUTE and RELATIVE addresses

  3. Learning Goals cont • Construct Charts and Graphs • Comparison Charts → Bar, Col, Radar • Analysis Charts → Scatter, Surface • Curve Fits → Linear Regression

  4. SpreadSheets • SpreadSheet Defined  A table of values arranged in rows and columns. The Intersection of a Row & Column is Called a Cell. • Each cell-value can have a predefined relationship to the other cell values. • If you change one cell-value other values may change depending on the interrelationship of the cells.

  5. MicroSoft Excel • Excel is the MS-Office Application program used to create spreadsheets • Within Excel, users can organize data, create charts, and perform calculations. • Excel allows the user to create very large spreadsheets, reference information from other spreadsheets, and it permits organized storage and modification of information • Excel operates like other MS Office programs and has many of the same functions and shortcuts as MSword & MSpowerpoint

  6. Why Excel? • Excel can do most (not all) of the common (i.e., useful & popular) tasks done in MATLAB or similar software • Excel is much more accessible • MATLAB is Powerful, but it’s also SPECIALIZED and EXPENSIVE • Excel is: Ubiquitous, and Quick & Easy • Excel is much more POWERFUL than Many people think

  7. MSExcel2010Specifications 17e9 cells/wksht http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx?CTT=5&origin=HP005199291

  8. Excel WorkSheet (1) • An Excel File is also Called a WORKBOOK • Each workBOOK can Contain a large number of workSHEETs (>200) • Where a column and a row intersect is called a CELL. • For example, cell B5 is located where column B and row 5 meet. You enter your data into the cells on the Active (Displayed) worksheet • The tabs at the bottom of the screen represent different worksheets within a workbook. NAME BOX: Displays Selected Cell Selected Cell WorkSheet ScrollButtons Active WorkSheet

  9. Excel WorkSheet (2) The NAME BOX indicates what cell you are in. This cell is called the “active cell.” This cell is highlighted by a black box. The FORMULA BAR indicates the contents of the cell selected. Any formula created will appear in this space Active Cell The RESULT of the Formula will Appear in a Formula Cell; NOT The formula itself Text DOES appearin a Text Cell

  10. Excel Data Types • Spreadsheets can contain Several types of data • Numbers • Text • Dates & Time • Can Add, Subtract • Currency • Charts • Equations • Pictures

  11. Cell Addressing • COLUMNS are designated by alphabetical values such as A, B, C …. • ROWS are designated by numerical values such as 1, 2, 3 …. • Individual cells are designated by ordered pairs containing the row and column designation such as A1,C7, B10 …. B10 =Active Cell

  12. Excel DATA Entry • Move the Mouse Pointer to the Desired Cell • Click Cell to Make it Active, then do One of • Type Data DIRECTLY Into the Cell • Move Cursor to FORMULA BAR and type there Type in Formula Bar Type in Cell

  13. Excel FORMULA Entry • You can also calculate values using formulas. • The formula can perform mathematical operations using data from other cells. • To Write a Formula, Rt-Clikthe Cell To make it Active • Start Typing, OR • Type in Formula Bar

  14. Fill FORMULAS • Enter the Formula in One Cell • Select the Formula Cell, Bottom Rt Corner of selection Box turns to a “+” • Drag the the + up/down or Across • “Cell References” adjust automatically

  15. Fill DATA • If you need to enter an INCREMENTED Data Series you can skip making a Formula, and Use FILL instead • Type the First 2 or 3 values in the Sequence • Select the Data Cells, Bottom Rt Corner of the selection Box turns to a “+” • Drag the the + up/down or Across

  16. Anatomy of a Formula MIXED Reference; COL (F) Changes, while ROW (16) Does NOT when moved or copied RELATIVE Reference; Changes when cell Filled, Copied or Moved ABSOLUTE Reference; Does NOT Change when cell Filled, Copied or Moved

  17. Excel Built In Functions • SUM (math & trig) • AVERAGE (statistical) • SIN (math & trig) • IF (logical) • NPV (financial) • CONCATENATE (text) • LOOKUP (lookup and reference) • Many More …

  18. SUM(number1, number 2,…) • Example=SUM(5,7,11) equals 23 • If cells A2:E2 contain 5, 15, 30, 40, 50: =SUM(A2:C2) equals 50 =SUM(A2:E2, 13) equals 153

  19. AVERAGE(number 1, number 2,…) • ExampleREGION A1:E1 is NAMED “Luminance” • Then Several Average Calcs

  20. SIN(number) • NOTE: Angle (number) must be provided in radians • If The argument is in degrees (°), Use the RADIANS Fcn to convert it to radians. • Some SIN & COS Calcs

  21. IF Function • The Syntax IF(logical_test,value_if_true,value_if_false) • Logical_test any value or expression that can be evaluated as TRUE or FALSE. • =IF(A10<=100,"Within budget","Over budget") • =IF(A10=100,SUM(B5:B15),"") • Value_if_true value returned if logical_test = TRUE • Value_if_false value returned if logical_test = FALSE • NOTE: Value_if_false can also be another formula. • Up to 64 IF functions can be nestedin Excel 2010

  22. EXCEL = (equal to) > (greater than) < (less than) >= (greater than orequal-to) <= (less-than or equal-to) <> (not equal to) MATLAB == (Equal To) > (Greater Than) < (Less Than) >= (Greater Than or Equal-To) <= (Less Than or Equal-To) ~= (Not Equal To) Logical Operators

  23. COUNTIF(range, criteria) • Counts the number of cells within a range that meet the given criteria • Let C5:C8 contain "apples", "oranges", "peaches", "apples", respectively: • COUNTIF(C5:C8,"apples") equals 2 • Suppose P2:S2 contain 29, 54, 73, 86, respectively: • COUNTIF(P2:S2,">55") equals 2

  24. Consider a NATION wide Survey of Engineering Baccalaureate New Grad Salaries Example  Data Scaling (1) • Now Examine Similar Data from UC Berkeley • UCB Salaries are Higher

  25. EngineeringNoted in GREENColor

  26. We want to NORMALIZE (i.e. Scale-UP) the National Data to Account for the higher Salaries (and Cost-of-Living) in the SF Bay Area Use as Scale Factor the Avg Engineering Salaries Example  Data Scaling (2) • Take from the NACE Data The average of Time For Live Demo

  27. All Done for Today YouTubeExcelTutorials • Google Videos → Excel Tutorials

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

  29. Demo Start = Demo_Salary-Survey_Scaling_Demo-Start_1012.xls

  30. Demo CALC = File Demo_Salary-Survey-2004_Scaling_0511.xls

  31. Demo Result from CHART WIZARD w/ Fine Tuning = File Demo_Salary-Survey-2004_Scaling_0511.xls

More Related