Results 1 to 3 of 3

Curly bracket, parentheses and square brackets in formula

This is a discussion on Curly bracket, parentheses and square brackets in formula within the Excel Questions forums, part of the Question Forums category; What is the terminology and concept behind this? It is probably easy to learn about if we knew the name ...

  1. #1
    New Member
    Join Date
    Jul 2009
    Location
    Florida
    Posts
    6

    Question Curly bracket, parentheses and square brackets in formula

    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]"!

  2. #2
    Board Regular phxsportz's Avatar
    Join Date
    Jun 2006
    Location
    Phoenix, AZ
    Posts
    1,985

    Default Re: Curly bracket, parentheses and square brackets in formula

    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
    I used to have a Darkroom, Now I have a PC... And it smells better
    WWW.SPUDSBAR.COM

  3. #3
    Board Regular jbeaucaire's Avatar
    Join Date
    May 2002
    Location
    Bakersfield, CA
    Posts
    5,755

    Default Re: Curly bracket, parentheses and square brackets in formula

    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.
    Microsoft MVP 2010 - Excel
    Jerry Beaucaire's Excel Tools

    "Actually I *am* a rocket scientist." -- JB

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com