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?

 

[Marcelo Branco]

You can also try in I10:K10

=MAX(0,INDEX(TREND(F10:H10,F6:H6,I6:K6),{1,2,3}))
Ctrl+Shift+Enter

M.

 

[XOR LX]

If your concern is more that FunctionThatReturnsAnArray is a particularly lengthy and resource-heavy construction, and so its repetition (i.e. as part of an IF statement similar to that mentioned) would result in both a difficult-to-read and even more resource-heavy construction, then there are alternative workarounds which, depending on the precise nature of FunctionThatReturnsAnArray, would avoid that repetition.

Some of these workarounds are creative, some border on convoluted, so it's perhaps debatable whether the saving in formula-length and/or efficiency is worth neglecting the obvious IF statement.

Re array formulas, can you clarify what you mean by "implemented"?

Regards

 

[DRSteele]

This could be the array you put into your UDF: =(TREND($F$9:$H$9,$F$6:$H$6,$I$6:$K$6)>0)*TREND($F$9:$H$9,$F$6:$H$6,$I$6:$K$6)

 

[Dashnick]

Thanks! the repetition is not so much that it would be called twice in the IF() statement (though I'd be interested to hear your workarounds for that), but rather that, for example, in DRSTEELE's solution, the formula is written separately in each of the 3 columns, as opposed to a single array formula spanning the 3 columns. So the function has to recompute 3x as many times. In the actual project I am working on, it would be across something like 160 columns, so this would be 160x the effort.

Re array formulas, can you clarify what you mean by "implemented"?

Just something explaining what exactly Excel does when an array formula is entered as opposed to a normal formula.. I would imagine it is something like:

- for functions that expect arrays, proceed normally
- for functions that expect scalars, create an array of size equal to the input size and store each scalar output in that array for each scalar input

But that's really just what I imagine happening; I'd be interested to know if that was actually the case .

Thanks again!
Dave

 

[Dashnick]

Thanks Marcelo, but that doesn't seem to work for me... The INDEX() function just returns a single value...

 

[DRSteele]

This might be interesting to you. You could watch some youtube videos from ExcelIsFun, like these: https://www.youtube.com/watch?v=vqfikSMx7mE https://www.youtube.com/watch?v=tDGrkC-SQ-4&list=PLrRPvpgDmw0kjL4875H36yNhWBb0f-nci

 

[XOR LX]

Ah. Perhaps you are confusing the two types of array formulas, i.e. single-cell array formulas and multi-cell array formulas?

In almost all cases, they are interchangeable, in that a single-cell array formula, entered into a cell with appropriate absolute/relative referencing, and then copied to fill an nxm range, will give precisely the same results as the equivalent multi-cell version entered over that same range.

However, I do not know whether the latter, by technically comprising just a single formula (albeit one which must surely be re-computed for each of the referenced cells), is any more efficient in terms of calculation than the former. An interesting question, though. Hopefully someone on this forum will be able to confirm shed some light on this.

Regards

 

[Dashnick]

Ah. Perhaps you are confusing the two types of array formulas, i.e. single-cell array formulas and multi-cell array formulas?

Right, I think we have both types happening here at once.

However, I do not know whether the latter, by technically comprising just a single formula (albeit one which must surely be re-computed for each of the referenced cells), is any more efficient in terms of calculation than the former. An interesting question, though. Hopefully someone on this forum will be able to confirm shed some light on this.

For a single-cell formula that is repeated, unless excel has some way of knowing it already computed the answer in the previous cell, I'd imagine it would have to redo the calculation. If it is just a single multi-cell array formula, it just does the calculation once and spreads the result over the total range.

 

[Marcelo Branco]

Thanks Marcelo, but that doesn't seem to work for me... The INDEX() function just returns a single value...

hmm...it worked for me


[Table="class: grid"][tr][td] [/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][/tr]
[tr][td]

5​

[/td][td] [/td][td] [/td][td]

Actual​

[/td][td]

Actual​

[/td][td]

Actual​

[/td][td]

Trend​

[/td][td]

Trend​

[/td][td]

Trend​

[/td][/tr]


[tr][td]

6​

[/td][td] [/td][td] [/td][td]

jan/15​

[/td][td]

fev/15​

[/td][td]

mar/15​

[/td][td]

abr/15​

[/td][td]

mai/15​

[/td][td]

jun/15​

[/td][/tr]


[tr][td]

7​

[/td][td]

Raw Trend​

[/td][td] [/td][td]

4,00​

[/td][td]

3,00​

[/td][td]

2,00​

[/td][td]

0,96785​

[/td][td]

-0,04822​

[/td][td]

-1,09816​

[/td][/tr]


[tr][td]

8​

[/td][td]

Bottomed at 0​

[/td][td] [/td][td]

4,00​

[/td][td]

3,00​

[/td][td]

2,00​

[/td][td]

0,97​

[/td][td]

0,00​

[/td][td]

0,00​

[/td][/tr]


[tr][td]

9​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

10​

[/td][td]

Attempt in Single Cell​

[/td][td] [/td][td]

4,00​

[/td][td]

3,00​

[/td][td]

2,00​

[/td][td]

0,96785​

[/td][td]

0,00​

[/td][td]

0,00​

[/td][/tr]
[/table]

Selected I10:K10 and typed in the formula bar
=MAX(0,INDEX(TREND(F10:H10,F6:H6,I6:K6),{1,2,3}))
Ctrl+Shift+Enter

M.

 

[XOR LX]

If it is just a single multi-cell array formula, it just does the calculation once and spreads the result over the total range.

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

Regards