How to use Excel IF function with AND, OR Combined with Examples

Hi Friends,

After the discussion about IF() function in Excel I am assuming that you are now quite comfortable with this function to use its' basic properties. So, in this article I'll go little more deeper use of IF() function. Today, I shall discuss about Nested IF with few examples and with some logical function like AND(), OR() etc.

Nested IF with AND or OR function is very useful where you get two or more parameters as input and you have to derive the data based upon the conditions provided, let us take an example, if somebody asks you to prepare a mark sheet for a school and gives you conditions that if any student scores 50% or more in 5 subjects and in aggregate of 60% then only he/she passes else fails. At this time you need to use nested IFs and nested function like AND or OR function.

Nested IF function in Excel

In my last article and of course in the YouTube Videos I mentioned the word Nested repetitively and intentionally I forced you to keep in mind that Nested  Function is a function within a function. Well, that's true, a nested function is a function covered by another function may be the same function or different function(s).

Now, we'll look into the nested property of IF() function and obviously how to use it with simple examples. We'll use the function AND() and the function OR()  in IF() function by the example of a mark sheet of a fictitious school and fictitious students in this article a little and in the upcoming article in details.

Lets' first understand the logical functions (AND & OR).

 Logical AND: A logical AND() function is used to check whether one or more given conditions are TRUE or FALSE. The AND() function returns TRUE if all the supplied arguments or conditions are TRUE, if one condition is FALSE then it will return FALSE. Its very simple.

Syntax

=AND(logical1, logical2, logical3, ...)

Here logical1, logical2, logical3 etc are the conditions to be provided one after another like A2>50 or A2<=100 etc. separated by comma(s).

You can enter upto 255 such logical value within this function.

How AND() function works ?

Let suppose in a given table of a mark sheet two numbers for two subjects are given in A2 and A3 as 79 and 120 respectively and given a condition to check whether both these numbers  are greater than 50 or not.

From the syntax of AND() function, if we write the formula as =AND(A2>50, A3>50), the output would be TRUE because both these numbers are greater than 50. Now suppose A2 is 45 then the output of this function would be FALSE because one of these arguments are false.

AND() function returns TRUE when all the given conditions are TRUE and if any one of these conditions is false it returns FALSE (Boolean) logical output.

In the above small example I want to convey you that if any one of the parameter or argument provided to AND() function is FALSE then the whole formula returns FALSE and if all the arguments are TRUE then this function returns TRUE. This is the way how AND function works in Excel.

Example of AND function

Now another useful function is OR(). Lets' find how this function works.

Syntax

=OR(logical1, logical2, logical3, ...)

Like AND function logical1, logical2, logical3 etc are the conditions to be provided one after another seperated by comma like A2>50, A2<=100 etc. And like AND function you can enter 1 to 255 logical tests within this formula.

How OR() function works ?

If we take the same example of marks for two subjects as 79 and 120 and make a little change in the condition as that if any of the number is greater than 100 then that student is qualified to another class.

Now using OR() function the formula now becomes =OR(A2>100,A3>100) when we press enter the output would be TRUE because one condition is satisfied but to see the effect of this function when all conditions are false let us change if the marks of A3 from 100 to 90 and rewrite the formula then this formula will return FALSE because all the given conditions are FALSE.

OR() function returns TRUE when any one of the given conditions is TRUE and if all the conditions is false it returns FALSE (Boolean) logical output.

We'll  use AND() function when the output needs all the given conditions to be TRUE and we'll use OR() function while the ouput needs only one condition to be satisfied.

I know at first it looks a little bit confusing but the below images  with example will make this very easy to understand.

Example of OR function

Let us take an example of a mark sheet. If we suppose that if any of the student in the below mark sheet has got below 45 marks in any subject then he/she fails, in this situation, we'll use the AND formula and it would be like

 =AND(D3>45,E3>45)  - for first student, output is TRUE because marks are greater than 45 in each subject

=AND(D4>45,E4>45)   - for second student, output is FALSE because Subject 2 marks is less than 45.

AND function Expanded

Now if we give condition that any student who achieved 45 marks or above in any subject will pass, then we have to use OR function and this formula becomes,

=OR(D3>45,E3>45) - for first student, output is TRUE as both subject's marks are greater than 45.

=OR(D4>45,E4>45) - for second student, output is TRUE because one of the subject's marks is greater than 45 although marks obtained in subject 2 is less than 45.

AND and OR function

So, this is how AND & OR function works.

How to use nested IF and AND function

To understand this we'll take the help of one example, let suppose that in the mark sheet table, if any student has got below 45 then he/she fails, otherwise he/she passes and we have to print the word Pass or Fail; then our formula becomes with IF and AND function combined or nested,

=IF(AND(D3>45,E3>45),"Pass","Fail").

Now recall my last article about the syntax of IF() function,

=IF(logical_test, value_if_true, value_if_false) or we can write theoretically =IF(Conditional test, value_if_true, value_if_false). Here AND(D3>45,E3>45) is my conditional test parameter with the IF function.

So, whenever this AND function returns TRUE, the value_if_true part of IF function gets printed, otherwise the value_if_false part is printed like the second student's example, =IF(AND(D4>45,E4>45),"Pass","Fail")

Graphically the use of AND  and IF function together is shown in the below image.

Working of IF and AND function in Excel

Similarly you can use OR function in place of AND function if such conditions are provided.

You can watch YouTube video clicking here or in below box. 

Hope this part was very easy for you, in my next article I'll show you some complex examples related to Nested IF function and other few important functions in excel with their formula, till then keep reading and keep watching my YouTube Videos.

Thank you...

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!

How and Where to use the ISNA Function with IF and VLOOKUP function

After discussing IFERROR() in Microsoft Excel Error Handling in previous post, now we should discuss about ISNA() function.
I would strongly recommend you to use IFERROR() in place of ISNA(), but it is better to have some knowledge about ISNA() function as ultimately its' the situation's demand where to use IFERROR() or ISNA() or ISERROR().

Description

The Microsoft Excel ISNA() function can be used to check for a #N/A (value not available) error Unlike IFERROR(), ISNA() will not mask each and every #error in MS Excel like #VALUE, #NAME, #NULL etc., ISNA() function will mask only #N/A errors in MS Excel.

The ISNA function is a built-in function in Excel that is categorized as an Information Function. The output of ISNA() is either TRUE or FALSE, the two most important logical output, as this is providing information we can categorize this function as information function in Microsoft Excel or Spreadsheet.

Now as the output of this function is logical i.e., either TRUE or FALSE we need to use another logical function IF() to omit the word TRUE or FALSE and printing the matching data (I'll discuss on IF() function latter, a  very important function) because while preparing some data you might not want to display the word TRUE or FALSE in your data. 
Let us look into the syntax of ISNA() function.

Syntax
The syntax for the ISNA function in Microsoft Excel is very simple:
ISNA( value )

Parameters or Arguments

Value
The 'value' means the data that you want to test. If value is a #N/A error value, this function will return TRUE. Otherwise, it will return FALSE. The ISNA() function can be used in two different ways.

a) Either you can give reference the cell to ISNA() function giving you #N/A error like =ISNA(B2) or 

b) You can use it within a function, i.e.,. as nested function like =ISNA(VLOOKUP(I5,B5:E20,2,0))

This function Applies To
               Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Let us have some practical example to understand this function.

Suppose we have two tables Table 1 and Table 2 as shown in the below picture. Now we need to get the value of Table 2 in NAME column corresponding to Employee Code from Table 1, so we use VLOOKUP() function to get the data. As the employee code 10040 and 10111 is not present in Table 1 it is showing us #N/A error (Value not available), we will mask this #N/A by ISNA().

How to use ISNA function in Excel errors

If you analyze the situation carefully (data output by VLOOKUP) and you want to print the text "Name Not Found" in place of #N/A then two things need to be done.

1) Find out where #N/A error comes and as it will return either TRUE or FALSE so 

2) we need to use some logical function to print the text "Name Not Found" in place of TRUE.

Again I would like to remind you that as the output is 'logical value' in ISNA() function, so if you use ISNA() with VLOOKUP() function and will fill the entire column 'NAME' (nested function i.e., a function within a function) then each and every result will be either TRUE or FALSE irrespective of data available or not in the table.

But, we have to be tricky while using this function. Refer to the below image, as the output is given by VLOOKUP() function, is either a Matching Column Data (Lik 'DDD') or a #N/A error, we need to print the matching data and in the same time print the text "Name Not Found" in place of #N/A.

How to use IF and ISNA function in Excel errors

So, what we'll do ? 

We'll insert ISNA() function before VLOOKUP() function and then move to the end of the formula to close the ISNA function by putting a bracket or closing parentheses (e.g., =ISNA(VLOOKUP($G3,$A$3:$D$12,2,0)) ) and now our formula can decide whether the output is giving a #N/A error or not by printing TRUE or FALSE. The first part is complete.

For the second part, we have to use IF() function, in brief, the syntax of IF() is =IF(logical_test, value _if_true, value_if_false). Here the whole formula is the logical_test part or parameter in IF() function, hence type IF( after the '=' sign and move to the end of the formula and put a comma IF() will ask for value_if_true, type "Name Not Found" (because ISNA() will return TRUE whenever it gets a #N/A error) including double quote as this a string/text and again put a comma IF() will ask for value_if_false, now the tricky part comes, copy the VLOOKUP() part from the formula and paste it and close the bracket because if ISNA() returns FALSE meaning VLOOKUP() has found the matching data and therefore, we need to print the data. Its' done...

The only drawback of ISNA() is it will show you either TRUE or FALSE as its result but in real life we need more, we need to rectify only that portion of the data which is giving a #N/A error and to make it we have to use another logical function IF(). If the situation is not demanding to use ISNA(), please do not use it, use IFERROR() function, its more productive. 

Why ? Please read the article on IFERROR().

Hope this discussion is helpful to you. Thank you...

#N/A #VALUE #DIV/0 #NAME? #REF #NUM #NULL #### error messages in Microsoft Excel, Error Handling in MS Excel

Hi friends,

While discussing about functions in Excel, often we get #N/A, #VALUE, #DIV/0, #NAME?, #REF, #NUM, #NULL, ####  or other types of errors while calculating or using formulas.

In today's discussion, I'll give you few ideas to handle these errors very efficiently.

Free Download Excel Working File
View in YouTube How to Eliminate Excel Error Very Easily and Quickly

First of all let us know the meaning of these errors, knowing the error type will help you to get rid of this problem very quickly and easily.

From the above table it is very clear why these errors occurs in excel, some are rectifiable within the formula some are not, they needs special care while preparing data or analyzing data.

So, let's discuss one by one...

1. "####" error : it is very simple just widen the column i.e., increase the width of the column.

The row of ##### occurs in a variety of instances, such as when:

a. an entered value is wider than the current cell width for a cell formatted for dates or times.

b. A formula entered into the cell formatted for numbers produces a result that is wider than the cell.

c. A number or text data, in excess of 253 characters,  is entered into a cell formatted for numbers dates, times, or accounting.

d. A negative number resides in a cell that has been formatted for dates or times. Dates and times in Excel must be positive.

2.#VALUE! error : in maximum case you will find this error is because of the different type of data types, for example if you add one cell containing number and the other cell having a text it will show you this error.

3. #DIV/0! error : if you try to divide some value by zero this error displayed, this is always means 'divide by zero' is happening.

The divisor or denominator in a division operation is equal to zero - either explicitly - such as =A5 / 0 - or as the result of a second calculation that has zero for a result. It might occur because a formula referencing a cell that is blank.

4. #N/A error : The most common error found in lookup formulas (VLOOKUP, HLOOKUP, INDEX and MATCH etc.), means lookup value not found, this error needs rectification in your formula and you can use a function to eliminate this error (not only this error but for maximum Excel errors) and it is =IFERROR(value, value_if_error) which I'll discuss letter in this post. 

But remember this error can be very usefully utilized in data analysis to find out the mismatch in two or more complicated and large data containing columns (I'll discuss it in my YouTube Video).

5. #NAME? error : this error is primarily occurs because of the typo error e.g., if you type =COUNTIIF (One 'I' is extra in this example) in place of =COUNTIF or SUUM instead of SUM function you will get this error.

6. #REF! error : reference error, as the name suggest this error is displayed because of non existing reference in the formula or it may display as you delete the data accidentally.

The possible reasons are :

a. individual cells or entire columns or rows containing data referenced in a formula are accidentally deleted.

b. data from one cell is moved (using cut and paste or drag and drop) into a cell that is referenced by a formula.

c. a formula contains a link - using OLE (Object Linking and Embedding) - to a program that is not currently running.

7. #NUM! error : Entering a non-numeric argument to a function that expects a numeric value. A function or a value that evaluates to a numeric value beyond the Excel’s capabilities to handle. Result of a function that could not find a valid value in spite multiple iterations. Very rare in real life data processing if you are a little bit cautious.

8. #NULL! error : occurs when the two or more cell references are separated incorrectly or unintentionally by a space in a formula.

Multiple cell references in a formula are separated by a space instead of a mathematical operator such as a plus sign ( + ) - example: =A1 A3+A5, or

the start and end points of cell ranges are separated by a space instead of by the range operator - the colon ( : ) - example: =SUM( A1 A5) or

individual cell references in a formula are separated by a space instead the union operator - the comma ( , ) - example: =SUM( A1 A3,A5) or

the intersect operator - the space character - is used intentionally, but the specified ranges do not intersect - example: =SUM(A1:A5 B1:B5)

How to Correct these errors ?

There are various techniques to correct this type of error display in Microsoft Excel, the best method we use is a function (and I personally love to use it always while typing a formula, it's a good habit) IFERROR()

Syntax

=IFERROR(value, value_if_true)

Use your formula in 'value' parameter and you can write anything in the parameter 'value_if_true' e.g., 

while calculating numbers you can write it '0' or if you need text to be displayed, you can write any text but within double quote e.g., "Value not found" etc. Please look carefully the below image, it will be more understandable.

#N/A #VALUE #DIV/0 #NAME? #REF #NUM #NULL & #### error messages in Microsoft Excel

You can use another function ISERROR()

Syntax

=ISERROR(VALUE)

Use your formula in 'value' parameter, it will return TRUE if error FOUND and FALSE if error NOT FOUND. But here you need to use IF logical function (I'll discuss in detail about this interesting logical function latter in this blog) to get the better result as described in the below image.

How to use ISERROR function in Excel

Sometimes you may get an #N/A error in VLOOKUP(), HLOOKUP(), MATCH() with INDEX() functions and although you can find or view the existence of the data but is not showing i.e., VLOOKUP returns #N/A error where the value exists by the formula like the image below. 

VLOOKUP #N/A Error in Excel

It's mainly because of the data type change, you may notice a little green triangle in the left-upper corner of the cell, it means that the data is in text format instead of number. A square box appears while this error occurs, you can click inside this box to view the message about the probable cause of this error. 

Youtube Link 1

Please view my YouTube Channel for more information regarding this errors, you'll get adequate information about this topic.

Thanks a lot...