3

Has anybody played around with using braces in a formula? Syntactically, they seem to define collections inline in a formula. It would be great if we could figure out how to define multi-row collections. My apologies for not making this inline code but I think the paste is clearer.

But to give an idea of what I am talking about, the following will produce 2.

MATCH(TRUE,{FALSE,TRUE,TRUE},0)

Spreadsheet snippet of examples of using braces

1

Well, thank you Solar Mike, looking at Excel documentation gave me the answer to my question about how to define an inline collection with rows and columns.

My mistake was trying to use C++ array initializer syntax. Instead you use a semicolon like so:

=ROWS({FALSE,4;TRUE,5;TRUE,6})
=COLUMNS({FALSE,4;TRUE,5;TRUE,6})
=VLOOKUP(TRUE,{FALSE,4;TRUE,5;TRUE,6},2,0)

The first statement produces 3, because the semicolons signify a new row. The second statement produces 2, because there are two comma-separated elements between each semicolon and commas signify a new column. And finally the VLOOKUP produces 5, the value of the element in the second column of the second row which is returned by VLOOKUP.

Et voilà!

0

Those braces contain an array of items to be tested, you can just as well reference a range of cells containing the same items to be tested.

The braces work with many functions and they also work in Excel...

Note, your match produces "2" as the result as that is the position of the first matching result, not the count of the number of "true"...

If you try:

=match(true,{true,false,true},0)

then the result is 1 (for both Numbers and Excel).

  • OK, so can you specify multi-dimensional collections in Excel. Would this work in Excel? INDEX({{1,2},{3,4}},2,2) Does it produce 4? – WilyUlysses Apr 9 at 12:31
  • @WilyUlysses does it work in numbers? – Solar Mike Apr 9 at 13:02
  • No it doesn't. I have examples of this in the image I posted with the question but basically it always has one row. I use collections of collections to try to build a multi-dimensional collection array. For example COLUMNS({{FALSE,1},{TRUE,2},{TRUE,3}}) produces 3, as one would expect since it's a collection of 3 collections. But ROWS({{FALSE,1},{TRUE,2},{TRUE,3}}) produces 1 not 2. – WilyUlysses Apr 9 at 13:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .