Excel 2016 just got better for Office 365 subscribers, thanks to the addition of several new functions. Here's a look at how to put them to work.
In February, Microsoft updated Office 365—and there's a lot to like. Most of it will please mobile device users, and everybody will appreciate Excel's six new functions. According to the Excel Team blog, these functions "... simplify some of your common calculations and help you avoid the tedious work of building custom functions to accomplish these tasks."
Note that the new functions aren't available for the standalone version. You can download and view the results of these functions, but the results are only for show. If you modify a dependent value while viewing in the standalone version of Excel, the new functions will return an error.
In this article, you'll learn how to use Excel's new functions to create more efficient expressions. For your convenience, you can download the .xlsx example file for use with your 365 account.
The TEXTJOIN() function combines text from multiple ranges with the added flexibility of a specified delimiter. This function uses the following syntax:
TEXTJOIN(delimiter, ignoreempty, text1, [text2], ...)
where delimiter is a text string or a reference to a text string that represents the character(s) you want to insert between each of the strings you're combining. In addition, ignoreempty is TRUE or FALSE with TRUE (the default) ignoring empty cells. The two text arguments are literal string values or references to ranges that contain the text you want to concatenate.
The biggest advantage is the ability to set the delimiter once. Figure A illustrates this point. Columns F, G, and H contain the following formulas, respectively:
=C2& " for " & D2
=CONCATENATE(C2, " for ", D2)
=TEXTJOIN(" for ",,C2, D2)
TEXTJOIN() eliminates the need to manually enter multiple delimiter characters.
In such a simple example, none of the functions appears to be superior because you specify the delimiter only once. But when combining several strings, TEXTJOIN() is more efficient. It also makes it easy to create a string array from a single field—functionality that's been a long time coming. Figure B shows the following function combining all the text values in the Personnel column:
Use this function to create a string array.
Use CONCAT() to combine multiple strings or ranges similar to TEXTJOIN(), but without the flexibility of a delimiter or empty argument. This function replaces CONCATENATE() (see #1), which remains available for backward compatibility. CONCAT() uses the following syntax:
CONCAT(text1, [text2], ...)
where both arguments are a string or an array of strings, such as a range. You can add a delimiter as an argument, but a better choice would be to use TEXTJOIN(). I'm not sure why Microsoft bothered with this one; I'm including it to be comprehensive and as a gentle warning to start using it instead of CONCATENATE().
The IFS() function checks one or more conditions and returns a value that corresponds to the first TRUE condition—that only sounds confusing. This function uses the following syntax:
IFS(condition1, truevalue1, [condition2, truevalue2], ...)
The conditionx expression must evaluate to TRUE or FALSE and truevaluex is returned when conditionx is TRUE. If you enter a conditionx value, you must enter a truevaluex value. This format is straightforward and easy to interpret and update.
This function can eliminate nested IF() functions, which are handy and effective, but messy and difficult to maintain. The IFS() function shown in Figure C is much easier to write and update than its nested IF() counterpart.
Use IFS() to replace nested IF() functions.
This function returns a bonus rate based on the Commission value in column E. As you can see, I've used it twice because this function stops checking when conditionx is met. That means if there's any ambiguity in your conditions, the order in which you list them will matter. The BonusWrong function:
doesn't always return the desired results. For example, the value in E2, $186, returns a bonus rate of .03. However, you might expect the bonus rate to be .02 because the value is less than 200. In this example, only two conditional sets will be met because all values will be less than 300 or greater than 300 (the last argument set). The function will never execute the conditional sets for less than 100 and less than 200.
The BonusRight function eliminates this problem because the order eliminates the ambiguity:
SWITCH() is similar to IFS(), but with a subtle difference: Instead of specifying conditional expressions, you specify an expression and a series of values and results. You're looking for an exact match; when the first exact match is found, its corresponding result is returned. The function also accommodates a default to return when no match is found.
The syntax is simple:
SWITCH(expression, value1, result1, [default or value2, result2] ...)
where expression is the value being compared to valuex and resultx is the value returned when the first two match.
Figure D shows the following SWITCH() function returning a shortened code for each region:
The options are straightforward and it's easy to see that there's no Northwest region. This is a good error clue for you. On close examination, you can see that I used Southwest; SW twice; simply change one to Northwest; NW to correct the expression. This type of error is easy to make. For this reason, I recommend always including a default argument, even if you think one isn't necessary.
SWITCH() quickly returns a text code for each region.
If you're familiar with COUNTIFS(), SUMIFS(), and AVERAGEIFS(), you'll appreciate MAXIFS() and MINIFS(). MAXIFS() lets you determine which values are evaluated by a traditional MAX() calculation.
This function uses the following syntax:
MAXIFS(range, criteriarange1, criteria1, [criteriarange2, criteria2], ...)
where range is the range of values you're evaluating, criteriarangex represents the range criteria that values in range must meet to be evaluated, and criteriax is the actual criteria value. Figure E shows a matrix that returns the maximum value from column B for each person by region using MAXIFS().
This matrix uses MAXIFS() to return conditional maximum values.
In this case, precise absolute and relational referencing is necessary:
When the value in column C equals the value in column H and the region value in column D equals the value in row 1, the value in column B is considered for evaluation.
As you might expect, MINIFS() is similar to MAXIFS(). The functionality and syntax are the same, but MINIFS() returns the minimum value. Figure F shows a second matrix using the following function to return the minimum value from column B for each person by region:
Use MINIFS() to return a conditional minimum value from a range.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at firstname.lastname@example.org.
- How to add Office macros to the QAT toolbar for quick access
- How to print one or more labels for a single Access record
- Excel tips: How to select cells and ranges efficiently using VBA
- Office Q&A: A dynamic lookup solution for matching partial lists to a master list in Excel