IF Function in Excel
IF is widely used function in excel. IF function in excel help us to make logical comparisons between value and what we expect.
For comparison test with IF function we can use logical comparators. There are six logical comparators in excel. Please find below summary of logical comparators.
|>=||Greater than or equal to|
|<=||Less than or equal to|
|<>||Not equal to|
IF: Return one result if TRUE, another if FALSE.
IF Formula Syntax :
logical_test : A value or logical expression that can be evaluated as TRUE or FALSE.
value_if_true : [optional] The value to return when logical_test evaluates to TRUE.
value_if_false : [optional] The value to return when logical_test evaluates to FALSE.
Tutorial of IF formula in Excel
In the above example, we have a list of students with their score. If we want to set two class out of all named “FAIL” & “PASS” For that, we set a function IF returns “FAIL” else it returns “PASS”.
Multiple IF statement in Excel (Nested IF Function)
It is possible to use multiple IF statement in excel in one formula. We can use 7 numbers of IF statement in one formula.
Example of Multiple IF statement in Excel
In above example we start testing to see if score is below 51, IF true returns “E”, IF FALSE we move into the next IF function. Now we will test if score is less than 61, IF true returns “D”. IF FALSE we move into the next IF function. IF score is less than 71. IF true return will be “C”. IF FALSE we move into next IF FUNCTION, IF score is less than 81, return will be “B”, IF FALSE we need “A”.
Note: – In this case it is important to move in one direction only, either from High to low & Low to high or high to low. IF function returns a result whenever test returns True.
IF function with Logical test- IF(OR)
Let’s calculate commission of Sales person based on Sales Goal and Number of accounts. Here Sales person either need to exceed sales Goal OR number of account goal to earn commission.
Since Robert has Total sales $ 11,000 >= Sales Goal $ 7,000, OR number of accounts 6> Account Goal 5, he is eligible for Bonus. Steeve is neither meeting Sales Goal nor meeting number of account Goal not eligible for Commission.
IF function with Logical tests- IF(AND)
Similarly calculate incentive of Sales person based on Sales Goal and Bonus Goal. Here Sales person need to exceed sales Goal AND Bonus Goal to Earn Bonus.
Robert is eligible for Bonus as he is meeting both conditions
(i)Total sales ($11,000)>= Sales Goal ($7,000)
(ii) Total Sales ($ 11,000)>= Bonus Goal ($10,500)