Option Buttons

In the old Clint Eastwood movie, Magnum Force, Harry Callahan used the line, “A man’s got to know his limitations”.  That may be so, but it is always good to have Options.  For this last post of 2010, I thought it would be interesting to take a look at Option Buttons.

Option Buttons can be very useful in creating Quizzes or Polls in Excel, and are not too difficult to create.  There are a number of steps, but I guarantee that it will be well worth it if you hang in there and give them a try!  Here is how you do it:

1.  Make sure the Developer tab is visible on your toolbar

2.  Under the Developer tab, choose Insert / Option Button (Form Control)

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 (Form Control)

5.  Then draw your Group Box all the way around your Option Buttons

Stick with me now, we’re almost there!

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 the Fun really begins.  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(E5=3, "Correct!", "Incorrect"))

Now when the user chooses Option 2 of the three possible, they are rewarded with the “Correct” feedback.  Option Buttons, give them a try; “It is always good to have options…”.

Happy New Year All!

            

A Little Excel Magic

Some shortcuts and tips can often appear Magical!  Here are a few of my current favorites:

Select Noncontiguous Cells

If you want to select noncontiguous cells in a worksheet, just hold down the CTRL key and click on the cells you wish to select.  Presto!

Enter Strings Starting with Zero

Sometimes, as in the case with zip codes, you want to enter strings of numbers that start with zero.  To do this, simply type an apostrophe first.  Alacazam!

Rearrange Rows and Columns

So, you have your data are in rows and you want them in columns (or vice versa)?  Highlight the cells that have your data, right-click, copy, and choose Paste Special. Then check the "Transpose" box. Voila!

A Room with a View

Feeling a bit cramped when viewing your spreadsheet?  You can hide columns or rows you don't need to be visible by right-clicking and selecting “Hide”.  Any functions tied to data in the hidden areas will still operate fine.  Open Sesame!

Instant Chart

Impress your friends and family by making an instant chart.  Simply select your data and click F11.  Magic!

I hope you have a Magical (and safe) Holiday Season!

Concatenation Revisited

This is one of my favorite time savers. I teach Excel, and I have had classes as large as 200 students. Since I believe strongly in frequent communication, I keep in touch with individual students and the group as a whole. Having a spreadsheet of student names (or company employees perhaps) is great to stay organized, but what if you want to use your data for sending emails? This is where Concatenation Shines!

Consider that you have a list of students with the First Names in Column A and Last Names in Column B. The good news is that you can easily combine them into an Email-Friendly column of names in a “Last Name, First Name” format is easy. The secret to Concatenation is using Ampersands and cell references combined with quoted text or punctuation. Here’s how to do it:

Assuming your table starts in cell A1, put the following formula in C1 (be sure to note that there is a space after the comma in quotation marks):

=B1&", "&A1

This simple formula combines the contents of B1 (last name)with a comma, space, and contents of A1 (first name). Select C1, place your cursor over the “handle” in the lower-right corner of the cell, and give a quick double-click. This will populate your Column C Email List down as far as you have data in Columns A and B.

Then it is a simple matter of copying the contents of Column C, and pasting into Outlook. Bamm! You have just saved a Ton of Time!

Using the RAND Function

A very interesting and at times very useful function in Excel is the RAND function. This 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. While some hard core statisticians have voiced concerns about the true Randomness of the RAND function, it suffices for nearly all but the most demanding statistical applications.

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 the following:

=RAND()*(b-a)+a

If you want only Whole Numbers you can use:

RANDBETWEEN ()

For example, =RANDBETWEEN(1,200) will produce a random whole number between 1 and 200.

So how can you use this nifty little function? It can be used in myriad ways; for example, it can be used in conjunction with other functions to create a Password Generator. There are countless statistical applications, but you can also use it for entertaining applications. For example, one Excel Enthusiast used it create a Tetris-style game in Excel.

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).

The RAND function. Great for use in statistical applications, as well as some RANDom FUN!

Choosy Moms Use “Choose”

Having a Choice is (as Martha Stewart would say) “a good thing”. Nested IF functions can be very useful, but they have limitations (the maximum nested functions is 7) and they are cumbersome.

Happily, there is an alternative choice to using these awkward IF functions: The CHOOSE function to the rescue! The Choose function is straightforward and simple to use, plus it can be combined with other Excel features to make it even more powerful! The function returns a value from a list based on a given position (Index Number).

Here is the uncomplicated syntax:

Choose( Index_Number, Value1, Value2, ... Value_n )

Some examples are as follows:

=Choose(3, “Apples”, “Pears”, “Plums”, “Cherries”) returns Plums

It also works with ranges:

=Sum(Choose(2, A1:A20, B1:B20, C1:C20) returns the Sum of B1:B20

You can, of course, link it to the value in a cell, which makes it more flexible. For example, you could link it to A1 which contains the Index_Number.

If A1 contains the number 4, then =Choose(A1, “Apples”, “Pears”, “Plums”, “Cherries”) would return Cherries.

The Choose function can handle up to 29 options, which makes it a great choice in many real-life situations. Give it a try and find out why Choosy Moms Use “Choose”.

Scatterplots and the Coefficient of Determination

One of my favorite charts and accompanying functions are Scatterplot (XY) Chart and the Coefficient of Determination function.

A Scatterplot Chart is commonly used to show the relationship between two variables or sets of data. For example, a sales manager could plot the number of sales calls taken with the number of sales made. Another example is comparing the average length of time a customer service representative takes per call and the overall quality score of their calls.

To determine how strong the correlation is between the sets of data, the wily Excel user can make a Scatterplot Chart and:

1. Right-click on one of the data points and
2. Choose Add Trendline
3. Right-click the Trendline and choose Format Trendline
4. Format the Trendline to your aesthetic preferences and
5. Put a Checkmark next to Display R-squared Value on Chart

The R-squared value is your Coefficient of Determination (COD) that will tell you how strong your data on your two axes. In the graph example above the COD value is .5574 (or approximately 56%) representing a strong correlation (and therefore reliable).

There you have it! Try using a Scatterplot and Coefficient of Determination sometime when seeking the correlation of data sets. It’s easy and can reveal some valuable information.

Happy Thanksgiving All!

Excel Easter Eggs

First of all, I want to congratulate the winner of our “People who use Excel are Cool” Contest that was announced two weeks ago. Gordon Guthrie of Linlithgow, Scotland was the winner with his creation of an Excel clone that works as a native web application. Gordon’s fine work can be viewed using Firefox, Safari or Chrome browsers by accessing the following link:

http://hypernumbers.com

Well, it’s not exactly Easter, but I thought it might be fun to talk about the so-called Easter Eggs that have been hidden in Excel. Virtual Easter Eggs are hidden games or messages that are built into software by crafty developers who have a sense of humor and enjoy building in a bit of intrigue for the “Insiders” who wish to search for the cryptic content. The term was coined in the late 1970s at Atari by the renowned computer game designer, Warren Robinett. Since designers were not given credit for the games they created, Robinett included a hidden screen which said “Created by Warren Robinett”.

The Excel 97 version had a comparatively ambitious Flight Simulator hidden within the application. Using a rather simple combination of keyboard commands brought you to this remarkable simulator.

Although more difficult to access, Excel 2000 included a Car Racing Easter Egg which resembled Spy Hunter.

Excel 2003 included an Office Quiz featuring the Crabby Office Lady. If you still have this version and you are connected to the internet, you can access this egg by typing in “Tortured Soul” in the search box.

Although there are rumors to the contrary, there are no widely-known hidden gems in Excel 2007 or Excel 2010. The general consensus is that Easter Eggs have been eliminated from Excel due to potential security concerns. If you know of any eggs in these versions, please write to me at ExcelEnthusiast@gmail.com. I would love to share them with our merry group! Cheers!