Discussion: creative ways to avoid CSE in Excel when arrays are needed

[bird333]

Title pretty much says it all. Just wondering about different ways to use certain functions to avoid having to enter array formulas (ctrl+shift+enter). I know 'sumproduct' is used to do so sometimes. Thanks!

[BadlySpelledBuoy]

SUMPRODUCT can indeed be used to avoid array formulas in some instances. In later versions of Excel there is lots of using AGGREGATE to avoid arrays in s ome instances too.

How to avoid arrays is very much dependent on what exactly you're trying to accomplish...

BSB

[FDibbins]

Another way might be to use helper columns to do some of the calcs, then base other calcs off the helpers

[Pepe Le Mokko]

OTOH SUMPRODUCT is an array formula but does not need the CSE...
What are you trying to avoid? The CSE operation or the use of array formulas ( which are said to slow down sheets which is not always the case) ?
Our friend XOR has some interesting views on the subject https://excelxor.com/2014/09/01/inde...mulas/#more-13

[Glenn Kennedy]

Or upgrade to O365, where array entry is no longer required.

[bird333]

OTOH SUMPRODUCT is an array formula but does not need the CSE...
What are you trying to avoid? The CSE operation or the use of array formulas ( which are said to slow down sheets which is not always the case) ?
Our friend XOR has some interesting views on the subject https://excelxor.com/2014/09/01/inde...mulas/#more-13

Point taken. Trying to avoid CSE.

[bird333]

Or upgrade to O365, where array entry is no longer required.

Unfortunately can't upgrade.

[AliGW]

Thread retitled and moved to a more appropriate forum section.

[AliGW]

Many members offer AGGREGATE solutions to circumvent CSE issues. Here's a link to some YouTube tutorials that I noticed Caracalla had posted in another thread yesterday: https://www.youtube.com/user/ExcelIs...uery=aggregate

[hrlngrv]

When you finally get the latest Excel with spilled formulas, you won't need [Ctrl]+[Shift]+[Enter] except to constrain array results to given ranges.

That said, why avoid array formula entry? There's a misconception that array formulas are slower than alternatives. That's seldom the case unless calling the same match or lookup function multiple times.

[hrlngrv]

A great many functions don't need [Ctrl]+[Shift]+[Enter] as long as one avoids using IF or, oddly, matrix functions like MMULT and TRANSPOSE, which are usually rather useless not entered as array formulas, generally speaking.

For example, if using a version which lacks MINIFS, =MIN(INDEX(NOT(array_condition)*1E300+array_values,0)) should work to return the conditional minimum, but the array formula =MIN(IF(array_condition,array_values)) should be more efficient in terms of both memory usage and recalc time.