EXCEL: FORMULAS & FUNCTIONS II PARTICIPATION PROJECT Housing

EXCEL: FORMULAS & FUNCTIONS II PARTICIPATION PROJECT Housing Problem WV Mining Problem

TOPICS COVERED Write 3-D formulas Use the IF function Use AND/OR criteria Use nested IF functions 2

3-D FORMULAS 3-D formulas are used to reference data on another worksheet in the same workbook. This is helpful for combining information from larger datasets. Example: Adding cells A1 on Sheet1 and A1 on Sheet2, placing the result in cell A1 on Sheet3. 3

WRITE 3-D FORMULAS 1. Select the cell where you wish to insert your 3-D formula. 2. Type “ ” to begin the formula. 3. Go to the worksheet that contains the first cell and click the cell. 4. Enter the arithmetic operator. 5. Go to the worksheet that contains the second cell and click that cell. 6. Continue building your formula, pressing the Enter key when done. 4

LOGICAL CONDITIONS: PART 1 In logic, a sentence or proposition is of the form “If A then B.” A is logical condition If taking a flight, then 50 min If driving, then 3 h 30 min If walking, then 67 h Morgantown Washington D.C. 5

LOGICAL CONDITIONS: PART 2 If a logical condition is satisfied, then its result is TRUE. Different logical conditions lead to different results. In Excel, a logical condition is a comparison using mathematical symbols that evaluate to true or false. – If the comparison is correct, the result is TRUE – If the comparison is wrong, the result is FALSE Comparison Operators Operator Description Equal to Not equal to Less than Greater than Less than or equal to (at most) Greater than or equal to (at least) For example, 3 5 is TRUE 4 6 is FALSE 9 10 is TRUE 8 12 is FALSE 6

IF FUNCTION ¿ 𝐼𝐹 (8 10 , Yes The most common logical function is IF() function in Excel. The IF() function has three arguments: – A logical condition – What to do if condition is met (TRUE) – What to do if condition is not met (FALSE) , No Condition What to What to show if show if TRUE FALSE 7

USE THE IF FUNCTION: PART 1 The IF() function can be typed into the cell directly or by using the Logical Function Library. 1. Select the cell where you wish to insert IF function. 2. Go to the Formulas ribbon. 3. In the Function Library section, click Logical, and then select IF. 8

USE THE IF FUNCTION: PART 2 4. In Function Arguments dialog box, set the following values: – Logical test: Test to use – Value if true: What to do if the logical condition is true – Value if false: What to do if the logical condition is false 5. Click the OK button. 9

AND FUNCTION The AND() function requires all logical conditions to be true to return TRUE. – If all conditions are true, then the result is TRUE – If one or more conditions are false, then the result is FALSE 10

INSERT AND FUNCTION: PART 1 AND() functions can be typed into the cell directly or by using the Logical Function Library. 1. Select the cell where you wish to insert the AND() function. 2. Go to the Formulas ribbon. 3. In the Function Library section, click Logical, and then select AND. 11

INSERT AND FUNCTION: PART 2 4. In Function Arguments dialog box, set the following values: – Logical1: First logical condition – Logical2: Second logical condition – Logical3: Third logical condition, if there is one 5. Click the OK button. 12

OR FUNCTION The OR() function is opposite of the AND() function. – If one or more conditions is true, then the result is TRUE – If all conditions are false, then the result is FALSE 13

INSERT OR FUNCTION: PART 1 OR() functions can be typed into the cell directly or by using the Logical Function Library. 1. Select the cell where you wish to insert the OR() function. 2. Go to the Formulas ribbon. 3. In the Function Library section, click Logical, and then select OR. 14

INSERT OR FUNCTION: PART 2 4. In Function Arguments dialog box: – Logical1: First logical condition – Logical2: Second logical condition – Logical3: Third logical condition, if there is one 5. Click OK. 15

NESTED IF FUNCTIONS Nested means putting an IF() function inside another IF() function. Nested IF() functions are used to make three or more comparisons. Example: The points are stored in cell A1 and the comments in A2. – A1 100 ----- Excellent – A1 90 ----- Good – A1 50 ----- Bad 16

USE NESTED IF FUNCTIONS: PART 1 Nested IF() functions can be typed into the cell directly or by using the Logical Function Library. 1. Select the cell where you wish to insert the nested IF function. 2. Go to the Formulas ribbon. 3. In the Function Library section, click Logical, and then select IF. 17

USE NESTED IF FUNCTIONS: PART 2 4. In Function Arguments dialog box, set the following values: – Logical test: First test to use – Value if true: Type your second IF function here if the logical condition on the first is true (see slide 10 “Insert IF function”) – Value if false: Type your second IF function here if the logical condition on the first is false (see slide 10 “Insert IF function”) 5. Click the OK button. 18