In this article I will explain the IF() function. The IF() function receives 3 input parameters. The first input parameter is a logical test. The logical test will either equate to True or False. If the logical test equates to TRUE then the second parameter will be returned  If the logical test equates to False then the 3rd parameter will be returned. You can download the workbook for this article here.

Jump To:

• Syntax
• Example 1
• Example 2
• Example 2

Syntax:

=IF(Logical Test, Value if True, Value if False)


Logical Test: An expression which equates to True or False.

Value if True: This value will be returned if the logical test equates to True.

Value if False: This value will be returned if the logical test equates to False.

Example 1, Simple IF() Formula:

In this example there is a series of numbers in column A. If the value in column A is negative we want the text “NEGATIVE” to be printed in column B. If the value in column A is positive we want the text “POSITIVE” to be printed in column B.

This can be achieved by writing the following formula in Cell B1:

=IF(A1<0,"NEGATIVE", "POSITIVE")


Result:

This can be extended to the rest  of the column by copying or dragging the formula:

Example 2, Nested IF() Functions:

Lets say that not only did we want to check whether the numbers in column A are positive or negative, but we also want to make sure if its zero or not. The if function only accepts one logical statement, but we need two logical statements here:

1: Check if its negative or not

2: If its not negative then check if it is zero not

Its obvious that if both statements above return false then its a positive number. In order to be able to use 2 logical statement we would need to use a nested IF() function. In this example print the formula below in cell B1:

=IF(A1<0,"NEGATIVE", IF(A1=0, "ZERO", "POSITIVE"))


Part 1:

=IF(A1<0,"NEGATIVE", IF(A1=0, "ZERO", "POSITIVE"))


The highlighted part checks if the value in cell A1 is negative.

Part 2:

=IF(A1<0,"NEGATIVE", IF(A1=0, "ZERO", "POSITIVE"))


If part 1 returned True the highlighted text above is printed in cell B1.

Part 3:

=IF(A1<0,"NEGATIVE", IF(A1=0, "ZERO", "POSITIVE"))


If part 1 returns False, then the highlighted part above (which is another IF() function) is evaluated and printed in cell B1.

Part 3.1:

=IF(A1<0,"NEGATIVE", IF(A1=0, "ZERO", "POSITIVE"))


The highlighted part above checks if the value in cell A1 is zero.

Part 3.2:

=IF(A1<0,"NEGATIVE", IF(A1=0, "ZERO", "POSITIVE"))


If part 3.1 returns True then the highlighted text above (“Zero”) will be the evaluated value for the highlighted text in part 3.

Part 3.3:

=IF(A1<0,"NEGATIVE", IF(A1=0, "ZERO", "POSITIVE"))


If part 3.1 returns False then the highlighted text above (“POSITIVE”) will be the evaluated value for the highlighted text in part 3.

Drag or copy the formula to the rest of the cells in column B:

Example 3, Nested IF(), One Step Further:

Lets say not only did we want to check if the value is positive, negative or zero, but also we wanted to check if the value is numeric or not. Write the function below in cell B1, and drag or copy it to the rest of the column:

=IF(ISNUMBER(A1), IF(A1<0,"NEGATIVE", IF(A1=0, "ZERO", "POSITIVE")), "NON NUMERIC")


You can download the workbook for this article here.

See Also:

• Excel Functions and Formulas, Sample #1, Split String Based on Delimiter.
• Excel Functions and Formulas Tutorial / Sample, Text and String #2 (Advanced)
• Excel Speed, IF (Formula vs VBA)
• IF OR statements in VBA

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website  www.software-solutions-online.com