Funcs work differently as array formula?

  • Thread starter Thread starter Jonathan Rynd
  • Start date Start date
J

Jonathan Rynd

While doing some auditing I noticed that the IF() worksheet function
behaves differently when it is entered as part of an array formula
(control-shift-enter) vs. regularly.

The biggest difference is what it outputs. The regular IF() just outputs
one item; the array formula IF() outputs a range.

I understand why Excel does this, and it is useful if a bit surprising.

My question is: Are there any other functions that exhibit different
behavior or outputs depending on whether it is part of an array formula?
 
Hi Jonathan,
There are many functions that return more than one value: FREQUENCY, LINEST,
MMULTI and to get all the values one must use SHIFT+CTRL+ENTER. In all
cases, if you just use ENTER you get only the first value.
To get more info visit Chip's page http://www.cpearson.com/excel/array.htm
Best wishes
Bernard
 
There are many functions that return more than one value: FREQUENCY, LINEST,
MMULTI and to get all the values one must use SHIFT+CTRL+ENTER. In all
cases, if you just use ENTER you get only the first value.
Click to expand...
...

Maybe this changed in later versions, but the only time MMULT returns anything
other than #VALUE! when entered just with [Enter] is when it's fed two numeric
scalars. Even then it returns a degenerate 0D array, e.g., {1} rather than just
1.

In most situations, SUMPRODUCT and LOOKUP are the only functions I've found that
always return the same results as regular and array formulas as long as there
are no other functions involved. Some other functions behave the same entered as
either type of formula when fed only scalars and array constants, but require
array entry when fed ranges or derived arrays, e.g., OR(A1={1,2,3}) is
indifferent to formula type, but OR(A1=COLUMN(A:C)) requires array entry.
 
Most functions that take a single value as a particular argument will
return an array if an array is passed for that argument.

Jerry
 
Back
Top