Array formulas containing functions that take arrays as formulas

[Dashnick]

Is there a way to force excel to take formulas that normally input arrays and instead only take scalars?

As an example, say I have an array formula over a number of cells, but I only want to return 0 or greater in any given cell

So like

{=MAX(0,FunctionThatReturnsAnArray())}

say input over cells A2:C2. Problem is, this will take the entire array as input to the MAX() function, instead of cell by cell. This means that it returns the maximum of the array returned by FunctionThatReturnsAnArray() (and 0) as a scalar, instead of an array of values >=0.

Is there a way to force excel to take formulas that normally input arrays and instead only take scalars? Does this make sense?

 

[Dashnick]

Why once? It still has to calculate that formula for different inputs.

Ah, sorry - I see what you mean. In this example I suppose that is true, it really is doing it 3 different times. In my UDF (which is exponential smoothing by the way), it would literally calculate the entire array of outputs each time, and just pick a single one if it is specified by an optional argument. Otherwise it just returns the entire array (if the optional pointer isn't specified).

I suppose the actual nature of the function matters; if it is just as much work to generate the entire array the first time than to recreate it for each call, then it wouldn't make a difference. In the case of exponential smoothing, you might imagine, there is a pretty big fixed cost upfront each time the function is run, so you'd want to minimize the number of times it is run.

Thanks for the great discussion.

 

[Dashnick]

hmm...it worked for me

Got it.. So in general would you say the workaround is:

{=FunctionLikeMAX(someScalar,INDEX(FunctionThatReturnsAnArray(inputArray),COLUMN(A1:INDEX(1:1,COUNT(FunctionThatReturnsAnArray(inputArray))))))}

if inputArray is a row, and

{=FunctionLikeMAX(someScalar,INDEX(FunctionThatReturnsAnArray(inputArray),ROW(A1:INDEX(A:A,COUNT(FunctionThatReturnsAnArray(inputArray))))))}

if inputArray is a column?

(Using the ROW() and COLUMN() things here to dynamically create arrays, let me know if there is an easier way to do this).

(Edited because I realized the COUNT() should be on the size of the output. I realize this is very unseemly, but now its really a matter of principle )

 

[XOR LX]

Thank you for raising such an interesting debate!

This link seems to suggest that the multi-cell version might be a tad more efficient:

Excel Array Formulae - Decision Models

although it does not appear to be by a great deal ("A range array formula covering multiple cells may be faster to calculate than individual formulae in each cell (although the speed advantage seems less in Excel97 and Excel2000).")

I personally always use the single-cell variety, though that's perhaps just out of habit.

Best of luck with the project and come back anytime!

Regards

 

[Marcelo Branco]

Got it.. So in general would you say the workaround is:

i should have used (in blue) instead of {1,2,3}
=MAX(0,INDEX(TREND(F10:H10,F6:H6,I6:K6),COLUMN(F10:H10)-COLUMN(F10)+1))
Ctrl+Shift+Enter

M.