{"id":8143,"date":"2018-06-25T12:17:28","date_gmt":"2018-06-25T12:17:28","guid":{"rendered":"http:\/\/www.nurturetechacademy.in\/?p=8143"},"modified":"2018-06-25T12:17:28","modified_gmt":"2018-06-25T12:17:28","slug":"microsoft-updates-six-new-excel-functions-office-365","status":"publish","type":"post","link":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/microsoft-updates-six-new-excel-functions-office-365\/","title":{"rendered":"Microsoft Updates six new Excel functions in Office 365"},"content":{"rendered":"

Hi,<\/p>\n

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.<\/p>\n

In this article, you\u2019ll learn how to use Excel\u2019s new function to create more efficient expressions.<\/p>\n

Six new formulas are listed below-<\/p>\n

1-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0TextJoin<\/p>\n

2-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0IFS<\/p>\n

3-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0MaxIFs<\/p>\n

4-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0MinIfs<\/p>\n

5-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Concat<\/p>\n

6-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Switch<\/p>\n

Let\u2019s began the journey, we will understand it one by one-<\/p>\n

1-TextJoin-<\/strong><\/p>\n

\"Pic-1\"<\/p>\n

In above example, we want to type a remark and add delimiters \u201cto\u201d in between the travel destination. To combine these number with delimiters \u201cto\u201d we have been using Formula\u00a0Concatenate<\/strong>. As you can see in above image, however, we must have mentioned \u201cto\u201d every single time before the text strings.<\/p>\n

Old Idea to apply delimiters \u201cto \u201cevery time, presenting a new formula is called Text Join which helps you to overcome this habit- see the image given below-<\/p>\n

\"PIc-2\"<\/p>\n

How textJoin will works-<\/p>\n

\"PIC-9\"<\/p>\n

Delimiter= This option helps us to separate between each text E.g. \u201cTo\u201d<\/p>\n

Ignore Empty= Whether to ignore empty cells or not<\/p>\n

Text1- First text or you can give a range as well<\/p>\n

Text2- Second text or you can give a range as well (Optional)<\/p>\n

2- IFS<\/strong><\/p>\n

\"Pic-3\"<\/p>\n

See the above example, if we want to calculate discount on multiple criteria basis, How will you do that, Yes,\u00a0correct\u00a0answer,\u00a0 we will use Nested IF function, However, It\u2019s so lengthy and boring to apply multiple time If(if(if\u2026..<\/p>\n

Now Microsoft office brings a New Logical function i.e. \u201cIFS\u201d, see the example below-<\/p>\n

\"Pic-4\"<\/p>\n

How will we use IFS function in excel?<\/strong><\/p>\n

=IFS (test1, value1, [test2, value2], …)<\/p>\n

Test1- First Logical test1<\/p>\n

Value1- Result value 1 if true<\/p>\n

Test2 \u2013 Second logical test2<\/p>\n

Value2- Second result value2 if True<\/p>\n

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\u00a0\ud83d\ude0a<\/p>\n

3- MaxIFS-<\/strong><\/p>\n

If we have a data and we want to see the maximum value then we use \u201c=Max(Number1..)\u201d, correct however if we want to see the maximum sales amount of one person then?<\/p>\n

Microsoft office 365 brings you a new function which can show you the maximum value on criteria \/Condition basis and that formula is \u201cMaxIFS\u201d.<\/p>\n

See the example below- if we want to see the maximum sales according to the salesperson-<\/p>\n

\"Pic-5\"<\/p>\n

How MaxiFs works-<\/strong><\/p>\n

=MAXIFS (max_range, range1, criteria1, [range2], [criteria2], …)<\/p>\n

Max_Range- Range of values used to determine maximum<\/p>\n

Range1- the first range to evaluate<\/p>\n

Criteria1- The first criteria to use on rang1<\/p>\n

Range2- the second range to evaluate[optional]<\/p>\n

Crtieria2- The second criteria to use on range2[optional]<\/p>\n

 <\/p>\n

4- <\/strong>MiniIFs–<\/strong><\/p>\n

Similarly, if we want to see the minimum value on criteria basis, we can use MinIFs-<\/p>\n

How we will use MinIFs-<\/p>\n

=MinIFs(Min_Range, Range1,criteria1,[Range2],\u2026\u2026)<\/p>\n

Min_Range- Range of values used to determine the minimum<\/p>\n

Range1- the first range to evaluate<\/p>\n

Criteria1- The first criteria to use on rang1<\/p>\n

Range2- the second range to evaluate[optional]<\/p>\n

Crtieria2- The second criteria to use on range2[optional]<\/p>\n

\"PIc-6\"<\/p>\n

5- Concat-<\/strong><\/p>\n

The Microsoft office 365 brings you a new function \u201cConcat\u201d which allow joining 2 or more strings. It was released in Microsoft office in 2016 and replaced the function \u201cConcatenate\u201d\u00a0 However the \u201cConcatenate\u201d function will still available for compatibility with earlier version of Excel.<\/p>\n

\"Pic-8\"<\/p>\n

How Concat works?<\/strong><\/p>\n

=CONCAT(text1, [ text2, … text_n ] )<\/p>\n

Text1- Select the first value or range<\/p>\n

Text2- select the second value or range<\/p>\n

6-Switch Function<\/strong><\/p>\n

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\u2019s possible to return default value which is optional.<\/p>\n

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\u2019s value, again and again, see the example-<\/p>\n

\"Pic-10\"<\/p>\n

 <\/p>\n

 <\/p>\n

And in switch function, there is no need to define expression every time, as you can see in the example-<\/p>\n

\"Pic-7\"<\/p>\n

You can download the practice file by clicking on this link-<\/p>\n

Stay in touch and Happy Learning\u00a0\ud83d\ude0a<\/p>\n

 <\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"excerpt":{"rendered":"

Hi,
\nIn 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.
\nIn this article, you\u2019ll learn how to use Excel\u2019s new function <\/p>\n

<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n","protected":false},"author":1,"featured_media":8155,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"footnotes":""},"categories":[56],"tags":[265,266,263,243,264,257,260,259,249,256,248,244,250,252,245,253,254,246,251,267,255,262,258,247,261],"class_list":["post-8143","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel","tag-corporate-training-companies","tag-corporate-training-companies-in-delhi","tag-corporate-training-courses","tag-corporate-training-in-delhi","tag-corporate-training-programs","tag-excel-course-online","tag-excel-courses-online","tag-excel-online-course","tag-excel-online-training","tag-excel-training-in-delhi","tag-excel-training-online","tag-learn-microsoft-excel","tag-learn-ms-excel-online","tag-learning-excel-online","tag-learning-microsoft-excel","tag-microsoft-excel-course","tag-microsoft-excel-online-training","tag-microsoft-excel-training","tag-ms-excel-learning","tag-ms-excel-online-training","tag-ms-excel-training-in-delhi","tag-online-corporate-training","tag-online-excel-course","tag-online-excel-training","tag-online-excel-training-courses"],"_links":{"self":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8143","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/comments?post=8143"}],"version-history":[{"count":1,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8143\/revisions"}],"predecessor-version":[{"id":8156,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/posts\/8143\/revisions\/8156"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media\/8155"}],"wp:attachment":[{"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/media?parent=8143"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/categories?post=8143"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.nurturetechacademy.in\/dev.nurturetechacademy.in\/wp-json\/wp\/v2\/tags?post=8143"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}