Microsoft Excel has come in handy for a lot of folks. Accountants, engineers, business financial managers, mathematicians, actuaries and even civilians who use the program for its proficiency in common calculations. In 1996 the program was used by 30 million people, a number that has possibly doubled or tripled in the last century. The excel team announced 6 new functions deemed to make work easier for excel users by building custom functions that were previously built by users, taking a lot of their time and energy. Here are the 6 new functions that have been added to this formula program for texts and numbers on sheets!
The new CONCAT function is developed from the CONCATENATE function. Sometimes you may need to amalgamate data on a long list of cells with another list. With CONCATENATE, you probably were doing each cell individually, which takes a lot of time. This new CONCAT function allows you to combine two long lists at ones say A1:A30 and B1:B30. Whether you want to combine strings, texts or ranges, this function allows you to combine all into a single string, all you have to do select the range of cells you intend to combine and use CONCAT function. CONCAT(text1,[text2],…) will have a range of rows/columns each with different words that give you a complete sentence from the individual words. You, however, need to specify the spacing in the formula as this function does not come with the delimiter argument.
It is similar to the CONCAT, as the word suggests but tends to even be more powerful. This function allows you to combine multiple ranges or cells into one string, but in this case with a specified delimiter. Unlike the CONCAT function, you can now specify the separator in the function. This delimiter is considered a separator in your text, it could be a space or otherwise and is filled in as quotation marks (“”). Note that selected cells have a 252 limit. In an example: TEXTJOIN(delimiter, ignore_empty, text1, [text2], …). As seen in the formula, empty cells can be ignored in the selected range when TRUE or added when FALSE. Since this is a text function, numbers will also be treated as texts.
MINIFS And MAXIFS Function
Based on specified conditions, these two new functions return the maximum value-MAXIFS and minimum values-MINIFS. Multiple conditions that are fed into the formula make the MAXIFS function detect the maximum value as shown: MAXIFS(max_range, criteria_range3, criteria3, [criteria_range4, criteria4],…)the range is the wider group from which the maximum value is selected. The criteria are the conditions from which these values are being evaluated. The opposite is true for the MINIFS function. They can be used on dates texts and numbers. You should make sure that all ranges, the max, min, and criteria, are of similar sizes lest you get an error message.
This is a solution to users who type long-nested IF formulas, especially those that have no idea what INDEX MATCH is. This function assesses several conditions and yields the value that corresponds to this. As such it is used by: IFS(logical_test1,value_if_true1,[logical_test2, value_if_true2], …). This also has a limit of 127 conditions/logic.
It supplements the IF function since it allows you to assess your expression against different scenarios just like the IF function. The arguments are then compared. In a formula example: SWITCH (expression, value1, result1, [value2, result2], …, [default]). It, however, does not work with logical operators.
With this new knowledge, we hope your calculations have just been made easier and faster! If you’re still looking for more guidance, schedule remote Excel training for you or your team members!