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