Excel 365 Array Formulas
Office 365 brings forth a new wave of formulas that you can use in Excel.
I’ve created a spreadsheet which covers a wide range of these new formulas which you can use to see them in action.
The formulas featured on the spreadsheet are as follows with only the ones highlighted being demoed in the webinar linked below.
XLOOKUP
CONCAT
TEXTJOIN
ISFORMULA
FORMULATEXT
MINIFS
MAXIFS
FILTER
SORT
SORTBY
UNIQUE
SEQUENCE
RANDARRAY
TRANSPOSE
FILTERXML
I’ve approached this spreadsheet and webinar from the point of view of someone who has gone from Excel 2010 to Excel 365 so there are some formulas featured that you may have already come across if you were on a more modern version of Excel previously but the array formulas are all brand new to Excel 365 and they are what I cover in the webinar linked below.
Attention: the Excel 365 Array Formulas section of the webinar is only from 00:00:00 to 00:53:30 with the remaining part of the webinar to do with how those formulas came in handy when developing a method of showing how costing systems do Overhead Allocation.
https://www.youtube.com/watch?v=lp-jRmwfIvw
Notes to add since completing this:
I don’t think I mention this in the webinar or put much emphasis on it in the spreadsheet but you can use the FILTER function a bit like you would use a VLOOKUP if you wanted to add multiple lookup criteria. Have a think about how you’d go about doing that using some of the examples provided and you may find yourself dropping lookups for good as that feature is extremely powerful.
The get any string of text formula for use in conjunction with ESR Payroll that will work in Excel 2013 onwards is here (replace | with a ~ or whatever your finance system displays the delimited characters as):
=FILTERXML(“<a><b>”&SUBSTITUTE(A50,”|”,”</b><b>”)&”</b></a>”,”//b[“&B74&”]”)
where A50 is your text, something like XN0501|MONTH01|265738271|17988282|28500|0.95|1.00|Staff Nurse
and B74 is the block of text you want to get out so if you want to get out the position number from above then this would be 4. If it were the job title it’d be 8.