Alternative to Array Formula

[yoursamrit2000]

I am using this formula :

=IFERROR(MEDIAN(IF((rngProgramme=rngEruCourse)*(rngOwnerType="LA")*(rngStartYearList=rngStartYear)*(rngCreatedDate1>=rngLeadsFrm)*(rngCreatedDate1<=rngLeadsTo)*(rngRating="Null")*(rngDisposition=""),rngAging)),"-")


THen Ctrl + Shift + Enter

and I have a lot of cells as such which hamper performance.

Looking for alternatives to CSE.

Please Guide!!!

 

[joeu2004]

I don't think there is a non-array formula alternative. But the problem might not be array formulas per se, but your implementation of them.

At a minimum, I believe the following is more efficient because it avoids the multiplications.

=IFERROR(MEDIAN(IF(rngProgramme=rngEruCourse, IF(rngOwnerType="LA",
IF(rngStartYearList=rngStartYear, IF(rngCreatedDate1>=rngLeadsFrm,
IF(rngCreatedDate1<=rngLeadsTo, IF(rngRating="Null", IF(rngDisposition="" , rngAging)))))))), "-")

Also, look at the named ranges. Are they whole-column references like $A:$A?

They might cause Excel to look at 1+ million rows, presumably unnecessarily. Instead, choose a reason max range; for example, $A$2:$A$100000.

 

[yoursamrit2000]

I have seen a couple of posts on the web regarding using INDEX and Match. I am not sure if this would help in avoiding ctrl+shift+enter alltogether.

 

[joeu2004]

Pressing CSE per se is not the problem. It simply tells Excel that an otherwise ambiguous formula should be treated as a multi-valued (array) formula, not "implicit intersection" that returns a single value.

I like to avoid pressing CSE because it is error-prone (we might forget after editing, and the formula might return a misleading result instead of #VALUE ). And yes, we might be able to wrap INDEX(...,0) around the array IF() expression to avoid pressing CSE.

But you are still dealing with an array formula (required for MEDIAN), and that is the root cause of the performance problem. You need to limit the size of the arrays (whole-column references), and you need to avoid unnecessary calculations (multiplications).

That said, the performance problem might be unavoidable if you have "lots" (how many?) of cells with such formulas, and their structure causes all or many to be recalculated often. In other words, sometimes it is just the nature of the beast.

But you have not provided sufficient information for us to say whether the spreadsheet design itself is a factor.

After avoiding whole-column references and unnecessary multiplications, there is a litany of things to look for that are common culprits. I cannot possibly address them at arm's-length. You can find some good discussions with a google search for "excel performance problems" without quotes. Look especially for articles by Charles Williams.

For example, the next thing to consider is whether any directly or indirectly referenced cells contain "volatile" functions that might cause the MEDIAN formulas to be recalculated unnecessarily. Some "volatile" functions are OFFSET, INDIRECT, RAND(BETWEEN), TODAY, NOW, etc. OFFSET and INDIRECT usually can be avoided by using INDEX; but the INDEX references are messy.

Good luck!