The Intersect Operator Revisited

The Intersect Operator is one of my favorite obscure Excel tools. Although relatively obscure, it is highly useful, as there are many ways it can be used to garner information from Excel tables without using awkward formulas that can take time and patience.  Happily, the Intersect Operator, is not only Utile and Flexible, it is also quite Easy to use (which is always a good thing…).

The syntax is simply: = RangeName1 RangeName2 (Please Note: No brackets or commas…) Now, you can do this by using the generic names of the ranges (i.e. =C2:C34 B10:H10), but that is a bit awkward (and who needs that!)

As you may know, I am a huge proponent of creating Named Ranges in your Excel workbooks. 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 default dialogue box as shown below (Just click OK, and Bamm, Named Ranges!).

Once you have your ranges Named, you can then (per the example below) find a value with the rudimentary function: =East Quarter3

But, Wait, 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))

Are there many other ways of doing this in Excel?  Of course, but any good Excel Guru should have the Intersect Operator in their bag of tricks. (You can never have too many tricks…).

The Excel Slot Machine

Excel users can be a very sensible lot, but Not Everything in Excel needs to be so terribly Serious. In fact, you can take some the very sensible tools in Excel and have some Fun with them. The post I made back in late 2012 has been one of the most popular in the 8 years I have been writing this blog.

It all revolved around using the fascinating RAND function for purposes not originally intended. In this case, an Excel Slot Machine!

The intriguing RAND function returns a Random Number that is Greater Than or Equal to 0 and Less Than 1. Each time your worksheet recalculates, (by reopening or forced recalculation by pressing F9), the RAND function returns a New Random Number.

Of course, some hard core statisticians have voiced concerns about the true Randomness of the RAND function (it is prone to sequential correlations if large runs of numbers are taken), but it more than suffices for most demanding statistical applications, and is certainly acceptable for us mere mortals.

The Syntax for the Rand function is simply: RAND( )

If you want to create a random number between two numbers, (where a is the smallest number and b is the largest number), you can use: =RAND()*(b-a)+a

If you want only Whole Numbers try using: RANDBETWEEN()

For example, =RANDBETWEEN(1, 100) will produce a Random Whole Number between 1 and 100.

There are countless statistical applications, of course, but you can also use it to have a bit of Fun. I have used the RAND function in conjunction with other functions and graphics to create a Slot Machine in Excel (send me a request at excelenthusiast@gmail.com if you would like a copy of the Slot Machine spreadsheet). I will be happy to send a copy to you.

Is an Excel Slot Machine an acceptable application of the power of Excel? Well, certainly some of the more Sensible ones among us may not think so, but hey, it’s good to have a bit of fun now and then. The RAND function. Great for use in statistical applications, building games, slot machines, and other Fun Stuff!

Quizzes in Excel

Only a very small percentage of Excel users ever get around to exploring Form Controls.  This is unfortunate, as they can be instrumental in creating engaging, dynamic Excel workbooks and tools.  Option Buttons, (one the tools contained in Form Controls) are one way to add this ability to your Excel creations, and make them more professional in the process.

Option Buttons can be very useful in creating Quizzes in Excel, and although they can at first appear a bit complex, they are in fact quite easy to create.  The following is a Step-By-Step Explanation of how to create your first quiz using Option Buttons:

1.   Add the Developer Tab on your toolbar:

a.   Click the File tab and then click Options, and then click the Customize Ribbon category.

b.   In the Main Tabs list, select the Developer check box, and then click OK

2.   Under the Developer tab, choose Insert/Form Controls/Option Button

3.   Draw the Option Button box on your worksheet and repeat for however many buttons you wish to have for choices

4.   Now, (this is Important), to make the buttons work together, return to your Insert dropdown and choose Group Box from the Form Control group

5.   Draw your Group Box all the way around your Option Buttons

Hang in There, We are Almost Done:

6.   Right-Click one of the Option Buttons, choose Format Control from the dropdown and select a Cell Link that you want your Option Buttons to populate

7.   Now is where it get Fun: Create a Formula that is based on the value that is shown in the Cell Link

8.   For example, let’s say you have linked three Option Buttons to Cell $E$5 and you wanted Option 2 to return a “Correct” response and the others to return “Incorrect”, here is what you do:

In cell E6, type =IF(E5=0,"", IF($E$5=2, "Correct!", "Sorry, Incorrect"))

Now when the quiz-taker chooses Answer 2 of the three possible, they are rewarded with the “Correct!” feedback.  Option Buttons, a remarkably forthright tool that let’s you do Even More in Excel!

Get & Transform

As we have discussed in years past, pulling data from sources outside of Excel is a fact-of-life for many Excel users.  Microsoft Query used to be the Go-To add-in for importing data into Excel, but it had several limitations.  Microsoft then introduced the considerably more robust Power Query, which provided a more intuitive user experience for uncovering, combining, and refining data across a wide variety of outside sources.

Microsoft Excel continues to evolve, of course, and what was previously referred to as Power Query is now known as the even more powerful Get & Transform tool in Excel 2016.

Get & Transform includes a powerful new set of features which provides fast, easy data gathering and shaping capabilities. It enables you to connect, combine, and refine data sources to meet nearly any of your analysis needs. These features are also used in Power BI, and in Power Query that were available for previous versions of Excel such as 2010 and 2013.

To access the power of Get & Transform in Excel 2016, just create a query in your workbook. A query, of course, enables you to connect to and transform data from a wide variety of available data sources. Loading the transformed data into a table, or into the built-in Data Model in Excel 2016 is a snap, and the data will even refresh when it is needed!

To create a query in Excel 2016, use the Data tab in the ribbon, then select the New Query button from the Get & Transform ribbon group.  Some of the data sources include:

•   Text file
• Web page
•  Facebook
• Wikipedia
• Sybase Database
• Teradata Database
• SharePoint List
• OData feed
•  CSV file SQL database
•  Microsoft Exchange
• Access database
• Oracle database

Get & Transform.  Another way to harness the Power of Excel!