Excel Function: IF ( logical_test, value_if_true, value_if_false )

Now in coming two weeks I have decided to discuss functions available in the stable of Microsoft Excel.  I have taken Logical function to start with, as I found them most useful will working data (this is my personal view :) though). To start with I am gonna explain IF function with an example.

The IF function is one of Excel's logical functions and it tests to see if a certain condition in a spreadsheet is true or false.

The syntax for the IF function is:

=IF (logical_test, value_if_true, value_if_false)

logical_test    - a value or expression that is tested to see if it is true or false.
value_if_true  - the value that is displayed if logical_test is true.
value_if_false - the value that is displayed if logical_test is false.

Some of the conditional operator you need to know:
<   - Less Than
>= - Greater than Or Equal To
<= - Less than Or Equal To
<> - Not Equal To

The following two images will tell you how we can use IF function. In cell A1 have value 15 and I want to populate test "Good" or "Bad" based on this value. For example if value is greater than 10 then it is "Good" else "Bad".


Formula which I have entered in cell A2: =IF(A1>10,"Good","Bad")

Similarly you can see cell B2 is giving me "Bad" because A2 is less than 10.

You can use this many ways and under numerous conditions. Few days back one of my teacher friend came to me and we were discussing how we can use Excel to help him in work. He gave me a situation where based on a number he wants to assign grades to his student. The parameters to grades were as follows:

A If the student scores 85 or above
B If the student scores 65 to 84
C If the student scores 50 to 64
D If the student scores 33 to 49
FAIL If the student scores below 33

An example of this data looks like this:

To solve his problem I used IF Function. I created a similar table below his data and wrote a formula as B12: =IF(B2>=85, "A", IF(B2>=65, "B", IF(B2>=50, "C", IF(B2 >=33, "D", "Fail" ) ) ) )

I draged the formaula cross the data sheet and got my result.

I hope this is clear to everyone. I know many of you are already using this function.  Kindly let us know how do you use this function in your daily life.  You can post your experiences and example in the comment box, it will help us in learning new things.  "Happy Learning"

Pass this to your friends and colleagues this may also help them! 

To download the example file click here

1 comment: