Alternative to Array Formula

yoursamrit2000

New Member
Joined
Nov 6, 2019
Messages
2
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,533
Office Version
2010
Platform
Windows
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.

Code:
=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

New Member
Joined
Nov 6, 2019
Messages
2
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

Well-known Member
Joined
Mar 2, 2014
Messages
2,533
Office Version
2010
Platform
Windows
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!
 

Forum statistics

Threads
1,078,435
Messages
5,340,250
Members
399,361
Latest member
Linford

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top