I am pleased to introduce you to some of the **New Excel Functions** in **Excel 2016**.

These functions are ‘**wow!!’**.

The functions I will be introducing in this tutorial are **TEXTJOIN, IFS, SWITCH, MAXIFS** and **MINIFS.
[xyz-ihs snippet=”Excel-Curriculum”]**

*Let’s take them one after the other.*

**The TEXTJOIN Function**

As the name implies, **this function is used to concatenate a list or range of text. It also places delimiting character(s) like the comma or space, between each field that it joins together**.

**Recommend**

**250+ Excel Formula & Excel Function List****How to become 4x Faster at Excel****How to remove password from Excel****Learn Advanced Excel Courses for $99**

**It is used to join text strings**.

The **SYNTAX** of **TEXTJOIN** is

** =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)**

The **delimiter** – this is the character you wish to separate the text string with. It could be a space, comma, or any other character.

The **ignore_empty **– If ignore_empty is **TRUE**, then **TEXTJOIN ignores blank cells** in the result of the result of the formula. But if **FALSE**, then **TEXTJOIN includes the blank cells** in the result of the formula.

[xyz-ihs snippet=”Excel-online-Course”]

The picture above shows the use of TEXTJOIN to concatenate four columns of data into one.

** =TEXTJOIN(“,”, TRUE, B15:E15)**

**IFS Function**

The **IFS Function** is like the **IF function**, except that it allows you to check whether one or more conditions are met and returns a value that corresponds to **the first TRUE condition**. The IFS Function can take the place of many nested IF statements, like “IF(IF(IF(IF())))”, though it can be confusing and be challenging at times.

Using the IFS function have some **advantages**, one of which is that you can specify a series of conditions in a single function.

The **SYNTAX** of the **IFS Function
**

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2]…)

=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2]…)

Looking at the example above, I used the conditions for the grade values as:

**=IFS(A2>=80,”A”,A2>=70,”B”,A2>=60,”C”,A2>=50,”D”,A2<50,”F”)**

**Interpretation:**

For a score of 80 and above, the system is to record A.

A score of 70 and above, the system records B, and so on.

But for a score below 50, but not equal to 50, the system records F.

It is important to note that this function (**IFS**) only permits you to test up to **127** different conditions.

[xyz-ihs snippet=”Excel-Curriculum”]

*Up next is the SWITCH function.*

**The SWITCH Function**

The SWITCH Function can also handle many conditions like the IFS Function. The difference between the SWITCH and IFS Functions is that, instead of specifying a series of conditional statements like in IFS, you specify an expression and a series of values and results.

**What does the SWITCH function do?** The SWITCH Function check a single expression against a list of values, then returns the result that corresponds to the first matching value.

**put**: the SWITCH function first looks for the value, matches it and then return the results.

**One merit **of the SWITCH Function over the IFS Function is that you can avoid repeating the expression.

An example of the SWITCH Function is shown in the picture above.

The **SYNTAX** of the **SWITCH Function**

**=SWITCH(expression, value1, result1, [value2, result2], …)**

The conditions used in my example above are:

**=SWITCH(A2,1,”Name”,2,”Age”,3,”Gender”,4,”State”,5,”Country”)**

**Note** the function (**SWITCH**) allows list up to **126 values**.

[xyz-ihs snippet=”Excel-online-Course”]

**MAXIFS and MINIFS Functions**

The **MAXIFS Function** returns the largest** value** among cells specified by a given set of conditions or criteria. While the **MINIFS Function** returns the smallest** value** among cells specified by a given set of conditions or criteria. They are the opposite of each other.

With MAXIFS and MINIFS, you **can specify several conditions**. The conditions help in filtering the data before calculating the largest or smallest values.

**Recommend**

**250+ Excel Formula & Excel Function List****How to become 4x Faster at Excel****How to remove password from Excel****Learn Advanced Excel Courses for $99**

**SYNTAX for the MAXIFS Function**

**=MAXIFS(****max_range****, criteria_range1, criteria1, [criteria_range2, criteria2] …)**

**Where: **

**max_range** – actual range of cells in which the largest value will be determined;

**criteria_range1 – **set of cells to check for the criteria;

**criteria1** – criteria which will be evaluated as largest. It could be in the form of a number, a text or an expression.

Let’s use the **MAXIFS Function** in the data presented in the picture above:

From the above, the **max_range** is **C2:C8**, while the **criteria_range** is **D2:D8.**

In the criteria_range, the 1st, 2nd, 5th and 7th cells match the criterial of “**C**”. From which the one with the largest value was the 5th cell, hence the result is **66**.

**SYNTAX for the MINIFS Function**

**=MINIFS(****min_range****, criteria_range1, criteria1, [criteria_range2, criteria2] …)**

**Where**:

**min_range** is the actual range of cells in which the smallest value will be determined;

**criteria_range1** is the set of cells to check for the criteria;

**criteria1** is the criteria which will be evaluated as smallest. It could be in the form of a number, a text or an expression.

We can use the **MINIFS Function **in the data presented in the picture above:

From the above, the **min_range** is **C2:C8**, while the **criteria_range** is **D2:D8**. In the criteria_range, the 1st, 2nd, 5th and 7th cells match the criterial of “**C**”. Of which the one with the smallest value was the 1st cell, hence the result is **60**.

**The following should be noted for MAXIFS and MINIFS**:

- The size and shape of
**the****max_range****/****min_range****must be the same as that of the****criteria_range**, if not the function return the Error Value. - You can only
**enter data range up to 127**.

*Did I hear you say ‘WOW!!!’*

*In this tutorial, we have introduced five new excel functions in 2016.*

*In subsequent tutorials, we will be picking these functions, one or two at a time to throw more light into them.*

**In the meantime, why don’t you try on these functions. Remember, it sticks better when you practice!**