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?

 

[DRSteele]

It would be helpful if you'd provide some sample data and an indication of what you want done with it.

You can provide sample data by following the link in my signature below.

 

[Dashnick]

Thanks DRSteele.. Below is a simple example. Note how the attempt does not work, as instead of giving the intended value of MAX(0,C) for each cell C, it instead takes the max of the entire array.

In general, the problem is basically that some functions you want to return an array where the function takes in an array of scalars and outputs an array of scalars, but instead, since the function is already expecting an array (like MAX()), it just outputs a single scalar.

Any way around this?

Thanks
Dave

Excel 2010

	D	E	F	G	H	I	J	K
5			Actual	Actual	Actual	Trend	Trend	Trend
6			Jan-15	Feb-15	Mar-15	Apr-15	May-15	Jun-15
7	Raw Trend		4.00	3.00	2.00	0.97	-0.05	-1.10
8	Bottomed at 0		4.00	3.00	2.00	0.97	0.00	0.00
9
10	Attempt in Single Cell		4.00	3.00	2.00	0.97	0.97	0.97

<tbody>
</tbody>

MrExcel Demo​

Worksheet Formulas Cell Formula F8 =MAX(0,F7) G8 =MAX(0,G7) H8 =MAX(0,H7) I8 =MAX(0,I7) J8 =MAX(0,J7) K8 =MAX(0,K7) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula I7:K7 {=TREND(F7:H7,F6:H6,I6:K6)} I10:K10 {=MAX(0,TREND(F10:H10,F6:H6,I6:K6))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[DRSteele]

Too much thinking can cause excess work.

Excel 2012

	D	E	F	G	H	I	J	K
5			Actual	Actual	Actual	Trend	Trend	Trend
6			15-Jan	15-Feb	15-Mar	15-Apr	15-May	15-Jun
7	Trend		4.00	3.00	2.00	0.97	0.00	0.00

<tbody>
</tbody>

Sheet26​

Worksheet Formulas Cell Formula I7 =MAX(TREND($F$7:$H$7,$F$6:$H$6,I6),0) J7 =MAX(TREND($F$7:$H$7,$F$6:$H$6,J6),0) K7 =MAX(TREND($F$7:$H$7,$F$6:$H$6,K6),0) <tbody> </tbody>

<tbody>
</tbody>

 

[XOR LX]

Hi.

Perhaps you mean:

=IF(FunctionThatReturnsAnArray>0,0,FunctionThatReturnsAnArray)

which will generally require CSE.

Regards

 

[Dashnick]

What do you mean by CSE? Yes, that would work in this example, but my main point is in general how to do this; can I just not use functions like MAX() or SUM() that take inputs as arrays?

 

[DRSteele]

If you insist on making it all complex, try this:

Excel 2012

	D	E	F	G	H	I	J	K
5			Actual	Actual	Actual	Trend	Trend	Trend
6			15-Jan	15-Feb	15-Mar	15-Apr	15-May	15-Jun
7	Trend		4.00	3.00	2.00	0.97	0.00	0.00
8
9	Trend, complex way		4.00	3.00	2.00	0.967853	0	0

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet26​

Worksheet Formulas Cell Formula I7 =MAX(TREND($F$7:$H$7,$F$6:$H$6,I6),0) J7 =MAX(TREND($F$7:$H$7,$F$6:$H$6,J6),0) K7 =MAX(TREND($F$7:$H$7,$F$6:$H$6,K6),0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula I9:K9 {=IF(TREND($F$9:$H$9,$F$6:$H$6,$I$6:$K$6)<0,0,TREND($F$9:$H$9,$F$6:$H$6,$I$6:$K$6))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Dashnick]

Thanks DRSteele. The reason I want to do it as a single array formula instead of separate formulas is to eliminate duplicate calculation; I actually have a custom UDF that takes considerable time, and repeating the same calculation for each column is a lot of wasted effort...

Again, your second example works in this scenario, but not in general; my point is around whether or not I can use SUM() and MAX() etc in this way or I just have to find workarounds.

Thanks again!

 

[XOR LX]

What do you mean by CSE?

CTRL+SHIFT+ENTER, the keystroke combination required to commit array formulas.

can I just not use functions like MAX() or SUM() that take inputs as arrays?

No, the explanation for which is precisely as you gave previously (and succinctly, I might add).

Regards

 

[Dashnick]

CTRL+SHIFT+ENTER, the keystroke combination required to commit array formulas.

yes of course lol. I got excited and thought it was something else.

No, the explanation for which is precisely as you gave previously (and succinctly, I might add).

Bummer.. oh well, worth a shot

By the way, do you know of a resource that explains in some level of technical detail how exactly array formulas are implemented in Excel?

Thanks!
Dave