IF function in Excel formulas for numbers, text, dates step by step

In my last couple of post I have frequently mentioned about IF function, now its' the time to discuss about IF() function in detail. Frankly speaking I love this function in Microsoft Excel as it helps me to derive data properly and its' a beautiful and very useful function in Microsoft Excel.

As you know that a Computer is different from a Calculator because a Computer has such a processor that can analyze data Mathematically and the most important Logically which differentiate a computer from a calculator and the IF function is the best example of  the logical analysis of a processor.

The IF function is one of the most popular and useful functions in Excel. We use the IF function to ask Excel to test a given condition logically and to return one value if the condition is met, and another value if the condition is not fulfilled.

In this article, we are going to learn the syntax and common usages of Excel IF function step by step, and then will have a closer look at formula examples that will hopefully prove helpful both to beginners and experienced or advanced level Excel users.

So let us move to the syntax directly.

Syntax

IF(logical_test, [value_if_true], [value_if_false])

The syntax is very simple, it consists of three parts or parameters or arguments. 


1. Conditional checking part;
2. What would be if Conditional checking returns TRUE and
3. What would be if Conditional checking returns FALSE and

One suggestion, please keep in mind these three things step by step of IF function, it will never betray you and I hope this function will be the best friendly function to you.

Parameters or Arguments

logical_test

The condition to be checked. The output of this Condition would be either TRUE or FASE and the condition can specify a text value, date, number, or any comparison operator.

value_if_true

The output to returns TRUE when the logical test evaluates to TRUE, i.e. if the condition is met or satisfied. This is Optional (we'll discuss it with an example)

For example, the following formula will return the text "OK" if a value in cell A1 is greater than 100: =IF(A1>100, "OK") Optional. It is the value that is returned if condition evaluates to TRUE.

value_if_false

This parameter is also Optional. It is the value that is return if condition evaluates to FALSE.

Let us understand it further with the below image.

Syntax of IF function in Excel

The outcome of  IF() function is  one, but it can be TRUE or FALSE depending upon the condition provided. For Example if  I write the condition in this function  as =IF(10>9), then as you know that it is a Logical True or Correct (because 10 is greater than 9) so the output will be TRUE and now if we put a Statement in the second part of this function i.e., value_if_true as "Yes its True" like =IF(10>9, "Yes its True") then this function will print this statement in the cell.

But what will be the output if we change the condition as ==IF(10>15) ? The output will be a printed statement "FALSE", because Excel's IF() function by default print the word "FALSE" if condition is dissatisfied and no parameter is given for the FALSE output, i.e., the second part value_if_false. So we will write another statement as "Condition not met" for the second part i.e., value_if_false, now the whole formula will become  =IF(10>15, "Yes its True", "Condition not met").

Can you guess what would be result of this formula in Excel ? Yes, the result of this formula will be printed in Excel as "Condition not met" because the provided condition is not satisfied or met. Again if we change the condition as =IF(10>5, "Yes its True", "Condition not met") the output will be Yes its True" because here the given condition is satisfied.

Now lets' modify the above picture as below and it will help you further to understand the working of this function better.

Parts of IF Function

Now the obvious question comes in mind when to use this function in excel ? The simplest answer is whenever you need to judge some value in your data or table with another value you can use this function, may be a text value or a number or text and number both combined i.e., alphanumeric text etc.

How ? Lets understand this one by one with examples.

But before that let us concentrate on some featured output of IF() function. Earlier in this discussion I mentioned that within the IF function value_if_true and value_if_false can be omited. Let's see what happens when this parameters are omited.

So, here we have two options, either we can omit value_if_true or can omit value_if_false.

      a)    If value_if_true is omitted:

If the value_if_true argument is omitted in Excel IF formula (i.e. there is only a comma following logical_test), the IF function returns zero (0) when the condition is met. Here is an example of such a formula: =IF(A1>10,, "Not Matching")

If you don't want your IF formula to display any value when the condition is met, enter double quotes ("") in the second parameter, like this: =IF(B1>10, "", "False part of IF function") 

Technically, in this case the formula returns an empty string, which is invisible to the user but perceivable to other Excel functions. It looks better than printing a zero in the table.

     b)    If value_if_false is omitted.

But if you don't care what happens if the specified condition is not met, you can omit the 3rd parameter in your Excel IF formula value_if_false, which will result in the following.

If the logical test evaluates to FALSE and the value_if_false parameter is omitted (there is just a closing bracket after the value_if_true argument), the IF function returns or prints the logical value FALSE. It's a bit unexpected, isn't it? Here is an example of such a formula: =IF(A1>10, "OK")

If you put a comma after the value_if_true argument, your IF function will returns 0, which doesn't make much sense either: =IF(A1>10, "OK",)

And again, the most reasonable approach is to put "" in the third argument, in this case you will have empty cells when the condition is not met: =IF(B1>10, "TRUE part of IF function", "")

Let us have some example on IF() Function in different conditions.

IF examples for numbers

The use of the IF function with numeric values is based on using different comparison operators to express your conditions. Operators are used to compare between different numbers, it may be 'greater than' or 'less than' or 'greater than or equal to' or 'less than equal to' etc., and depending upon the conditions given in IF() function the result is printed in Cells. Therefore, let us first understand the meaning of different operators and their uses in IF function.

Operators in Excel IF

The use of IF function with text

IF function is not case sensitive by default. What it means for you is that logical tests for text values do not recognize case in usual IF formulas. Suppose in your table some text is written like "Test Text" and you give condition like =IF(B3="test text","Do this","Do that").

While using IF() function is used to compare two texts in two columns or cells here would be two options

1. Either you check that these texts are equal or

2. These texts are not equal.

So, the operators hear would be '=' for case 1 and '<>' for case 2.

How IF function works

Now if you need to apply the case sensitivity in IF function then what would you do ?

Simple, you need another function which is case sensitive  and make a nested function of it and the function is 'EXACT()' function.

So, our above mentioned  formula becomes

 =IF(EXACT(L1,"Test Text"),"Do this","Do that")

IF with Exact Function in Excel

The Syntax of  EXACT() function is =EXACT(text 1, text 2), its' very simple text 1 is one text (I have declared it as reference cell 'L1') and text 2 is another text.

The use of IF function to perform a math operation and return a result

In real life sometimes we need to calculate a value mathematically depending on the outcome of  a condition supplied to IF function. So, instead of returning certain values, you can make your IF formula to test the specified condition, perform a corresponding math operation and return a value based on the result. You can do this by using arithmetic operators or other Excel functions in the value_if_true and /or value_if_false arguments. Here are just a couple of formula examples:

Example 1: =IF(A1>B1, C3*100, C3+50)

The formula compares the values in cells A1 and B1, and if A1 is greater than B1, it multiplies the value in cell C3 by 100, otherwise it makes addition to C3 by 50.

Example 2: =IF(A1<>B1, SUM(A1:E1), "")

The formula compares the values in cells A1 and B1, and if A1 is not equal to B1, the formula returns the sum of values in cells A1:E1, an empty string otherwise.

Using the IF function in Excel - formula examples

The use of the IF function with numeric values is based on using different comparison operators to express your conditions. I have already provided the table above.

Note. When using text values as parameters for your IF formulas, remember to always enclose them in "double quotes".

Use Excel IF function with dates

The conditional checking part in IF function for dates are not same as numbers. You may think that IF formulas for dates are identical to IF functions for numeric and text values that we've just discussed. But, it is not so.

Lets' see how to compare dates in Excel IF functions.

IF cannot recognize dates and interprets them as mere text strings, which is why you cannot express your logical test simply as >"17-07-2017" or >17-07-2017.

Example. IF formulas for dates with DATEVALUE function

To make the Excel IF function to recognize a date in your logical test as a date, you have to wrap it in the DATEVALUE function i.e., before the date, like this DATEVALUE("17-07-2017"). The complete IF formula will take the following shape:

=IF(C2<DATEVALUE("17-07-2017"), "Today", "Recheck")

Compare Date in IF function using DATEVALUE function

I Hope, the above examples have helped you to understand the general logic of the IF function. In practice, however, you would often want a single IF formula to check multiple conditions, and our next article will show you how to tackle this task. In addition, we will also explore nested IF functions, array IF formulas, 

IFEFFOR and IFNA functions and more.

Please stay tuned and thank you for reading!