Menu
Insights Training
  • Home
  • Chat with Us
  • Webinars
  • Public Schedules
  • Course List
  • Clients / Class Pictures
    • 2017 Class Pics / Clients
    • 2018 Class Pics / Clients
    • 2019 Class Pics/ Clients
    • 2020 Class Pictures
    • 2021 Class Pics
    • 2023 Class Pics
  • Testimonials
  • Convert Courses to Online
  • Request for Proposal
  • Insights Online
  • Take an Assessment
  • Blog
  • Contact Us
  • OTHER SERVICES
  • Privacy Policy
  • Code Verifier
  • Blog
  • Refer and Earn
  • Home
  • Chat with Us
  • Webinars
  • Public Schedules
  • Course List
  • Clients / Class Pictures
    • 2017 Class Pics / Clients
    • 2018 Class Pics / Clients
    • 2019 Class Pics/ Clients
    • 2020 Class Pictures
    • 2021 Class Pics
    • 2023 Class Pics
  • Testimonials
  • Convert Courses to Online
  • Request for Proposal
  • Insights Online
  • Take an Assessment
  • Blog
  • Contact Us
  • OTHER SERVICES
  • Privacy Policy
  • Code Verifier
  • Blog
  • Refer and Earn

Blog

New Excel 2016 Functions

9/22/2017

Comments

 
1.  TEXTJOIN
This function is an evolution of the old CONCATENATE function.

TEXTJOIN allows concatenation of an entire range with a specified delimiter.

Picture
To combine the column data into a full address, the formula would be:

=TEXTJOIN(", ",1,A2:D2)
​
•The first argument is the delimiter.  This is what will separate the values.
•The second argument is either TRUE or FALSE.  Indicating this as TRUE or 1 means it will ignore empty cells.  As FALSE, it would consider empty cells and still show them in the result.
​

2.  CONCAT
​CONCAT is obviously another evolution of CONCATENATE. 

Using the previous example, if CONCAT is used, it will result to:
Picture
CONCAT only accepts one kind of argument, which is the range.  It will combine the texts, that’s all.  No delimiter can be set, nor ability to ignore blanks.  Therefore, it is a more basic kind of TEXTJOIN.

=CONCAT(A2:D2)

Similar to TEXTJOIN, the #VALUE error will show up if the result is more than 32767 characters.

3.  IFS
The IFS function has been introduced to handle a situation where you will string several IF statements in one.
Picture
=IFS (B2<=80,"Fail"  ,  B2<=90,"Good"   ,   B2>90,"Pass"  )

Note that unlike in the nested IF, the IFS function does not allow a “catch rest” or the last argument in a multiple IFs formula.  All possible outcomes must have their corresponding logical test.
​

4.  SWITCH
​
The SWITCH is similar to the old CHOOSE function, but is not limited in accepting numbers in order.  It is used to simply conditional statements.
Picture
=SWITCH( A2 , "Japan","Asia" , "France","Europe" , "Philippines","Asia" )

The formula starts with cell to be evaluated.  Then succeeding values and their corresponding outcomes follow.

In the example, if A2 is “Japan,” then the result will be “Asia.” 

If A2 is “France,” the result is “Europe.”
Comments
    View my profile on LinkedIn

    Archives

    April 2020
    May 2019
    September 2017

    Categories

    All
    Excel Functions
    Word Features

    RSS Feed

Home

Courses

Public Classes

Contact

Copyright © 2022