some function has a curly brackets { } by pressing CTRL + SHIFT + ENTER
What is this curly brackets what thy do in the function ..
can i use it in the SUM() FUNCTION? IF I USE IS IN SUM FUNCTION WHAT IT WILL DO ?
CAN I HAVE AN EXAMPLE FOR THIS ?
some function has a curly brackets { } by pressing CTRL + SHIFT + ENTER
What is this curly brackets what thy do in the function ..
can i use it in the SUM() FUNCTION? IF I USE IS IN SUM FUNCTION WHAT IT WILL DO ?
CAN I HAVE AN EXAMPLE FOR THIS ?
=SUM((A2:A10="Fax")*(B2:B10="Brown")*(C2:C10))
Hi
Its an array formula above is an example ctrl shift enter puts in the curlys a google search should give you lots more just search on array formula syntax for sum function or similar
Chris
Click my star if I helped Thanks
http://www.myonlinetraininghub.com/excel-array-formula
Hi
Had a look on the internet for you if you click the above link it explains arrays in detail in easy to understand english.
Chris
The curly brackets (in this case) tell us that you've told Excel to treat the function as an arrayed function. To my understanding, when this is done, Excel knows to designate space in memory for storage of intermediate values when it would not normally do so. For example
you have numbers in A1:B6 and you want to sum the difference in each row (i.e (B1-A1)+(B2-A2)+...
=SUM(B1-A1,B2-A2,B3-A3,B4-A4,B5-A5,B6-A6) will work
or
using CONTRL SHFT ENTER
=SUM(B1:B6-A1:A6) tells excel to store these intermediate values in memory for later use (SUM). IF you don't use CNTRL SHFT ENTER, you'll get an error.
It takes some practice and some imagination to figure out which functions can be made to work as arrays and which can't. (more like under what circumstances can a function be made an array and when it can't)
Here's another example
You want to do a INDEX/MATCH on two columns concatenated instead of 1
i.e. if Col A has male names and B has female and you want the value in C that corresponds to James in A and Mary in B
=INDEX(C1:C10,MATCH("JamesMary", A1:A10&B1:B10,0)) will only work if you tell excel to treat it as an array (store all those intermediate values (A1&B1, A2&B2, etc)
Hope that helps.
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Array formulas well explained..
www.cpearson.com/excel/arrayformulas.aspx
Life's a spreadsheet, Excel!
Say thanks, Click *
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks