What is an array formula?

[Sandy]

Good day,

What is an array formula? I understand we need to input
a range with ctrl+shift+enter. Can't we just type
=average(c2:c45)? Does the colon make it an "array".

Thank

Sandy

 

[Trevor Shuttleworth]

Sandy

take a look at Chip Pearson's site where he explains array formulae:

http://www.cpearson.com/excel/array.htm

Regards

Trevor

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/array.htm

 

[Dave R.]

I wouldn't want to TRY to describe an array formula in any REAL detail,
others here can do that much better..

But using CSE tells certain functions that you are "dealing with" a group of
numbers (an array) rather than just one. Certain functions, like SUM and
SUMPRODUCT, work automatically on arrays.. but they are usually not referred
to as array formulas unless they happen to contain some other function that
needs to have CSE pressed when the formula is entered.

In your average formula, as it's written, there will be no effect other than
having { } around it of entering it with CSE or just enter.

Here's one example of the difference between pressing CSE (i.e. set off
array formula) and not, and getting the right answer, or not;

A B
apple 50
apple 99
orange 100

if you want a formula to tell you the greatest value in B when A is "apple",
you would use:
{=MAX(IF(A1:A3="apple",B1:B3,""))} with CSE -- you would get 99, the correct
answer.

If you entered without CSE, you'd get 100 because (I think, could be wrong)
IF doesn't work automatically with arrays, while MAX does-- so if you don't
use CSE, it evaluatesas MAX(IF(A1="apple",B1:B3,"") -- that is, it only
looks at the first cell (A1), says "yeah, A1 equals APPLE" then returns the
MAX value from B1:B3, which is 100.

Hope this helps, and that others can correct if I'm wrong..

 

[Harlan Grove]

. . . Certain functions, like SUM and SUMPRODUCT, work automatically
on arrays..

....

Really?! What do you get for =SUM(ROW(1:3)) entered normally and entered as
an array formula?

Almost all functions handle array *constants* properly. Aggregating
functions, such as SUM and AVERAGE, handle *ranges* properly. SUMPRODUCT and
LOOKUP (and to an extent FREQUENCY) handle calculated arrays properly
without array entry, but they're about all as far as I'm aware.