Jump to content


Photo

Curly brackets in Excel formulas

formulas arrays curly brackets

  • Please log in to reply
2 replies to this topic

#1 Carlton Seymour

Carlton Seymour

    Advanced Member

  • Members
  • PipPipPip
  • 41 posts

Posted 16 March 2014 - 05:13 AM

Is there a logic for when to use curly brackets versus regular brackets? How does one know if they can use multiple formulas within a function?

 

Regards

 

Carlton



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 16 March 2014 - 10:14 AM

Hi Carlton,

 

Curly brackets are used to define "arrays" within formulas, and can technically be used with any function that accepts a range of values.

 

For example, you can define a "virtual" range of cells by using the following array formula:

{"a",1;"b",2;"c",3}

 

This is effectively the same as:

arrayrange.png

 

You could write a VLOOKUP formula to search for the value corresponding to the letter b as follows:

=VLOOKUP("b",{"a",1;"b",2;"c",3},2,FALSE)

 

This will return 2 - the number that corresponds to the letter b.

 

This technique is most often used with functions such as SUMIFS and OR.  Examples:

=OR(A2={"a","b","c"})

=SUM(SUMIF(A2:A5,{"a","b","c"},B2:B5))

 

For a more comprehensive look at the OR and SUMIF functions, see the following video lessons:

Expert Skills Lesson 3-6: Use the SUMIF and COUNTIF logic functions to create conditional totals

Expert Skills Lesson 3-16: Use the AND and OR functions to construct complex Boolean criteria


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Carlton Seymour

Carlton Seymour

    Advanced Member

  • Members
  • PipPipPip
  • 41 posts

Posted 16 March 2014 - 12:21 PM

As always thank you Jonathan







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users