Phil H wrote...
Is there any chance that you could explain in this in something
approaching laymans english (i.e. that I might understand).
....

Not really because the issues are inherently complex.

MMULT, MINVERSE, TRANSPOSE, ROW, COLUMN, N, T, and INDEX functions can
return single-item arrays, e.g., type the formula =ROW(A1) and press
[F9] rather than [Enter], and Excel will display ={1} rather than 1 in
the formula bar. The good news for MINVERSE, TRANSPOSE, N, T and INDEX
is that they only return single-item arrays when passed single-item
arrays. MMULT, ROW and COLUMN, OTOH, can return single-item arrays
when you wouldn't necessarily expect them to do so.

Problems arise when you use dynamic ranges, i.e., references to ranges
produced by OFFSET and INDIRECT functions, where any of the 2nd
through 5th arguments to OFFSET or the one and only argument to
INDIRECT are arrays. In those situations, OFFSET and INDIRECT return
undocumented 'objects' that can only be described as arrays of range
references. The only functions that can use those beasts are SUMIF,
COUNTIF and presumably the Excel 2007/2008 extensions AVERAGEIF,
SUMIFS, COUNTIFS and AVERAGEIFS. All other functions return error
values when passed these 'objects'.