Advance Excel Learning - IF Formulla - Software Applications - MS Excel - TIK Share
1 Likes 1984 Views

# 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.

#### 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