Array Formulas

J

Jon Dow

How do you know when to use ctrl+shift+enter to create an array formula vs
just hitting enter?
 
B

Bernard Liengme

An Array formula always need Ctrl+shift+enter.
So what are array formula?
a) if the formula returns more than one value (formula using functions like
LINEST, FREQUENCY, etc come to mine) must be entered as an array.
and
b) when you use an 'ordinary function but add something that makes an array
of data. For example =AVERAGE(IF(A1:A5>0,A1:A5,FALSE)). The IF generates an
array of five values. If you enter this with just Enter you get a #Value!
error.

Note that using Ctrl+shift+enter when a simple Enter is needed has not
dreadful effect. So the formula SUM(A1:A10) gives the same answer with
either form of commitment.

Chip has lots on array formulas at
http://www.cpearson.com/excel/ArrayFormulas.aspx
best wishes
 
H

Harlan Grove

Bernard Liengme said:
So what are array formula?
a) if the formula returns more than one value (formula using functions like
LINEST, FREQUENCY, etc come to mine) must be entered as an array.
Click to expand...

Any formula that would return multiple values needs to be entered INTO
A MULTIPLE CELL RANGE as an array formula, e.g.,
={0,1,2,3,4,5}*{0;1;2;3;4;5}.
b) when you use an 'ordinary function but add something that makes an array
of data. For example =AVERAGE(IF(A1:A5>0,A1:A5,FALSE)). The IF generates an
array of five values. If you enter this with just Enter you get a #Value!
error.
Click to expand...
....

OK, but try

=AVERAGE(IF({0;1;2;3;4;5}>2,{0;1;2;3;4;5}))

It's A LOT MORE SUBTLE AND COMPLICATED than you've put it.

A better but still incomplete rule of thumb is that most (but not all)
formulas that use single are multiple cell ranges where single cells
or values would normally be used require array entry, but formulas
using array constants as the only multiple value arguments usually
don't require array entry in formulas that would always return single
values, e.g., AVERAGE, SUM, MAX, MIN.
 
Top