CODEWITHSUNDEEP
excelexcel-formula
Anna V
Anna V

Reputation: 1

Spill error caused by array formula not updating at every calculation pass

I am running a simulation that depends on each iteration spitting out a list of named observations that meet certain criteria from another spreadsheet in the workbook. I used the filter function to make that list. Works great when the model is static. But when I run a simulation randomly varying certain inputs in the model, only about 1 in 10 iterations spits out a list. In those iterations that don't yield a list, I'm getting the "Spill range is unknown" error. Basically, it looks like the filter function doesn't update with each new iteration. Is there a way to either force it to update at every iteration? Or can someone thing of a reasonably substitute function?

This is the formula I'm using: =FILTER('Source'!$A$4:$A$712,'Source'!$CU$4:$CU$712<>""). Column A contains a list of names, and the values in CU contain a numeric value if the iteration picks that particular name.

I was expecting the filter function to update at every iteration of the simulation.

Upvotes: -2

Views: 58

Answers (1)

rotabor
rotabor

Reputation: 4493

Include the value for the if_empty argument:

=FILTER('Source'!$A$4:$A$712,'Source'!$CU$4:$CU$712<>"","")

Refer to FILTER function.

UPD

The root cause of the "Spill range is unknown" error is using any of RAND* function. Probably, the first or second argument, or both, of the FILTER function refers to the range which can contain a variable number of values caused by such functions as RAND, RANDARRAY, etc.

Refer to Use the Analysis ToolPak to perform complex data analysis to get known how to fill a range with independent random numbers.

One more solution is to replace the random-size array by the maximum desired size array of random values using a given probability (0.1 for e.g.):

=RAND()<0.1

Upvotes: 0

Related Questions