Wednesday, September 26, 2012
The Intersect Operator!
The syntax is simply: = RangeName1 RangeName2 (Note: No brackets or commas…) Now, you can do this by using the generic names of the ranges (i.e. =B2:B34 B10:H10), but that is a bit clumsy (and who needs Clumsy…).
A much better way is create Named Ranges. This can be easily done by Highlighting your entire database including the headings with Ctrl + A and then click Ctrl + Shift + F3. This will bring up a dialogue box as shown below (Click OK). Very Cool!
Once you have your ranges Named, you can then (as with the example below) find a value with the elementary (my dear Watson) function: =East Quarter3
But That’s Not All! You can combine the useful Intersect Operator with statistical functions to find even more information! For instance, if you wanted to find the overall average quarterly sales for the “East” region, you could use: =AVERAGE((East Quarter1):(East Quarter4))
Yes, there are other ways of doing this in Excel, but any Excel Geeks worth their pocket protectors should have the Intersect Operator in their bag of tricks.
All the best!