|

Excel: Six New Functions

By Bill Jelen
March 1, 2016
12 comments
Excel_web_image

The late January 2016 Excel update for Office 365 subscribers included six new calculation functions and one new chart type. The six functions are CONCAT, TEXTJOIN, IFS, SWITCH, MAXIFS, and MINIFS. The new chart type is a funnel chart. At least two of these calculation functions are vast improvements over existing functionality.

 

GETTING UPDATES

 

One of the more confusing aspects of Office 365 is that not everyone gets the update on the same day. Subscribers to the Home, Personal, or University get the updates first. This way, if there is a bug, it will only happen to the less mission—critical spreadsheets that we keep at home.

 

Once enough Home users find the feature and don’t have problems, then Microsoft pushes the new bits out to Office 365 Business subscribers. If you absolutely want to get the new bits first, join either the Office Insider program (Home, Personal, Mac, or University) or the First Release program if you are a business subscriber.

 

CONCATENATING MULTIPLE CELLS

 

Excel previously offered a CONCATENATE function. If you wanted to join 10 cells together, you had to individually name each cell: =CONCATENATE(A1,A2,A3,A4,…,A10). The new CONCAT function can accept a range of cells: =CONCAT(A1:A10).

 

As you can see in cell C2 of Figure 1, the values are smashed together without any separators. The alternative is another new function TEXTJOIN. The syntax for the function is =TEXTJOIN(delimiter, ignore_empty, text1…). You can specify that you want to separate each value with either a space or a comma and a space. You can also specify if you want to exclude empty cells that appear in the range. As you can see in cell C5 of Figure 1, the result is far more aesthetically pleasing.

 

Figure 1
Figure 1

 

AVOIDING NESTED IF FUNCTIONS

 

You have likely encountered situations in Excel where a formula performs a series of logical tests. If the score is above 92, then give an A. If the score is above 85, then a B. If the score is above 70, then a C, and so on. While these can be achieved with a VLOOKUP, the approach is often to nest a series of IF functions: =IF(A2>92,“A”,IF(A2>=85,“B”,IF(A2>70,“C”,IF(A2>65,“D”,“F”)))). When you finish this series of IF functions, you need to make sure to close all of the parentheses.

 

The latest release of Excel includes the plural IFS function. After the first logical test and answer if true, you simply launch into the next logical test: =IFS(A2>92,“A”,A2>85,“B”,A2>70,“C”,A2>“65”,D). The slightly difficult concept is how to finish the IFS function if none of these are true. You could try to write a logical test to handle the “all other” condition. For example, you could use A2<=65,“F” to finish the IFS function. But, it is also possible to simply use True as the logical test to finish the function. That will force the next argument to be used in all other cases. The formula used in column B of Figure 2 makes use of True as the next-to-last argument.

 

Figure 2
Figure 2

 

In this example, the IFS function is still redundant, as all of the logical tests are checking on the value of A2. The new SWITCH function handles the situation where you are comparing several different values to the same expression. The formula in column C of Figure 2 assigns a status based on the letter grade in B2. In the formula, the first argument of B2 is the value by which all future tests will be compared. If the grade is F, then the student is failing. If the grade is D, then the student is on probation. All other grades are assigned a status of Passing.

 

MAX OR MIN WITH MULTIPLE CONDITIONS

 

Excel 2010 introduced the SUMIFS, COUNTIFS, and AVERAGEIFS functions, which allow you to specify up to 127 pairs of criteria. Any records that match all of the criteria are totaled, counted, or averaged. The latest update of Office 365 adds new functions to find the largest or smallest values using MAXIFS or MINIFS (see Figure 3).

 

Figure 3
Figure 3

 

OTHER CHANGES

 

The latest release includes support for Funnel charts (found in the Waterfall and Stock chart menu on the Insert tab). And all of the Office applications now have a new theme where the ribbon is black (see Figure 4). This is supposedly great for speakers who are working in a dark backstage environment. Unfortunately, I don’t know many accountants who use spreadsheets in the dark.

 

Figure 4
Figure 4

 

The caveat when using these new functions: Make sure that any coworkers who open the workbook have the same release of Office 365 as you do, otherwise a #NAME error will result.

 

SF SAYS

 

By offering valuable functions only to those who subscribe to Office 365, Microsoft hopes to drive more customers to the subscription model.

 

Bill Jelen is the author of MrExcel XL: 40 Greatest Excel Tips. Send questions for future articles to IMA@MrExcel.com.
12 + Show Comments

12 comments
    Safdar imam March 4, 2016 AT 9:51 pm

    Thanks for letting us excel in excel.

    mahmoud March 5, 2016 AT 5:55 am

    good job

    Muhammad haris dilshad March 5, 2016 AT 7:27 am

    I need the excel sheet please

    Ghassan March 6, 2016 AT 11:28 pm

    so nice

    Hafiz Muhammad Sajjeel March 7, 2016 AT 12:00 am

    good

    DAN March 13, 2016 AT 10:46 pm

    READ

    Chris Mishler March 23, 2016 AT 3:19 pm

    Hopefully we can eschew the black background on the ribbon. Reverse video is generally frowned upon in designing with human factors in mind. it takes longer to “compute” reverse video (white on black) in the human brain.

    Bill jelen March 25, 2016 AT 4:20 pm

    +Chris Mishler: THe black ribbon is also new in February 2016, but the other white, grey, colorful are still available. I joke that the black riBbon must be for people who excel in the dark.

    amr saudi March 26, 2016 AT 10:16 am

    Very informative. Thanks

    Bernard Vukas March 26, 2016 AT 12:20 pm

    Hi Bill,

    Awesome post, Thanks for the update! I wanted to let you know about a new Excel add-in that would enable all the newest Excel formulas in all older versions of Excel; including versions that are not under an active Office 365 subscription:
    http://bvukas.com/announcing-excel-formula-pack-add-in/

    I hope you find it useful. I look forward to hearing your comments.

    Thanks,
    Bernard

    Cynthia Somers March 31, 2016 AT 2:06 pm

    I love learning about the “excel”lent new features of excel!

    Joseph Kattupalath April 30, 2016 AT 7:07 am

    Looking for new formulas and functions in excell

You may also like