IF Function in Excel with Example

IF ()

This function tests for a specific condition.

 Best view in desktop mode.

	A	B	C	D	E	F
1	Salesman	Total Sales	Target Result
2	Deepak	6000	Achieved	=IF(B2>=B14,"Achieved","Not Achieved")
3	Neeraj	5300	Achieved	=IF(B3>=B14,"Achieved","Not Achieved")
4	Roshni	4500	Achieved	=IF(B4>=B14,"Achieved","Not Achieved")
5	Aamir	2900	Not Achieved	=IF(B5>=B14,"Achieved","Not Achieved")
6	Rohit	5600	Achieved	=IF(B6>=B14,"Achieved","Not Achieved")
7	Pinky	5500	Achieved	=IF(B7>=B14,"Achieved","Not Achieved")
8	Prashant	6300	Achieved	=IF(B8>=B14,"Achieved","Not Achieved")
9	Chhavi	3500	Not Achieved	=IF(B9>=B14,"Achieved","Not Achieved")
10	Priyansh	4100	FALSE	=IF(B10>=B14,TRUE,FALSE)
11	Ajay	5200	TRUE	=IF(B11>=4200,TRUE,FALSE)
12
13
14	Today's Target	4200
15

Result can be obtained in TRUE and FALSE based on condition criteria meet. Like row 10 and 11.

Criteria can also be defined within the function. Like row 11.

Syntax

=IF (logical_test, [value_if_true], [value_if_false])

where

logical_text    = condition to evaluate.

value_if_true= TRUE or defined value when condition is true.

Value_if_false= FALSE or defined value when condition is false.

Formatting

No special formatting is needed.

Example 1

Here is student marks and we have to calculate result on the basis of marks obtained. Passing marks minimum value is 60.

	A	B	C	D	E
19	Student Name	Marks	Result
20	Deepak	56	Fail	=IF(B20>=60,"Pass","Fail")
21	Neeraj	78	Pass	=IF(B21>=60,"Pass","Fail")
22	Roshni	69	Pass	=IF(B22>=60,"Pass","Fail")
23	Aamir	82	Pass	=IF(B23>=60,"Pass","Fail")
24	Rohit	42	Fail	=IF(B24>=60,"Pass","Fail")
25	Pinky	85	Pass	=IF(B25>=60,"Pass","Fail")
26	Prashant	53	Fail	=IF(B26>=60,"Pass","Fail")
27	Chhavi	78	Pass	=IF(B27>=60,"Pass","Fail")
28	Priyansh	86	Pass	=IF(B28>=60,"Pass","Fail")
29	Ajay	95	Pass	=IF(B29>=60,"Pass","Fail")
30

Example 2 - Nested If Statements

When one IF function is used inside another IF function to check more conditions to return more results. This refers “nested if”.

In below student marks table, we want the result as “First Division” for those students who have obtained marks 75 and more.

	A	B	C	D	E
34	Student Name	Marks	Result
35	Deepak	56	Fail	=IF(B35>=85,"First Devision",IF(B35>=60,"Pass","Fail"))
36	Neeraj	78	Pass	=IF(B36>=85,"First Devision",IF(B36>=60,"Pass","Fail"))
37	Roshni	69	Pass	=IF(B37>=85,"First Devision",IF(B37>=60,"Pass","Fail"))
38	Aamir	82	Pass	=IF(B38>=85,"First Devision",IF(B38>=60,"Pass","Fail"))
39	Rohit	42	Fail	=IF(B39>=85,"First Devision",IF(B39>=60,"Pass","Fail"))
40	Pinky	85	First Devision	=IF(B40>=85,"First Devision",IF(B40>=60,"Pass","Fail"))
41	Prashant	53	Fail	=IF(B41>=85,"First Devision",IF(B41>=60,"Pass","Fail"))
42	Chhavi	78	Pass	=IF(B42>=85,"First Devision",IF(B42>=60,"Pass","Fail"))
43	Priyansh	86	First Devision	=IF(B43>=85,"First Devision",IF(B43>=60,"Pass","Fail"))
44	Ajay	95	First Devision	=IF(B44>=85,"First Devision",IF(B44>=60,"Pass","Fail"))
45

We can add more conditions by using AND() Function & OR() Function within IF() Function. And 64 IF() Functions can be nested.

REPLACE in Excel

<<  Previous

Next  >>

ISBLANK  In Excel