# Excel 2019 New Functions

Microsoft have introduced the following new built-in functions in Excel 2019. Note that these functions have been available in Microsoft's subscription service, Office 365, since 2016.

Text Functions
CONCAT

Joins together two or more text strings.

#### Concat Function Example

A B C
1 C:\ Users\ Jeff

In the above spreadsheet on the right, the text strings in cells A1, B1 and C1 can be joined together, using the Concat function, as follows:

=CONCAT( A1:C1 )

which gives the resulting text string C:\Users\Jeff

TEXTJOIN

Joins together two or more text strings, separated by a delimiter.

#### Textjoin Function Example

A B C
1 C: Users Jeff

In the above spreadsheet on the right, the text strings in cells A1, B1 and C1 can be joined together, separated by the delimiter "\", using the Textjoin function as follows:

=TEXTJOIN( "\", TRUE, A1:C1 )

which gives the resulting text string C:\Users\Jeff

Logical Functions
IFS

Tests a number of supplied conditions and returns a result corresponding to the first condition that evaluates to TRUE.

#### Ifs Function Example

A B C
1 20 0 2

In the above spreadsheet on the right, the ifs function can be used to divide the value in cell A1 by the first non-zero value in cells B1-C1:

=IFS( B1<>0, A1/B1, C1<>0, A1/C1 )

which gives the result 10.

(I.e. the value in cell A1 divided by the value in cell C1.)

SWITCH

Compares a number of supplied values to a supplied test expression and returns a result corresponding to the first value that matches the test expression.

#### Switch Function Example

A B
1 2

In the above spreadsheet on the right, the switch function is used to return a season relating to the number in cell A1.

=SWITCH( A1, 1, "Spring", 2, "Summer", 3, "Autumn", 4, "Winter" )

which gives the result "Summer".

Statistical Functions
MAXIFS

Returns the largest value from a subset of values in a list that are specified according to one or more criteria.

#### Maxifs Function Example

A B C
1 Student Group Score
2 Jane 1 57%
3 Lucy 2 87%
4 James 2 75%
5 Kevin 1 41%
6 Joseph 1 77%
7 Emma 1 69%
8 Alice 2 89%
9 Graham 2 73%

In the above spreadsheet on the right, the maximum exam score recorded in Group 1, can be found by using the Maxifs function as follows:

=MAXIFS( C2:C9, B2:B9, 1 )

which gives the result 77%.

MINIFS

Returns the smallest value from a subset of values in a list that are specified according to one or more criteria.

#### Minifs Function Example

A B C
1 Student Group Score
2 Jane 1 57%
3 Lucy 2 87%
4 James 2 75%
5 Kevin 1 41%
6 Joseph 1 77%
7 Emma 1 69%
8 Alice 2 89%
9 Graham 2 73%

In the above spreadsheet on the right, the minimum exam score recorded in Ggroup 2, can be found by using the Minifs function as follows:

=MINIFS( C2:C9, B2:B9, 2 )

which gives the result 73%.