Using Function

Prepared by: Prof. Rubilee S. Mariano

COMPREHENSIVE

Objectives

XP

Evaluate a single condition using the IF function Evaluate multiple conditions using the AND function Calculate different series of outcomes by nesting IF functions Test whether one or more conditions are true with the OR function

New Perspectives on Microsoft Office Excel 2007

Working with Logical Functions

XP

IF function The Excel IF function checks to see if a certain condition is true or false. If the condition is true, the function will do one thing, if the condition is false, the function will do something else. IF Function Format
IF(logical_test, value_if_true, [value_if_false])

New Perspectives on Microsoft Office Excel 2007

Example, we will try to test if the value in column XP A3 is greater than the value in column B3. If it is, the IF function will place the statement "A is larger" in column D. If it is not, the IF function will place the statement "B is larger" in column D. Our IF function will be entered into cell D1 and it looks like this: =IF(A3 > B3,"A is larger","B is larger") Note: the two text statements "A is larger" and "B is larger" are enclosed in quotations. In order to add text to an Excel IF Function, it must be enclosed in quotation marks.
New Perspectives on Microsoft Office Excel 2007 4

Parts of the Excel IF function Inside the round brackets there are three parts or arguments to an IF Function. The Arguments the logic test - is A3 > B3 the value if true - is "A is larger" the value if false - is "B is larger

XP

Between each argument of the IF function, the comma is used as a separator. The comma is what tells Excel when each argument of IF function ends and the next section begins.

New Perspectives on Microsoft Office Excel 2007

Adding Separators XP Therefore, as we complete the IF function, we will add two separators one between the logical test and the value if true arguments one between the value if true and the value if false arguments.

IF(logical_test, value_if_true, [value_if_false])

Writing the IF Function Logical Test The logical test is always a comparison between two amounts,thus, we are comparing the values in cells A3 and B3. Between the two values we place a comparison operator. The comparison operators that can be used in the logical test portion of an Excel IF function are: Equals (=) Less than (<) Less than or equal to (<=) Greater than (>) Greater than or equal to (>=) Not equal to (<>)
New Perspectives on Microsoft Office Excel 2007 6

Exercise 1. Using Excel's IF Function

XP
B RESULT

A 1 2 THE DATA 50

A. Write an IF statement so that if the number in Cell A2 is less than 100 the formula displays the text "Within budget", otherwise the formula displays the text "Over budget in cell B2.

B. Write an IF statement so that if the number in Cell A2 is 100 then the formula sums the range B5:B15. Otherwise, the formula returns a blank (empty text).

New Perspectives on Microsoft Office Excel 2007

NESTED IF Functions

XP

A nested IF function is when one IF function is placed inside another IF function to test an additional condition =IF([Pay Grade]=1,2500,IF([Pay Grade]=2,5000, IF([Pay Grade]=3, 7500,"Invalid pay grade")))

New Perspectives on Microsoft Office Excel 2007

Exercise 3. More Practice with IF Functions

A 1 2 3 4 SCORES 45 90 78 IF SCORES IS GREATER THAN 89 FROM 80 TO 89 THEN RETURN A B

XP

FROM 70 TO 79
FROM 60 TO 69 Below 60

C
D F

A. Write a NESTED IF statement assigning a letter grade to the score in Cell A2. ANSPROB

New Perspectives on Microsoft Office Excel 2007

XP

AND Function The AND function is one of Excels Logical Functions. Like most logical functions, the output from the AND function is either the word TRUE or FALSE. To determine whether the output will be TRUE or FALSE, the function evaluates at least one mathematical expression located in another cell in the spreadsheet.

New Perspectives on Microsoft Office Excel 2007

10

XP

Example AND Function Note: This example is typed in cell C1. =AND (B1>100, B2>100, B3>100) If all three of these cells (B1,B2, and B3) contain a value greater than 100, the output for the AND function in cell C1 will be TRUE. If any of these cells have numbers less than or equal to 100, the output for the AND function will be FALSE.

New Perspectives on Microsoft Office Excel 2007

11

Combining the AND Function with Other Functions XP By itself, the AND function has limited usefulness. By combining it with another function, such as the IF function, the AND function can increase the capabilities of your spreadsheet. Example Nesting the AND function inside the IF function =IF(AND(B1>100,B2>100,B3>100),Over budget,Acceptable) Assuming this function is located in cell C1, if all three of these cells (B1,B2, and B3) contains a value greater than 100, the IF function will show the statement Over Budget in cell C1. If any of these three cells contain a number less than or equal to 100, the IF function shows the statement Acceptable in cell C1.
New Perspectives on Microsoft Office Excel 2007 12

Note: In Excel 2007, the AND function can contain a maximum of 255 expressions. For earlier versions of Excel, the limit is 30 expressions.
Exercise 1. Using Excel's AND
A 1 2 3 4 THE DATA 5 9 15

XP

A. Write an AND formula to determine if A2>A3 and A2<A4 is a true or false statement.

New Perspectives on Microsoft Office Excel 2007

13

SOLUTION : A. =AND(A2>A3, A2<A4)

XP

PROB. CREATE A FORMULA USING IF AND FUNCTION BASED ON THE CONDITION THAT THE STUDENT COULD ONLY PASS THE SUBJECT IF HIS/HER GRADE IS GREATER THAN 75 AND ABSENCES IS LESS THAN 10.

New Perspectives on Microsoft Office Excel 2007

14

Exercise2 :
A 1 2 3 4 5 6 7 8 Linda Joe Bill Mary Mark John Ted Name East West East South South North East B District C Sales $20,000.00 $42,302.00 $53,001.00 $12,000.00 $ 2,050.00 $9,000.00 $40,000.00 D Emp. Yrs 2 9 3 12 6 0 4 E Job Level

XP

Problem: suppose you want a formula in column E that will assign a job level based on two different criteria: Salespeople who have been employed for more than 5 years AND have annual sales of more than $10,000 should be assigned a job level code of 2, and all others should have a job level code of 1.
New Perspectives on Microsoft Office Excel 2007 15

SOLUTION:=IF(AND(D2>5,C2>10000),2,1)

XP

The OR function is one of Excel's Logical Functions. Like most logical functions, the output from the OR function is either the word TRUE or FALSE. To determine whether the output will be TRUE or FALSE, the function evaluates at least one mathematical expression located in another cell in the spreadsheet. Example OR Function Note: This example is typed in cell c1 If any of these three cells (B1,B2, or B3) contains a value greater than 100, the output for the OR function in cell C1 will be TRUE. If all three cells have numbers less than or equal to 100, the output for the OR function will be
New Perspectives on Microsoft Office Excel 2007 16

Combining the OR Function with XP Other Functions By itself, the OR function has limited usefulness. By combining it with another function, such as the IF function, the OR function can increase the capabilities of your spreadsheet. Example Nesting an OR function inside an IF function =IF(OR(B1>100,B2>100,B3>100),Over Budget,Acceptable)
New Perspectives on Microsoft Office Excel 2007 17

Assuming this function is located in cell C1, if any of XP these three cells (B1,B2, or B3) contains a value greater than 100, the IF function will show the statement Over Budget in cell C1. If all three cells have numbers less than or equal to 100, the IF function shows the statement Acceptable in cell C1. Note: In Excel 2007, the OR function can contain a maximum of 255 expressions. For earlier versions of Excel, the limit is 30 expressions.

New Perspectives on Microsoft Office Excel 2007

18

Problem:
A 1 2 3 4 THE DATA 5 9 15

XP

B. Write an OR formula to determine if A2>A3 or A2<A4 is a true or false statement.

New Perspectives on Microsoft Office Excel 2007

19

XP

Problem: Create a formula using the If function to return 25 if cell A1 > 100 and cell B1 < 200. Otherwise, it should return 0.

New Perspectives on Microsoft Office Excel 2007

20

Ans. =IF(AND(A1>100,B1<200),25,0)

XP

THANK YOU!!!

New Perspectives on Microsoft Office Excel 2007

21

Using Lookup Tables and Functions

A lookup table is a table that organizes data you want to retrieve into different categories The categories for the lookup table, called compare values, are located in the tables first column or row To retrieve a particular value from the table, a lookup value (the value you are trying to find) needs to match the compare values VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

XP

New Perspectives on Microsoft Office Excel 2007

22

Using Lookup Tables and Functions

XP

New Perspectives on Microsoft Office Excel 2007

23

Using Lookup Tables and Functions

XP

New Perspectives on Microsoft Office Excel 2007

24

Highlighting Duplicate Records with a Custom Format

XP

Select the column you want to search for duplicates In the Styles group on the Home tab, click the Conditional Formatting button, point to Highlight Cells Rules, and then click Duplicate Values Click the values with arrow, then click Custom Format In the Format Cells dialog box, set the formatting you want to use Click the OK button in each dialog box

New Perspectives on Microsoft Office Excel 2007

25

Highlighting Duplicate Records with XP a Custom Format

New Perspectives on Microsoft Office Excel 2007

26

Using the Conditional Formatting Rules Manager

XP

Each time you create a conditional format, you are defining a conditional formatting rule A rule specifies the type of condition (such as formatting cells greater than a specified value), the type of formatting when that condition occurs

New Perspectives on Microsoft Office Excel 2007

27

Using the IFERROR Function

XP

Error values such as #DIV/0!, #N/A, and #VALUE! indicate that some element in a formula or a cell referenced in a formula is preventing Excel from returning a calculated value The IFERROR function can determine if a cell contains an error value and display the message you choose rather than the default error value =IFERROR(VLOOKUP(L2,HealthPlanRates,2,False) *12,"Invalid code")
New Perspectives on Microsoft Office Excel 2007 28

Using the IFERROR Function

XP

New Perspectives on Microsoft Office Excel 2007

29

Summarizing Data Conditionally

XP

You can calculate the number of cells in a range that match criteria you specify using the COUNTIF function, which is sometimes referred to as a conditional count =COUNTIF(range,criteria) You can add the values in a range that meet criteria you specify using the SUMIF function, which is also called a conditional sum =SUMIF(range,criteria[,sum_range])
New Perspectives on Microsoft Office Excel 2007 30

Summarizing Data Conditionally

XP

You use the AVERAGEIF function to calculate the average of values in a range that meet criteria you specify =AVERAGEIF(range,criteria[,average_range])

New Perspectives on Microsoft Office Excel 2007

31

Summarizing Data Conditionally

XP

The COUNTIFS function counts the number of cells within a range that meet multiple criteria COUNTIFS(criteria_range1,criteria1[,criteria_range2, criteria2...]) The SUMIFS function adds values in a range that meet multiple criteria SUMIFS(sum_range,criteria_range1,criteria1[,criteria_ range2, criteria2...]) The AVERAGEIFS function calculates the average of values within a range of cells that meet multiple conditions AVERAGEIFS(average_range,criteria_range1,criteria1 [,criteria_range2, criteria2...])
New Perspectives on Microsoft Office Excel 2007 32

Using Advanced Filtering

XP

Advanced filtering, similar to filtering, displays a subset of the rows in a table or range of data The criteria range is an area in a worksheet, separate from the range of data or Excel table, used to specify the criteria for the data to be displayed after the filter is applied to the table

New Perspectives on Microsoft Office Excel 2007

33

Using Advanced Filtering

XP

New Perspectives on Microsoft Office Excel 2007

34

Using Advanced Filtering

Click the Data tab on the Ribbon, and then, in the Sort & Filter group, click the Advanced button

XP

New Perspectives on Microsoft Office Excel 2007

35

Using Database Functions to Summarize Data

XP

Functions that perform summary data analysis (SUM, AVERAGE, COUNT, and so on) on a table of values based on criteria that you set are called the Database functions, or Dfunctions DfunctionName(table range, column to summarize, criteria range)

New Perspectives on Microsoft Office Excel 2007

36

Using Database Functions to Summarize Data

XP

New Perspectives on Microsoft Office Excel 2007

37

Using Database Functions to Summarize Data

XP

New Perspectives on Microsoft Office Excel 2007

38