Advance Excel (IF Formula)

IF FORMULA IN EXCEL

INTRODUCTION

The If formula in excel is used to implement two or more than two conditions, therefore it can be used to create different types of reports like grading system.



IF - PASS & FAIL FORMULA

=IF(A2>=33,"Pass","Fail")

Explanation: In the above example "A2" is a cell address, there we are checking, that the value of "A2" >= 33 is or not.

If yes then the message should be print as "Pass" otherwise "Fail" should be print.  

GRADING SYSTEM BY IF FORMULA

=IF(G3>=75,"A",IF(G3>=50,"B",IF(G3>=33,"C",IF(G3<33,"F"))))

Explanation: In the above example grade calculating with help of average, therefore "G3" is an address of first average cell.  

FIND UNIVERSITY RESULT BY IF FORMULA

=IF(COUNTIF(A2:E2,"<33")=0,"Pass",IF(COUNTIF(A2:E2,"<33")>2,"Fail",COUNTIF(A2:E2,"<33")))

Explanation: In the above example, we are calculating university result. and to perform this task, we need to insert "Countif' function inside the "If" function.

In the formula, we are finding total marks which less than 33. 

If found more than 2 then the message "Fail" should be print, if not found anyone, then message "Pass" should be print, otherwise, the number of "ER" subjects should be print.

Calculate Income Tax By IF

=IF(A2>=25000,(A2*5%),IF(A2>=50000,(A2*15%),(A2*0%)))

Explanation: In the above formula, we are calculating income tax by basic salary amount.
If salary is greater than 25,000 then 5% income tax should be calculated, if basic salary is greater than 50,000 then 15% income tax should be calculated and if salary is less than 25,000 then 0 (Zero) calculated.

FIND MISSING RANGE NUMBERS (1 - 10)
There are several methods to find missing rank and number between a particular series and here I am going to explain the simplest method to find missing range/number by IF formula.

Formula: =IF(A3-A2=1,"","Missing")

HOME