Using {} brackets in formulae

S

Stephen Pain

Hi all,

I am using a forecast spreadsheet template with the following formula:

{=AVERAGE(IF(D$7:D$31=D14,E$7:E$31,""))}

to create a moving average index value. However, I have never see
these {} brackets used in a formula before. What dot hey do as th
solution appears to change without them. Furthermore, if I attempt t
alter the contents of the formula - for example the range of values
the brackets disappear, and if I type them manually Excel fails t
recognise it as a formula and I get a text string.

Please help!

Stev
 
E

ehntd

The brackets signal that the calculation must be done on an array o
elements, rather than just one single element. That is why, if yo
remove them the result changes. If you have more than one elemen
being compared you need the brackets
 
J

Jim Rech

Further to ehntd, formulas with brackets are called "array formulas" and
they are created by "array entering". To array enter a formula you press
Ctrl-Shift-Enter rather than just Enter.

Chip Pearson has a page on array formulas:

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

--
Jim Rech
Excel MVP
|
| Hi all,
|
| I am using a forecast spreadsheet template with the following formula:
|
| {=AVERAGE(IF(D$7:D$31=D14,E$7:E$31,""))}
|
| to create a moving average index value. However, I have never seen
| these {} brackets used in a formula before. What dot hey do as the
| solution appears to change without them. Furthermore, if I attempt to
| alter the contents of the formula - for example the range of values,
| the brackets disappear, and if I type them manually Excel fails to
| recognise it as a formula and I get a text string.
|
| Please help!
|
| Steve
|
|
| --
| Stephen Pain
| ------------------------------------------------------------------------
| Stephen Pain's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=15261
| View this thread: http://www.excelforum.com/showthread.php?threadid=275578
|
 
J

Jerry W. Lewis

You do not type them, Excel supplies them when you array enter
(Ctrl-Shift-Enter).

Jerry
 
Top