Array formulas containing functions that take arrays as formulas

Dashnick

New Member
Joined
Sep 10, 2015
Messages
35
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

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?
 
You can also try in I10:K10

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

M.
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
Thanks Marcelo, but that doesn't seem to work for me... The INDEX() function just returns a single value...
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,226,875
Messages
6,193,455
Members
453,801
Latest member
777nycole

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top