Hi,
In February, Microsoft office has introduced Office 365. In this version, Microsoft introduced Six new functions which are more useful in the calculation and can save your time.
In this article, you’ll learn how to use Excel’s new function to create more efficient expressions.
Six new formulas are listed below-
1- TextJoin
2- IFS
3- MaxIFs
4- MinIfs
5- Concat
6- Switch
Let’s began the journey, we will understand it one by one-
1-TextJoin-
In above example, we want to type a remark and add delimiters “to” in between the travel destination. To combine these number with delimiters “to” we have been using Formula Concatenate. As you can see in above image, however, we must have mentioned “to” every single time before the text strings.
Old Idea to apply delimiters “to “every time, presenting a new formula is called Text Join which helps you to overcome this habit- see the image given below-
How textJoin will works-
Delimiter= This option helps us to separate between each text E.g. “To”
Ignore Empty= Whether to ignore empty cells or not
Text1- First text or you can give a range as well
Text2- Second text or you can give a range as well (Optional)
2- IFS
See the above example, if we want to calculate discount on multiple criteria basis, How will you do that, Yes, correct answer, we will use Nested IF function, However, It’s so lengthy and boring to apply multiple time If(if(if…..
Now Microsoft office brings a New Logical function i.e. “IFS”, see the example below-
How will we use IFS function in excel?
=IFS (test1, value1, [test2, value2], …)
Test1- First Logical test1
Value1- Result value 1 if true
Test2 – Second logical test2
Value2- Second result value2 if True
Arguments IFS, Each test/Value return value true or False and every value is connected with the previous value. You can give 127 conditions one time in IFS function 😊
3- MaxIFS-
If we have a data and we want to see the maximum value then we use “=Max(Number1..)”, correct however if we want to see the maximum sales amount of one person then?
Microsoft office 365 brings you a new function which can show you the maximum value on criteria /Condition basis and that formula is “MaxIFS”.
See the example below- if we want to see the maximum sales according to the salesperson-
How MaxiFs works-
=MAXIFS (max_range, range1, criteria1, [range2], [criteria2], …)
Max_Range- Range of values used to determine maximum
Range1- the first range to evaluate
Criteria1- The first criteria to use on rang1
Range2- the second range to evaluate[optional]
Crtieria2- The second criteria to use on range2[optional]
4- MiniIFs–
Similarly, if we want to see the minimum value on criteria basis, we can use MinIFs-
How we will use MinIFs-
=MinIFs(Min_Range, Range1,criteria1,[Range2],……)
Min_Range- Range of values used to determine the minimum
Range1- the first range to evaluate
Criteria1- The first criteria to use on rang1
Range2- the second range to evaluate[optional]
Crtieria2- The second criteria to use on range2[optional]
5- Concat-
The Microsoft office 365 brings you a new function “Concat” which allow joining 2 or more strings. It was released in Microsoft office in 2016 and replaced the function “Concatenate” However the “Concatenate” function will still available for compatibility with earlier version of Excel.
How Concat works?
=CONCAT(text1, [ text2, … text_n ] )
Text1- Select the first value or range
Text2- select the second value or range
6-Switch Function
The switch function compares a value against the list of values and returns the result according to the first result match. if no match found, it’s possible to return default value which is optional.
The switch function as well as if function both specify a series of the condition, however with IF function we define the expression and it’s value, again and again, see the example-
And in switch function, there is no need to define expression every time, as you can see in the example-
You can download the practice file by clicking on this link-
Stay in touch and Happy Learning 😊