Certainly, every computer user is familiar with the Microsoft Office suite, which is the most popular suite for managing office tasks. Although it is a non-free software package, everyone prefers to use it despite the availability of free alternative programs that perform the same functions. Starting from 2007, Microsoft has been releasing a new version of the Office suite every three years, including important updates to the suite’s programs, whether security updates or enhancements to the performance of the suite, as well as updates to the user interface of the various suite programs.
By the end of 2018, Microsoft released a new version of the Office suite for the year 2019. This package came with many updates to all suite programs, in addition to specific updates for each program individually. The popular Microsoft Excel program, designed for financial business management, received numerous security updates and added features related to maps and user interface enhancements. But the most important part was the addition of a new set of functions or equations, which we will review today in this article. It is worth mentioning that as of writing this article, these functions are only available in the paid version Office 365.
Read also: Operations and Equations in Excel You Need to Know
IFS
Every Excel user is familiar with the conditional IF function, which, in short, executes a specific command if a certain condition is met. However, if you have more than one possibility, and for each possibility there is a specific command to be executed, you will need to create a nested conditional equation or Nested IF. Besides the difficulty of creating this equation due to the many conditions, this can lead to many errors in writing and executing the function itself. In the 2019 version, the IFS function was added, which simply allows the user to specify each condition alongside the command to be executed if the condition is met sequentially. When executing the function, the computer will execute the command related to the first condition that is met without the need to create a nested function and write multiple parentheses.
=IFS(A1=1, “Summer”, A1=2, “spring”, A1=3, “Winter”, A1=4, “Autumn”)
In this case, the IFS function will examine cell A1. If the value is 1, the corresponding value will be Summer, and if the value is 2, the corresponding value will be spring, and so on.
Switch
A new addition in conditional equations, in this case, the function allows the user to specify the cell to be examined for possibilities, and then sequentially enter the possibilities and the command to be executed with each possibility.
=SWITCH( A1, 1, “Summer”, 2, “spring”, 3, “Winter”, 4, “Autumn” )
In this case, the SWITCH function will examine cell A1. If the value is 1, the corresponding value will be Summer, and if the value is 2, the corresponding value will be spring, and so on.
CONCAT
If you use text functions, you are probably familiar with the Concatenate function, which simply aggregates multiple text values to form a single text. In the 2019 version, a new function named CONCAT was added, which adds text values from multiple connected cells or ranges. For further clarification, let’s review the following examples.
=CONCATENATE(B5,C5,D5,E5)
=CONCAT(B5:D5)
The first function adds the text contents in cells B5, C5, D5, E5, but using CONCAT, those cells were condensed into a range from B5 to D5.
TEXTJOIN
Another new function for text operations, this function combines multiple text values to form a single text, just like CONCAT, with the added feature of being able to add a fixed value as a delimiter between these texts.
=TEXTJOIN( “*”, TRUE, A2:D2 )
This function will add all the text values in cells A2, B2, C2, D2, adding an asterisk between each text and adding the word True, meaning to ignore any cells without values.
MAXIFS and MINIFS
Two very similar functions, each of which determines the maximum value (MAXIFS) or the minimum value (MINIFS) within a specific range of cells that match a certain condition.
=MINIFS( C2:C10, B2:B10, “Accountant” )
In the above function, the lowest salary between cells C2 to C10 among all rows containing the job title Accountant in cells B2 to B10 only will be determined, and the result will be 3200.
=MAXIFS( C2:C10, B2:B10, “Accountant” )
Similarly, the MAXIFS equation works, where the highest salary between all rows containing the job title Accountant in cells B2 to B10 will be determined, and the result will be 4000.
