IF() in excel

IF() is a logical test in excel that determines that the condition given in the function is correct in the excel data or not. We can directly say that it runs a test and returns True or False.

The formula is given by:

The above formula specifies that if the logic specified is correct, excel will return the value specified in [value_if_true]. If the logic specified is incorrect, it will return [value_if_false].

Where,

Logical_test: It includes the expression to be evaluated.

[value_if_true]: It includes the value to be returned if the expression founds to be true.

[value_if_true]: It includes the value to be returned if the expression founds to be false.

For example,

A = 30

B = 50

Let the condition be A>B

Now, excel will check the condition and will match the specified values in the variable. If the condition is found to be correct, it return TRUE, else it returns FALSE.

In the above example, A < B. But, according to the specified condition A > B

Hence, excel will return False.

The above example can be expressed as:

IF(A>B, TRUE, FALSE)

Note: We can only specify TRUE, FALSE, and numbers directly in the IF() function. If we want to specify other parameters (pass, fail), we must enclose such parameters in double quotes.

Let’s discuss the method to pass different values.

Case 1: True or False

IF() in excel

Where,

B2 and C2 are the cell numbers.

If the above logical test is true (B2 is greater than C2), the function will return TRUE. If it is false (B2 is smaller than C2), the IF() function will return FALSE.

Case 2: Pass or fail

The pass and fail are the parameters other than specified in excel. For this, we need to enclose such values within double quotes. Otherwise, excel will return an error.

It is given by:

IF() in excel

Where,

B2 and C2 are the cell numbers.

If the above logical test is true (B2 is greater than C2), the function will return pass. If it is false (B2 is smaller than C2), the IF() function will return fail.

Case 3: 1 or 0

IF() in excel

Where,

B2 and C2 are the cell numbers.

If the above logical test is true (B2 is greater than C2), the function will return 1. If it is false (B2 is smaller than C2), the IF() function will return 0.

Case 4: greater or lesser

IF() in excel

Where,

B2 and C2 are the cell numbers.

If the above logical test is true (B2 is greater than C2), the function will return B greater. If it is false (B2 is smaller than C2), the IF() function will return C greater.

We can also specify the above logical test as:

Similarly, as per the above cases, we can pass any parameter in the function according to the data in excel. We will discuss practical examples for better understanding later in the topic.

IF() in excel

Case 5: Formula as a result

Here, the value returned by the function will work as the formula.

It is given by:

IF() in excel

Where,

C2 is the cell number

If the above logical test is true (value in cell C2 is greater than 25), the function will return the value in a formula. i.e., it will multiply the resulted cell value by 5% (C2 * 5/100). If it is false (C2 is smaller than 25), the IF() function will return the resulted cell value with 2% (C2 * 2/100).

Similarly, we can use any formula as a combination of different logical operators.

Case 6: IF() with AND function

The IF() function with AND comprises of two logical test conditions. The AND function will produce the output as true if both the specified conditions are correct. Otherwise, it will return the value by considering it incorrect.

It is given by:

IF() in excel

Where,

B2 and C2 are the cell numbers

If the above logical test is true (B2 is greater than 25 and C2 is less than 30), the function will return OK. If one or both the logical test is found to be false, the IF() function will return –.

Case 7: IF() with OR function

The IF() function with OR also comprises of two logical test conditions. The OR function will produce the output as false if both of the specified conditions are incorrect. Otherwise, it will return the value true if either or both the logical test specified in the function is correct.

It is given by:

IF() in excel

Where,

B2 and C2 are the cell numbers

If the above logical test is true (B2 is greater than 25 or C2 is less than 30 or both conditions satisfy), the function will return OK. If both the logical tests are false, the IF() function will return –.

Case 8: IF() with NOT function

As the name implies, the NOT function with IF() will work in a reverse manner. It means that if a condition is true, it will return the value in place of false. It only comprises of a single logical test within the function.

The declaration is given by:

=IF(NOT(Something is true), value if true, value if false)

For example,

IF() in excel

Where,

G2 is the cell number

If the value found in the cell G2 is red, the function will return FALSE. Otherwise, it will return TRUE. For example, if G2 were green, the function would return TRUE.

Examples

Let consider some examples.

Example 1: To check whether a student has failed or passed the examination.

Let’s consider the passing marks to be 60 out of 100. Here, we will find the students who passed or failed the examination.

The steps are as follows:

  1. Click on the first cell of the result column.
  2. Type ‘=IF(C3>60,”PASS”,”FAIL”),’ as shown below:
    IF() in excel
  3. Press Enter. The result of the first student will appear.
    We can either randomly type the same function on each cell or perform the drag and drop method.
  4. Click on the bottom-right corner of the first cell and drop down to the last cell, as shown below:
    IF() in excel
    The same formula within the function will be applied automatically to the rest cells of the column. So, among eight students, five passed, and three failed.

Example 2: To increment the salary based on the performance.

Here, we will increment the salary based on the performance marks out of 10 of various employees in an organization.

The steps are as follows:

  1. Click on the first cell of the increment% column.
  2. Type ‘=IF(C3>7,”40% “,”15%”),’ as shown below:
    IF() in excel
  3. Press Enter. The increment% of the first student will appear.
  4. Click on the bottom-right corner of the first cell and drop down to the last cell, as shown below:
    IF() in excel

The same formula within the function will be applied automatically to the rest cells of the column. So, among ten employees, four employees have got the increment of 15%, and six employees got the increment of 40%.

Example 3: Using the IF() function to produce a formula as a result

Here, we will provide the discount price for commodities with respective price ranges.

The steps are as follows:

a. Click on the first cell of the discount column, as shown below:

IF() in excel

b. Type ‘=IF(C3>300,C3*10%,C3*20%)’

c. Press Enter. The desired discount value will appear.

d. Click on the bottom-right corner of the first cell and drop down to the last cell, as shown below:

IF() in excel

The same formula within the function will be applied automatically to the rest cells of the column. So, there are two commodities whose price is less than 300. Hence, discount of 20% will be given on such commodities, while discount on other commodities will be 10%.

Example 4: Using AND and OR with the IF() function

Consider the below data in excel.

IF() in excel

Using AND function with IF()

Here, we will find the students who got grade A, which is given to those who score above 90 in all the subjects.

The steps are as follows:

e. Click on the first cell of the grade column.

f. Type ‘=IF(AND(B3>90, C3>90),”Grade A “,”–“).’

g. Press Enter. The desired result of the first student will appear.

h. Click on the bottom-right corner of the first cell and drop down to the last cell, as shown below:

IF() in excel

The same formula within the function will be applied automatically to the rest cells of the column. So, among eight students in the class, three students have got Grade A.

Here, the AND function will work by checking both the logical tests. If a student has scored above 90 in both the subjects, he/she scores grade A. Otherwise, not.

Using OR function with IF()

Here, we will find the students who are failed in either of the subjects. It will provide the list of students who need to attend an extra class for the respective subject. The passing numbers are 50.

The steps are as follows:

  1. Click on the first cell of the Extra class column.
  2. Type ‘=IF(OR(C3 <50, D3<50),”YES “,”NO”),’ as shown below:
    IF() in excel
  3. Press Enter. The desired result of the first student will appear.
  4. Click on the bottom-right corner of the first cell and drop down to the last cell, as shown below:
    IF() in excel

The same formula within the function will be applied automatically to the rest cells of the column. So, among eight students in the class, three students need to attend the extra classes.

Here, the OR function will work by checking both the logical tests. If a student has scored less than 50 in either or both of the subjects, he/she needs to attend the class. Otherwise, not.


Next TopicREPT() in excel