Advance Excel Learning - IF Formulla - Software Applications - MS Excel - TIK Share

Advance Excel Learning - IF Formulla


CA Pulkit Sharma

2013-03-11

=IF(logical_test,value_if_true,value_if_false)                                                                                                                                                        

=if(Put the condition,value if condition is satisfied,Value if Condition is not satisfied)

The "IF" formulla is one of best advance formulla available in excel. The formulla behaves on the logic of If and Than.

Using this formulla user can set a predefined rule that if a value is equal to x than what should be the result and if its not x than what should be the result.

 

Using above formulla you can always get your basic data work done. We will take a bigger and practical useful example of how to handle various figure using if function.

Student

Marks(B8)

Result

Amar

45

=IF(B8>35,"PASS","FAIL")

Akbar

40

PASS

Anthony

46

PASS

Ram

80

PASS

Shyam

33

FAIL

Geeta

43

PASS

Sheeta

21

FAIL

 

Above formulla is a simple case of how  by using formulla a lot of time can be saved. One has to just put the formulla and than copy and paste in all other cells.

There are other ways to do same thing using different formulla, like conditional formatting. But in letter classess you will understand why we have used PASS and FAIL.

Now take above example little further, as already said that in Excel different formullas can be used in one formulla. We will use  different formullas in  combination with IF formulla in next example.

 

Take below example, to pass in class student has to get minimum 35 in each subject.

Student

Science(B22)

Maths(C22)

Social(D22)

Hindi(E22)

Sanskrit(F22)

Total

Result

Amar

60

43

50

56

60

269

=IF(MIN(B22:F22)<35,"FAIL","PASS")

Akbar

50

60

40

42

65

257

PASS

Anthony

30

25

50

55

58

218

FAIL

Ram

80

89

90

98

95

452

PASS

Shyam

33

34

56

40

67

230

FAIL

Geeta

35

78

85

80

90

368

PASS

Sheeta

32

34

21

34

45

166

FAIL

 

In above example, you can notice that instead of comparing a perticular cell we have put "MIN" Formulla along with "IF" formulla.

Now suppose student Should get Total Marks more than 200 and 35 in each subject to pass, than also "IF" formulla can be used.

 

Take below example, to pass in class student has to get minimum 35 in each subject and Total 200 Marks.

Student

Science(B22)

Maths(C22)

Social(D22)

Hindi(E22)

Sansk(F22)

Total

Result

Amar

60

43

50

56

60

=SUM(B34:F34)

=IF(MIN(B22:F22)<35,"FAIL",IF(G34>200,"PASS","FAIL"))

Akbar

36

40

40

42

36

194

FAIL

Anthony

30

25

50

55

58

218

FAIL

Ram

80

89

90

98

95

452

PASS

Shyam

33

34

56

40

67

230

FAIL

Geeta

35

78

85

80

90

368

PASS

Sheeta

32

34

21

34

45

166

FAIL

 

you would have noticed that I  used IF formulla twice, I  replaced the "PASS" with  IF Formulla. We can use IF formulla in many ways.

 

Exercise:                                                                                   

1. Well if you understood the formulla than try to use "IF" Formulla and find which student PASSED and Which FAILED, if

      Each student has to get 40% in each subject.

      Each student has to get 40% in each subject and total 45% marks.

      Consider paper is total of 95 Marks, ie percentage = (marks/95)*100.

 

 

Have Any Problem in Understanding or any doubt please do write in Extend Section.

 

Download the file for offline reading and excercise  if-formulla.zip 

 

rajeshwari uniyal

2013-10-07

Hi guyzzz I am learning if and multiple if option want to solve the query only by if or multiple if Eg Special Order Product Offer Value Loc Snr member Citi Card Wood Yes £2,000 GGN Yes No Yes Glass No £2,000 GGN No No No Cement Yes £500 Noida Yes Yes No Turf Yes £3,000 Noida No No Yes condition one--- is should be filled 3 condition ,offer value= yes,order value = 2000 or greater than 2000, loc sholud be GGN if all 3 condition is fulfilled vl provide 10% discount or if not then value should be same condition 2 - is ...Snr= yes tehn can provide 10% discount,or member= yes then can provide 10% discount or citi card = yes then can provide same 10% so wat should be formula IF(E5="yes",IF(F5>=2000,IF(G5="GGN",IF(H5="YES",F5-F5*10%,IF(I5="yes",F5-F5*10%,IF(J5="yes",F5-F5*10%,F5)f5-f5*10%,f5),f5),f5)..... but its showing error please resolve the same

Reply