Basically, if functions are used to compare values or to do something when the condition is true or false. This function can also be used with AND, OR, NOT functions. Let us see in detail with an example.
Syntax: =if(Condition, "Value if true", "Value if false")
For example, we have two salespeople A and B with their daily sales. So, we are going to compare who has sold more products daily.
As mentioned earlier, This function is basically for comparing two or more values. So we use if function to compare A and B's daily sales.
First day sales of A and B are 30 and 29 respectively. Obviously, it is clear from seeing that A has sold more than B. In excel we do it by comparing the cell value "A2" which is A's First-day sale with the cell "B2" which is B's first-day
sale. The condition for checking is A2>B2.
If the condition is true that is A2>B2 then "A" is printed else "B" is printed.
This is the same as above instead, we compare SUM value and interpret the result.
In this example, we are going to check whether the daily target is achieved. The daily target is to sell 60 products.
Syntax: =if(SUM(A2:B2)>60,"Done","NOT DONE")
If they both combined sold 60 products or above the condition is satisfied and it prints "DONE" or else "NOT DONE".
This function returns true when both conditions are satisfied and returns false when both or either one conditions are not satisfied.
The below example returns true when both A and B have sold more than or equal to 30 and returns false when either A or B have sold less than 30.
This function returns true when either one condition is satisfied and returns false if both conditions are not satisfied.
The below example returns true when either A or B have sold more than or equal to 30 and returns false when both A and B have sold less than 30.
This function is the same as OR but we add NOT to it. This function returns true when either the conditions are not satisfied and returns false when the conditions are satisfied.
As you can see, the function returned true since both cell A5 and B5 are not greater than equal to 30.
Nested if are used when there are many criteria to compare value. In the below example we are checking the cell A2 value with different criteria.
If A2 value is less than or equal to 9 it returns 1 star, else if A2 value is less than or equal to 19 it returns 2 stars, else if A2 value is less than or equal to 29 it returns 3 stars, else if A2 value is less than or equal to 35
it returns 4 stars, else if all the above conditions are not true then it returns 5 stars. Check the below picture for criteria table.
Hope you liked this article, if so please share it with your friends.
Detailed Video: How to use if function in Excel, including AND, OR, and NOT.