Welcome to Excel Jet Consult blog post. Recently, Microsoft rolled-out six new functions, one new chart, new theme in Office 2019 and 365. Undoubtedly, the new functions will simplify many difficult operations in Excel
NEW FUNCTIONS
- IFS Function – evaluates whether one or more conditions is satisfied and delivers a value corresponding to the first TRUE condition.
IFS Syntax =IFS(logical_test1, value_if_true1, …)
- MAXIFS Function – returns the maximum value among cells specified by a given set of conditions or criteria
MAXIFS Syntax =MAXIFS(max_range, criteria_rang1, criteria1, …)
- MINIFS Function – returns the minimum value among cells specified by a given set of conditions or criteria
MINIFS Syntax =MINIFS(min_range, criteria_rang1, criteria1, …)
- CONCAT Function succeed the CONCATENATE function. The function joins a list or range of text strings
CONCAT Syntax =CONCAT(text1, …)
- TEXTJOIN Function – concatenates a list or range of text strings using a delimiter
TEXTJOIN Syntax =TEXJOIN(delimiter, ignore_empty, text1, …)
- SWITCH Function – evaluates an expression against a list of values and returns the result corresponding to the first matching value
SWITCH Syntax =SWITCH(expression, value1, result1, [default_or_value2, result2], …)
IFS Function Example
Calculate the Total Sales for each Product using IFS Function
- In cell F4, execute the formula:
=IFS(C4=”Jeep”,D4*E4,C4=”Rolls Royce”,D4*E4,C4=”Volkswagen”,D4*E4,C4=”Honda”,D4*E4)
- Click CTRL + Enter and copy the formula down
Task completed. No nested IF statement
MAXIFS Function Example
In cells I11:13, return the Highest Total Sales for each of the Account Manager in cells H11:H13
In cell I11, execute the formula: =MAXIFS(F4:F21,B4:B21,H11)
Click CTRL + Enter and copy the formula down
Task completed, the Highest Total Sales for David Smith is ₦1,620.00
MINIFS Function Example
In cells J11:13, return the Lowest Total Sales for each of the Account Manager in cells H11:H13
In cell J11, execute the formula: =MINIFS(F4:F21,B4:B21,H11)
Click CTRL + Enter and copy the formula down
Task completed, the Highest Total Sales for David Smith is ₦360.00
CONCAT Function Example
In cells E5 downwards, join combine the First Name, Last Name and Middle Name
In cell E5, execute the formula: =CONCAT(B5:D5)
Click CTRL + Enter and copy the formula down using fill handle
Task completed
TEXTJOIN Function Example
Join the First Name, Last Name and Middle Name using comma delimiter
In the cell E5, execute the formula: =TEXTJOIN(“,”,TRUE,B5:D5)
Click CTRL + Enter and copy the formula down
Task completed
SWITCH Function Example
Calculate the Total Sales for each Product sold by Unit and Price
In cell E3, execute the formula:
=SWITCH(B3,”Toyota”,C3*D3,”Ford”,C3*D3,”Limousine”,C3*D3,”Lexus Jeep”,C3*D3,”Rolls Royce”,C3*D3)
Click CTRL + Enter and copy the formula down
Task Completed
Funnel Chart and Black Office Theme
