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!

Working with Excel on iPad

Since you are reading this blog on a Kindle, it is likely that you also own (or are thinking about owning) an iPad. Aside from all of the clamor from advocates and naysayers, the iPad can coexist nicely with a Kindle, and can provide an alternative for working on Excel rather than being tied to a full-blown desktop or laptop computer.

There is no doubt that not all Excel’s features are available when working on an iPad. For a great many common tasks, however, it is more than sufficient, and there is something very positive about being flopped on a sofa and still having access to your favorite software. Not only that, but as the software makers further refine and create new applications that can handle spreadsheets, the possibilities continue to grow.

Applications

There are a growing number of applications that the Excel Enthusiast / iPad Owner can use. My favorites are Quickoffice’s Quicksheet and Apple’s Numbers. Although DocsToGo is a worthy contender, most users (in my humble opinion) will find the features and user interface more pleasing with the other two apps. I have been using all three applications since shortly after the iPad’s debut and I find I seldom use DocsToGo for anything other than PowerPoint.

Compatibility

While either Quicksheet or Numbers can handle a great variety of formulas, creating Charts in Apple’s Numbers is a real treat. Both of these applications can import and export in Excel format. This is of keen importance, of course, as what good is a spreadsheet if you can’t export it back to Excel.

Navigating/Viewing

Although it may be a bit foreign at first, tapping to select cells and using the convenient selection handles to choose a range becomes second-nature quite quickly. The now commonly known pinching gestures zoom you in or out on your data and charts, and it is easy to get hooked on these new ways of getting around a spreadsheet.

Sharing

Once you have created your spreadsheet masterpiece on your iPad tool, you can easily email it in its original Apple format, a PDF or, of course, as an Excel document.

Although some will deride this new way of interfacing with your data, I firmly believe that if you give it a chance, you will find that it makes a pleasant and productive alternative way of working with your Excel creations. Cheers!

Contest!

People who use Excel are Cool! They have used Excel for notonly ingenious ways to crunch data and design stunning charts, but also suchthings as tracking weight loss and designing quilts.

They have used Excel in so many unique and interesting ways that I thought it would be fun to hold a contest for the Kindle Excel Enthusiasts subscribers. I am very sure many of you have used Excel in ways that be of great interest toother Excel Enthusiasts.

So, here are the simple rules:

1. Send an email to excelenthusiast@gmail.com

with a brief description of the cool and/or innovative way you have used Excel.

2. Attach an example if you wish, and please keep the attachment to less than 3mb.

3. Get your entry in by midnight November 15.

The winner will receive a new 8GB Kingston Datatraveler flash drive via U.S. mail the following week, and will have their name and Excel creation announced in the following week’s blog along with any honorable mentions.

Please let me hear from you; it will be Fun!

Four Fun Ways to Liven Up Your Excel

Here are Four Fun Ways to spiff up your Excel workbooks and worksheets (sure to amaze your colleagues!).

1. Change the Color of Your Sheet Tabs

Right click on your worksheet and select “Tab color” option to change the worksheet tab color.

2.  Insert a Quick Organization Chart

Clickon the Insert tab on the toolbar and go to SmartArt and choose the “Hierarchy”group.  Pick the Org Chart that suits your fancy.

3.  Hide the Grid Lines on Your Worksheets

Do away with clutter by going to the View tab on the toolbar and Deselecting the box next to Gridlines.

4.  Add a Rounded Border to Your Charts

Simply right-click on your chart, select Format Chart option, and choose “Rounded Borders”.

There you have it!  Four really Fun Ways you can liven up your Excel workbooks and look Very Cool doing it!

This Week's Post

Greetings Excel Enthusiasts!

Just a note to say that some of you may find this week's post on the Frequency Function a bit intimidating, but hang in there, its worth a try!

I am also serious about lending a hand on a real-life project if you need any assistance.


All the best,

Bob

rdelamartre@gmail.com

Using the Frequency Function

Whether you are in the corporate or academic world, there are times when you want to know investigate how frequent values within particular ranges occur. An excellent solution in Excel is the Frequency Function.

This unique tool works as an Array function that counts the number of values that occur in each specified interval for a given set of values and a given set of intervals (or “Bins”, as they are often referred).

The FREQUENCY Function syntax is as follows:

 FREQUENCY(data_array, bins_array)

The function is entered as an Array formula after you select a range of adjacent cells (C2:C5 in our example) into which you want the distribution to appear. After you select the data and bins arrays, press CONTROL+SHIFT+ENTER.

Please Note: The number of elements in the returned array is one more than the number of elements in bins array. The extra element in the returned array returns the count of any values above the highest interval.

Example:

Formula Description and Result

=FREQUENCY(A2:A10,B2:B4)

1. Number of scores less than or equal to 75 (4)
2. Number of scores in the bin 76-84 (2)
3. Number of scores in the bin 85-94 (1)
4. Number of scores greater than or equal to 95 (2)

The Frequency Function can be a great boon to you regardless of the type of industry you work in.

Important Reminder: Be sure to Select your Entire Range (once again, C2:C5 in our example) where you want your results, and then enter the formula with the Array Keyboard Combination of CONTROL+SHIFT+ENTER.

Please send me an Email at rdelamartre@gmail.com if you have any problems setting up your table.

Happy Excelling All!