What's New and Improved in Office 2016 for Office 365

[AlKey]

For those who maybe interested, the recent update to Microsoft Office 2016/Office 365 has delivered a few new and very useful function for Excel.

MAXIFS and MINIFS -- these two functions use the same syntax as other IFS functions

CONCAT function that replaces CONCATENATE can take ranges but has no delimiter.

TEXTJOINT a totally new concatenate function that takes ranges, uses delimiter and takes an argument of TRUE/FALSE (1,0) for empty cell.

SWITCH somewhat similar to CHOOSE.

and IFS function

MAXIFS function

=MAXIFS(K2:K5,K2:K5,"<400")

v	K	L	M
1
2	414		78
3	34
4	78
5	35

MINIFS function

=MINIFS(K2:K5,K2:K5,"<400")

v	K	L	M
1
2	414		34
3	34
4	78
5	35

CONCAT function

Although as I mentioned earlier CONCAT function doesn't have a delimiter option, I was able to use it as an array formula

=SUBSTITUTE(TRIM(CONCAT(IF(A2:A8={2,4,7}," "&B2:B8,"")))," ",", ")

v	A	B
1
2	1	Sunday
3	2	Monday
4	3	Tuesday
5	4	Wednesday
6	5	Thursday
7	6	Friday
8	7	Saturday
9
10		Monday, Wednesday, Saturday

TEXTJOIN function

=TEXTJOIN(", ",1,B2:B8)

v	A	B
1
2	1	Sunday
3	2	Monday
4	3	Tuesday
5	4	Wednesday
6	5	Thursday
7	6	Friday
8	7	Saturday
9
10		Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday

SWITCH function

=SWITCH(D2,1,SUM(G$2:G$5),2,SUM(I$2:I$5),3,SUM(K$2:K$5))

v	D	E	F	G	H	I	J	K	L
1		Sum ranges		range 1		range 2		range 3
2	1	147		25		65		414
3	2	270		32		45		34
4	3	561		45		145		78
5				45		15		35

IFS function

=IFS(A1<5,1,A1<10,2,A1<15,3,TRUE,4)

v	A	B
1	1	1
2	5	2
3	10	3
4	3	1
5	2	1
6	4	1
7	6	2
8	7	2
9	12	3
10	15	4

For more details:

https://support.office.com/en-us/art...f-bca4603e1426

[AlKey]

To update your Office 365/Office 2016:

Got to File, Select Account, Click on Update Options, Update Now. Your update will be completed in few minutes.

[:) Sixthsense :)]

Sounds good and thanks for sharing the info

I love to here the Concat() function arrival but just wondering why no Split() function?

[FDibbins]

As already posted (I am using 2016 Pro Plus)...

hmm my updates are set to automatic, and when I tried to manually update, it saw i was already up to date

[AlKey]

Do you also have a Office365 subscription?

[JapanDave]

http://www.excelforum.com/the-water-...016-sucks.html