Buzz
Functions like TEXTJOIN and CONCAT to combine lists or ranges of text strings, MAXIFS and MINIFS to calculate the maximum and minimum values within a range for one or more conditions, and IFS and SWITCH to minimize confusion when using nested IF functions. These are the 6 new Excel functions that boost efficiency. Follow the article below to learn how to use these new Excel functions effectively.
6 New Excel Functions to Enhance Work Efficiency
Merging text strings with TEXTJOIN and CONCAT functions
Combining text strings is a common task for spreadsheet users. Until now, combining text strings from cell references required specifying individual cells. With this Excel hack using 2 functions TEXTJOIN and CONCAT, string combining becomes much simpler.
The TEXTJOIN and CONCAT functions now allow you to merge text strings from cell references with or without using separators, such as commas to separate each item. Simply reference the range and specify the separator once, and Excel will handle the rest for you.
Traditional solution:
Using CONCATENATE function to join text strings:
New solution utilizing TEXTJOIN function to concatenate text strings:
Using TEXTJOIN function to merge text strings:
Suppose you only want to combine parts of an address into a single text string. Using the traditional solution would require you to specify each cell and repeat a comma to separate each part:
Discover a simpler solution. Just identify the comma (or any separator you prefer), choose to ignore empty or non-existent cells, and then designate the area.
For a deeper understanding of the TEXTJOIN and CONCAT functions, seek additional information and examples online.
The IFS and SWITCH functions help define a series of conditions
The new IFS and SWITCH functions offer users an alternative to using a series of nested IF functions, such as 'IF (IF (IF ())', in cases where multiple conditions need to be checked to find a corresponding result.
The IF function is one of the most commonly used Excel functions, and using nested IF functions within IF functions is also quite common in Excel, but it can confuse users.
The advantage of using the new IFS function is that you can specify a series of conditions within a single function. Each condition is followed by the result that will be used if the condition is True. For example, suppose you want to calculate grades based on a certain score in a test. Using the IFS function, the formula would be:
=IFS(C1>=90, “A”, C1>=80, “B”, C1>= 70, “C”, C1>=60, “D”, C1<60,...
In the formula above, it can be understood that if the score in C1 is greater than or equal to 90, it corresponds to grade A. If the score is greater than or equal to 80, it is grade B. If the score is greater than or equal to 70, it is grade C.
SWITCH function in Excel handles multiple conditions. The key difference with the SWITCH function is that instead of specifying a series of conditional statements, you specify an expression and a series of values and results.
Values are compared against the expression, and when an exact match is found, the corresponding result is applied to the cell. You can also specify a 'default' result to be returned if no exact match is found for the expression. The advantage of the SWITCH function is that you can avoid repetitive expression evaluation, which often occurs when using nested IF functions.
In the example below, the first part of the formula extracts size codes (i.e., XS, M, and G) from the middle of the item in column B. It's quite lengthy, so using the SWITCH function makes sense, requiring the formula to be written only once.
The example can be explained as follows:
Extract size codes from items in column B. If it's 'XS', the result is 'Extra Small'. If it's 'S', the result is 'Small'. If there's no match, the result is 'Not Specified'.
Similar results can be computed using nested IF functions, but the formula would be significantly longer, as shown below:
Utilize MAXIFS and MINIFS functions for data filtering and calculation
If you're familiar with COUNTIFS, SUMIF, and AVERAGE functions, you don't need much explanation about MAXIFS and MINIFS. The basic MAX and MIN functions calculate the largest or smallest value in a range, while AVERAGE supports computing the average, and SUMIF calculates conditional sums. But what if you need to apply conditions to filter your data? That's precisely what MAXIFS and MINIFS functions allow.
You can specify one or more filtering conditions before calculating the maximum or minimum value. Conditions can be applied to adjacent ranges or ranges containing values. For example, suppose a retailer has a table containing sales data for all their stores. They can use MAXIFS and MINIFS functions to calculate the maximum and minimum sales figures for a specified item in stores located in a certain region.
In the example below, the MINIFS and MAXIFS functions are used to calculate the minimum revenue and maximum profit from the table, but only including values from the Sales column if the value in the Retailer column is 'BigMart', the value in the Brand column is 'Longlast', and the value in the Sales column is greater than 0.
Above are 6 new Excel functions that enhance productivity, applicable across various fields. Hence, it's evident that Excel's support capabilities are immensely vast and remarkable.
