Curly bracket, parentheses and square brackets in formula

[darenw]

What is the terminology and concept behind this? It is probably easy to learn about if we knew the name of this oddity.

Our spreadsheet shows formulas that show like this:

{sum(if(...))}(1,1)[0][0]

The ... indicates omitted detail. We want to modify the formula to get data from a different column, but any attempt to edit the formula causes the curly brackets and stuff at the end to vanish. Then the cell shows "#VALUE!" unless we back out of the change.

Related to this is that when we save this spreadsheet in Gnumeric, and try to read it in Excel (not sure which version) the curly brackets and funny stuff don't appear unless we are careful to save it as "Excel 5.0/95", we wear the right color socks, and Mercury is trine with Jupiter... Is there a more reliable, version-independent way to accomplish whatever magic is going on?

BTW, google isn't any good at searching for "(1,1)[0][0]"!

 

[phxsportz]

When you edit these formulas you need to hit
CTRL+SHIFT Enter.

This is an array formula

Here's a good site that explains it well..

http://www.cpearson.com/excel/ArrayFormulas.aspx

PS { referred to as Curly Braces

 

[jbeaucaire]

Braces { } appear around a formula when you confirm it by pressing CTRL-SHIFT-ENTER. (CSE)

This activates an array causing the formula to run multiple times for all the possible combinations of variables in the formula. When you edit the formula, you must reconfirm with CSE to reactivate the array with the new variables. THEN copy the cell down/across/whatever.