OR Function

Like ‘AND’ logical function ‘OR’ function is also widely used in Microsoft Excel with other Logical functions like ‘IF’, ‘COUNTIF’, ‘SUMIF’ ‘AND’ etc., as well as other functions.Today I will discuss about the logical OR function in this article.

To understand OR function in Microsoft Excel let suppose I give you a funnel with a filter in it which can filter the return type of any condition.

Now, you pour all the condition to be tested (like, 100<90, 100 is less than 90) in this funnel and if ANY CONDITION return ‘TRUE’ then ‘OR’ function will return TRUE.

Let have another example to understand this function, suppose we have following data to be checked whether the return type is TRUE or FALSE.

Ø 90 < 75            à FALSE Statement

Ø 120 < 100        à FALSE Statement

Ø 75 < 200          à TRUE Statement

Ø 55 < 24            à FALSE Statement

Ø 34 < 23            à FALSE Statement

Ø 27 > 72            à FALSE Statement

From the above 6 statements we can understand that only ONE condition is TRUE, rest conditions are FALSE.

Therefore, ‘OR’ function will return TRUE.

How OR Function Works

Now let us discuss OR function in details.

Description

The Microsoft Excel OR function returns TRUE if any of the conditions are TRUE. Otherwise, it returns FALSE.

Syntax

The syntax for the OR function in Microsoft Excel is

=OR(logical1, [logical2...])

Note : Here logical means Conditions to be checked.

Parameters or Arguments

logical1

A condition to test that can either be TRUE or FALSE.

Logical2...

Conditions to test that can either be TRUE or FALSE. There can be up to 30 conditions.

Returns

The OR function returns TRUE if any of the conditions are TRUE.

The OR function returns FALSE if all conditions are FALSE.

Difference Between logical AND function & OR function

The syntax of both the function is almost same,

=OR(logical1, [logical2...])

=AND(logical1, [logical2...])

AND function returns TRUE when ALL the conditions are TRUE

on the contrary 

OR function returns TRUE when ANY ONE of the conditions is TRUE

I hope you have enjoyed this article. Take care…

Thank you for reading….

AND Function

Today I will discuss about a logical function ‘AND’ which is widely used in Microsoft Excel with other Logical functions like ‘IF’ as well as other functions.

We had already discussed about this function while discussing IF function, i.e., nested IF with AND function but we will discuss it here in detail.

Sometimes you may face situations where you should take decision based on the output of two or more conditions return TRUE otherwise the decision will be another.

Let’s take an example of an Examination in three subjects, if any student gets the pass marks (50) in all (three) subject in a test then only he will be declared as ‘PASSED’ else he would be declared as ‘FAILED’.

So normally we say that if Harry, a student, gets 50 marks in Subject 1 and 50 marks in Subject 2 and 50 marks in Subject 3 then he has ‘PASSED’ the test.

What we are doing here is we are testing each subject one after another whether the marks obtained is greater than 50 or not, if marks of any subject are less than 50 then the student will be called ‘FAILED’.

Here we can use AND function.

So, let’s discuss about AND function.

The Microsoft Excel AND function returns TRUE if all conditions are TRUE. It returns FALSE if any of the conditions are FALSE.

The AND function is a built-in function in Excel that is categorized as a Logical Function.

Syntax

The syntax for the AND function in Microsoft Excel is very simple

=AND(logical1, logical2, ... )

Parameters or Arguments

logical1

The first condition to test whether it is TRUE or FALSE.

logical2,

Returns

The AND function returns TRUE if all conditions are TRUE.

The AND function returns FALSE if any of the conditions are FALSE.

The below image will make it more clear.

How AND function Works

Example :

It is very easy to use AND function, type =AND( and then write the conditions separated by comma.

Suppose we have two numbers 101 & 111 in two cells C4 & D4, now if we write AND function the formula will be =AND(C4>100,C5>100) which will return TRUE because both the numbers are greater than 100.

If we write any number less than 100, the output will be FALSE.

This was simple,

now we will use this function with IF function, suppose we need to print “OK”, if both the values are greater than 100 and “Not OK” if any of the value is less than 100.

We will do it in simpler way, we will add IF function in the beginning of the above formula

=AND(C4>100,C5>100)

and the formula will be

=IF(AND(C4>100,C5>100),"OK","Not OK").

In same way, you can use AND function in other function (like SEARCH, FIND etc.) to make more complex formula as per requirement.

In the next article, we will discuss about OR function and there will make some more complex formula using AND & OR functions.

I hope you have enjoyed this article. Take care…

Thank you for reading….

TEXTJOIN Function

Today in this article I will discuss about a new excel function TEXTJOIN, an advanced version of CONCAT or CONCATENATE.

As the name indicates it is used to join texts or strings from more than one cell and you can use a separator if require. It works in the same manner the function CONCAT or CONCATENATE works.

One obvious question arises in our mind that if CONCATENATE & CONCAT is available in Microsoft Excel then what is the justification to introduce another function which works same, joining texts ?

There are differences between these three functions although they are used to get the same result.

Let’s discuss the function.

Description

The Microsoft Excel TEXTJOIN function allows you to join 2 or more strings together with each value separated by a delimiter.

The TEXTJOIN function is a built-in function in Excel categorized as a String/Text Function and TEXTJOIN function allows you to join 2 or more strings together with each value separated by a delimiter.

Syntax

The syntax for the TEXTJOIN function in Microsoft Excel is

=TEXTJOIN(delimiter, ignore_empty, text1, text2, ...)

Parameters or Arguments

delimiter

It is your separator, string or a single character inserted between each text value in the resulting string. Most commonly, a comma or space character or a hyphen.

ignore_empty

Determines whether empty values are included in the resulting string.

TRUE ignores empty values and FALSE includes empty values in the result.

This is most interesting and useful parameter in TEXTJOIN function compared to CONCAT or CONCCATENATE function, there was no such scope to avoid blank cells. Our example will clarify the usefulness of this parameter.

text1, text2, ... text_n

The strings that you wish to join. There can be up to 252 strings that are joined together.

Returns

The TEXTJOIN function returns a string/text value.

Note : This function is only available to Excel 2016, not before this version.

Example : 

Suppose we  need to join the characters as shown in the below table. There are two functions CONCAT and TEXTJOIN.

TEXTJOIN Function in Microsoft Excel

If we use CONCAT function the formula will be like :

=CONCAT(A6,",",B6,",",C6,",",D6,",",E6)

whereas if we use TEXTJOIN function the formula becomes :

=TEXTJOIN(",",TRUE,A5,B5,C5,D5,E5)

Very simple, lets analyze these two functions. As you are aware CONCAT is simple, type =CONCAT and an opening bracket then select the CELLs and after one selection of cell you must type your separator (for my case it is “,”). After you complete selecting the range press enter and you are done.

In TEXTJOIN function what you need to do is type =TEXTJOIN and an opening bracket then

1.   First type your SEPERATOR (may be comma or a hyphen)

2.   Put a comma, you will get two options,

a.   TRUE – Ignore empty cells

b.   FALSE – Include empty cells

TEXTJOIN Function in Microsoft Excel

3.   These options are self-explanatory, if you want to ignore the empty cells, it will not be displayed including the SEPERATOR. But if you select the FALSE statement then this cell content will be counted and a separator will be visible which CONCAT function normally does.

4.   Now select the ranges you want to join.

From the above example, we can notice that the using TEXTJOIN is much easier than the usual CONCAT or CONCATENATE function.

Difference between CONCAT or CONCATENATE and TEXTJOIN

1.   you don’t to type the separator repetitively but if you need two different separators then this function will not help, you must use CONCAT function.

2.   Unlike CONCAT function you are provided by an option to exclude or include the blank cells. If there is a blank cell and you use CONCAT function the output gives us TWO SEPERATOR one after another. But if you use TEXTJOIN function this problem will not occur.

3.   The most useful parameter is the RANGE selection. There is no way to select the whole range in a row or column using CONCAT or CONCATENATE function. The beauty of TEXTJOIN is that if the data is available in a row or column you can easily provide the range as we do for other functions like SUM function, the formula then becomes, =TEXTJOIN(",",TRUE,A5:E5).

4.   TEXTJOIN allows to use the ARRAY within this function, I will discuss it latter in another article.

I hope you have enjoyed this article.

Thank you for reading….