Scatterplots and Correlations

For all of you fellow Statistics Fans out there, one of my Excel tools are the 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 brilliant (and soon to be more appreciated) Excel user can make a Scatterplot Chart and and then do the following:

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.  This will range from -1.00 to +1.00.  In the graph example above the COD value is .5574 (or approximately 56%) representing a strong correlation (and therefore reasonable credible).

That's it in a Nutshell!  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.  Just remember, Correlation Does Not Equal Causation...

Working with Excel on iPad

Since you are reading this blog, it is likely that you are a Technophile and also own (or are thinking about owning) an iPad. The iPad can coexist nicely with you other computing equipment, 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 proclaim distain for 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!

Easter Eggs

Well, it’s not exactly Easter, but it is always fun to reminisce 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, Easter Egg, was coined in the late 1970s at Atari by the renowned computer game designer, Warren Robinett. Since at that time designers were not given credit for the games they created, the clever Robinett included a hidden screen which said “Created by Warren Robinett”.

The Excel 97 version had a very 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 have been rumors to the contrary, there are no widely-known hidden gems (or germs, depending on your view…) in Excel 2007 or Excel 2010. The general consensus is that Easter Eggs have been eliminated from Excel due to potential security concerns. The inclusion of these eggs have also come to be considered Unprofessional (some people just don’t like to have any fun…)

If, however, you know of any eggs in these later Excel versions, please write to me at ExcelEnthusiast@gmail.com. I would love to share them with our merry group! Cheers!

Calculate Your Car Payments

I have to admit, I am a Function Junkie, and let’s say it is time to trash the Nerdmobile and buy a new car. Well, no self-respecting Excel Guru is going into a car dealer without his or her trusty laptop or Excel-compatible tablet loaded with the most recent version of Excel.

And why do I want to bring my computer you say, (besides just to look cool)? To Calculate the Monthly Car Payments, of course! Here is how:

The Excel PMT function can calculate the monthly repayment using the following format:

PMT(AnnualInterestRate/12, NumberOfPayments, AmountOfLoan)

For example, if your annual interest rate is 4%, on a 5-year (60 months) loan of $30,000, your formula would look like this:

=-PMT(0.04%,60,30000)

Note that a negative sign (-) was placed in front of the function in order to return a positive value (negative payment values just don’t look cool…)

With your formula in place, hit Enter and, Bam!, you have your monthly payment. If you don’t like the results, play around with the interest rate, the length of the loan, or maybe check out some cheaper cars.  How cool is that!

Welcome to the Advanced Excel Enthusiasts Blog!

Hello Excel Gurus!

Today marks the debut of the Excel Enthusiasts: Advanced blog! In the weeks and months ahead, this blog will explore intermediate and advanced Excel. Topics will range from highly useful ways of mining and displaying your data, to truly esoteric, wild and wonderful looks at what this powerful tool can do.

I hope you will check in now and then. You are also encouraged to join the 2,500+ member Excel Enthusiasts Group on LinkedIn and stay connected with other Excel experts!

All the best,

Bob (The Excel Guy)