Using Excel Built-in Functions E ngineering College of Engr.10 San - PowerPoint PPT Presentation

E ngineering College of Engr.10 San Jose State University JKA & KY 1

E ngineering College of Engr.10 San Jose State University Engineering Analysis • Engineering analysis is a systematic process for analyzing problems that arise in the various fields of engineering. • As part of the problem solving process, the data collected has to be processed, analyzed and sometimes displayed graphically by using many mathematical tools that are available . • In many cases, once you have defined and set up the problem properly, numerical methods are required to solve the mathematical equations. Microsoft’s Excel spreadsheet software has many numerical procedures built directly into its program structure. 2

E ngineering College of Engr.10 San Jose State University Spreadsheets’ Capabilities Data Analysis Tools • Store, process, and sorts data • Graphically display data (Engineering application) • Perform statistical analysis • Fit equations to curves (Engineering application) • Solve single and system of algebraic equations (Engr. Appl.) • Solve optimization problems (Engineering application) • Draw Flow Charts 3

E ngineering College of Material Strength Engr.10 Ductile Steel (low carbon) San Jose State University Standard Tensile Test Standard Specimen S y – yield strength S u – fracture strength σ (stress) = Load / Area ε (strain) = (change in length) / (original length) Ken Youssefi 4 PDM I, SJSU

E ngineering College of menu bar Engr.10 San Jose State University File management currently active formula bar options cell (A1) worksheet tabs 5

E ngineering College of Engr.10 San Jose State University Entering Data into Cells (Cell Content) 6

E ngineering College of Engr.10 Copying Cells San Jose State University 1. Using icons in the ribbon menu a) Click on the Cells to be copied b) Select “ Copy” on the Home toolbar c) Click on the Cells to paste the copied contents d) Select “Paste ” or “Special Paste” on the Home toolbar 2. Using keyboard - Ctrl C to copy & Ctrl V to paste “Pull Down corner” at Lower Right Corner 3. (recommended for a range of cells) a) place the cursor on the LRC of the active cell b) Click and drag over the cell rage “Fill Down” : select the cell containing the active formula and the range of cells you need to fill select “Fill” on the Home menu and choose “Down” 7

E ngineering College of Engr.10 San Jose State University Relative Addressing A B C D E A B C D E 1 1 2 internal equation 2 Result 3 2 3 =B3+C3 3 2 3 5 4 5 1 =B4+C4 4 5 1 6 5 10 4 =B5+C5 5 10 4 14 6 15 5 =B6+C6 6 15 5 20 7 SUM: 34 15 =SUM(D3:D6) 7 SUM: 32 13 45 8 8 1. =B3+C3 adds the content of cells B3 and C3. 2. As the formula is copied into D4, D5 and D6, cell addresses of the formula are incremented. 8

E ngineering College of Engr.10 Absolute Addressing San Jose State University A B C D E A B C D E 1 k= 0.5 1 k= 0.5 2 internal equation 2 Result 3 2 3 =B3+C3+$B$1 3 2 3 5.5 4 5 1 =B4+C4+$B$1 4 5 1 6.5 5 10 4 =B5+C5+$B$1 5 10 4 14.5 6 15 5 =B6+C6+$B$1 6 15 5 20.5 7 SUM: 34 15 =SUM(D3:D6) 7 SUM: 32 13 47 8 8 • Using the absolute cell address, $B$1, will keep the cell reference constant for all calculations. 9

E ngineering College of Engr.10 San Jose State University Basic Math Operations Operation Algebraic Excel Format add a+b a+b subtract a-b a-b multiply ab, axb, a.b a*b divide a/b a/b a n exponential a^n number 5.07x10 +12 5.07E12 format 5.07*10^12 3.15x10 -3 3.15E-3 3.15*10^(-3) 10

E ngineering College of Engr.10 San Jose State University Excel Formulas • In Excel, a formula expresses dependency of one cell on others in the worksheet. • Formula entry for a Cell begins with clicking on the Cell first and then either typing the equal sign “=“ in the Cell itself or clicking on the Formula Bar , and ends with “ Enter ”. • A Formula can be edited by first clicking on the Cell and then editing the formula on the active Cell or on the Formula Bar. • A Formula may contain functions. Example Math syntax: 3 x 2 + e (-0.3 x ) - 10 x Excel syntax: =3*(A15^2) + EXP(-0.3*A15) - 10*A15 The value of x is in cell A15 11

Using Excel Built-in Functions E ngineering College of Engr.10 San Jose State University After clicking on the Cell • Click on the “Formula Bar” ( f x ), follow instructions on the “Insert Function” window. Or, in the “Formulas” menu select “Insert Function“ • Follow instructions on the “Function Argument” window, e.g., select the value, or range of values for the function. 12

E ngineering College of Engr.10 San Jose State University Frequently Used Excel Functions Math Excel Syntax Purpose: Returns the: (Assume value of x is in cell A15.) π value of π (3.141593…) PI() e x Value of e x where e is the base of EXP(A15) Natural Log √ x SQRT(A15) Value of the square root of x log 10 (x) LOG10(A15) Logarithm of x, with base 10 ln(x) LN(A15) Natural logarithm of x, with base e SUM(x1,x2,x3) SUM( A15:A17) x1+x2+x3 cos(x) COS(A15) Cosine of x sin(x) SIN(A15) Sine of x tan(x) TAN(A15) Tangent of x Average(x1,x2,x3) AVERAGE(A15:A17) (x1+x2+x3)/3 13

E ngineering College of Engr.10 San Jose State University Example: “My Expense Table” 14

E ngineering College of Engr.10 San Jose State University “My Expense Table” - Aug. Expense =SUM(B5:B10) 15

E ngineering College of Engr.10 San Jose State University “ My Expense Table” - Aug. Expense (cont.) 16

E ngineering College of Engr.10 San Jose State University “Eliminating Repetition” Repeat procedure for the “Total” of each category Place cursor in the lower right corner of cell B11, “click -and- drug” along row 11 for the rest of the total of the rest of the months 17

E ngineering College of Engr.10 San Jose State University “My Expense Table” - Completed 18

E ngineering College of Engr.10 San Jose State University Insert menu 2013 version Plotting Data 2007 version 19

E ngineering College of Engr.10 San Jose State University 2013 version Data menu 2007 version 20

E ngineering College of Engr.10 San Jose State University 2013 version View menu 2007 version 21

E ngineering College of Engr.10 San Jose State University MAX and MIN Functions • =MAX or MIN(X1, X2, X3…) will take the maximum or minimum of the numbers in the parentheses. • =MAX or MIN(X1:X4) will take the maximum or minimum of all the numbers from X1 to X4. COUNT Function • =COUNT(X1, X2, X3…) will count the number of cells that contain numbers and the arguments in the list that are numbers. Example =COUNT(A1, A2, A3) will return 2, because of the 4 and 9. =COUNT(A1:A4) will return 3, because of the 4, 9 and 3. =COUNT(A1:A4, - 17, “world”) will return 4, because of the 4, 9, 3 and -17. 22

E ngineering College of Engr.10 San Jose State University COUNTIF Function • =COUNTIF(range, criteria) will count the number of cells in the range that match the criteria. Note: if the criteria consists of a relational expression, such as “>5”, it must be enclosed in double quotes. Example =COUNTIF(A1:A4, “>5”) will return 2, because 6 and 9 are greater than 5. =COUNTIF(A1:B4, “=6”) will return 3, because three cells in the range contain 6. 23

E ngineering College of Engr.10 San Jose State University IF Logical Function • =IF(logical_test, value_if_true, value_if_false) returns the second argument if the test is true, and the third if it is false. Example =IF(A1>3, “A1 is larger than 3”, 0) would return “A1 is larger than 3”. =IF(A4>3, “A1 is larger than 3”, 0) would return 0. 24

E ngineering College of Engr.10 San Jose State University VLOOKUP or HLOOKUP Function Vertical search Horizontal search • =VLOOKUP(lookup_value, table_array, col_index_number, not_exact_match) finds the value in the top row of a table and returns the corresponding column value.  Lookup_value: The value that the function looks for in the first column.  Table_array: The table that the function looks through.  Col_index_number: The column number from which the function should return (search for) the value.  Not_exact_match: Can be either True or False . True means it will find the closest value to the lookup value. False means you only want the value returned if it is an exact match. 25

E ngineering College of Engr.10 VLOOKUP Function - Example San Jose State University Q R S Question: find the price 50 for item with ID# 12 51 52 53 =VLOOKUP(lookup_value, table_array, col_index_number, not_exact_match) =VLOOKUP(12, Q50:S53 ,2, FALSE) Answer = 14.33 26