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
Bookmarks