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.
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.
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).
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.
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.
March 2016
