• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

CURLY BRACKETS IN EXCEL

saqibhanif37

New Member
Hello. My name is Saqib.
I am confused with the array formulas and curly brackets used as functions but not array formulas. I do understand they look something like this
{=<formula line>}, I am wanting to learn, what does the curly bracket really do in excel if it is not an array formula? I mean there has been posts where people use functions, part of which is "...find({1,2,3,4,5,6,7,8,9,0},A2)..."
This function doesn't really appear to work if used like this:
Assuming Cell A2 is "Saqib6" and I use a function in B2 which is "find({1,2,3,4,5,6,7,8,9,0},A2), it wouldn't work. I'm only trying to understand the ligatures of the curly bracket.
 
Perhaps it makes more sense like in the attached workbook.
Some formulae can handle array input or can give an array result. With the new calculation engine (will be released in version 2019) you will see those values get spilled. In all previous version you don't see then, but all values are calculated.
If you want to learn, google Mike Girvin, the author of CONTROL+SHIFT +ENTER, a reference on array formulae, how they work, and how to build them.
 

Attachments

  • example.xlsx
    8.8 KB · Views: 24
It does work. The quantity in braces is an array constant, so
={1,2,3,4,5,6,7,8,9,0}
entered with CSE is a row array of 10 digits.
= FIND( {1,2,3,4,5,6,7,8,9,0}, "Saqib6" )
searches for each digit in turn within your name. Only 6 is present, so the function returns five #VALUE! errors, then a 6 showing the position of the digit 6 in the string, followed by four further #VALUE! errors. It might have been more instructive to search for {"a","b",6}, or at least more successful. That would have given
2,5,6
as an array of three results.
 
@saqib
That is because you have not selected the array of 10 cells and committed the formula with Ctrl+Shift+Enter.
The error you see is only the first result of 10. All but one will be errors because you seek to find a string that is not present.
 

Attachments

  • Array FIND (PB).xlsx
    9.9 KB · Views: 15
Last edited:
Prettied up a little, so that it looks less like an error.
As Guido pointed out, on a dynamic array installation, it is no longer necessary to commit the formula to a contiguous range of cells with CSE.
All that is needed is to type the formula into one cell and enter. The array is a property of the one cell but it is displayed over multiple cells provided they are empty.
The attached file assumes a standard Office installation though.
 

Attachments

  • Array FIND (PB).xlsx
    16.9 KB · Views: 22
Last edited:
Hi Saqib,

Visit the following almost 5 years old thread, you may find some interesting points:


Regards,
 
Back
Top