COD

COD – No, we are not talking about Cash On Delivery or Codfish, in this post we are going to look at the very powerful analysis tool of Coefficient Of Determination. One of my all-time favorite Excel Combinations is the threesome of:

 

    1)  An (XY) Scatterplot Chart
    2)  The Linear Trendline
    3)  The Coefficient of Determination

An (XY) 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.

A Linear Trendline is a best-fit straight line that is used with a majority of linear data sets.

 

The Correlation Of Data Function can be an extremely worthwhile endeavor for any business analysts, as it represents the strength and validity of your correlated data.

Before we go further, a word to the wise: The old adage, “Correlation does not Imply Causation” is as true today as it always has been, and continues to be a rebuttal to otherwise unscrupulous statisticians.

So, how can we use these powerful tools?  

Let’s say, for instance, that you want to do some speedy research into the efficacy of your sales advertising. Perhaps you want to know whether it is typically better to spend advertising dollars on Tuesday or Thursday.

First determine your ad effectiveness on Tuesdays by selecting data from those days and making a Scatterplot Chart.  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 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 example above, the COD value is .74 (or 74%) representing a strong correlation between spending advertising dollars on Tuesdays and increased sales.

Then simply do the same for your Thursday data, and compare the size of the CODs. If you get, for example, a Coefficient of Determination of 74% on Tuesday and 29% on Thursday, it is a strong indication that you should Spend your money on Tuesdays!

Try using the combination of a Scatterplot Chart, Linear Trendline, and a COD, and see how easy it is to do some very worthwhile analysis.

Whoa, What’s This?

As with so many things in Excel (and much of life…), it is easy to overlook some incredibly useful and convenient resources.  I say, look here Watson, the information is available right on our Status Bar! 

Most of you probably are probably aware of the real-time Status Bar display of common data such as Average, Count, and Sum for any cells you have selected.

However, what you may have overlooked (I know I did for a long time…) is that there an incredible collection of Excel Goodies right at your fingertips!  Simply Right-Click the Status Bar and Presto!  You will see a List of 26 pieces of Information that can be instantly accessed or controlled in this area.

 

For example, you can easily insert or delete the Number Count, Maximum or Minimum, Average, and so forth.  But don’t stop here!  You can also Control such handy goodies as Macro Recording, Zoom, Fixed Decimal, Signatures, and much more!

What makes this all So Cool is that it resides right there at the bottom of your worksheet and it continually available whenever you need it.  Take a look at your Status Bar and see what you might have been missing. Control at your fingertips, just waiting to be of service to you. Give it a try!

The Curious DATEDIF

The marvelous holiday of St. Patrick’s Day is an excellent time to revisit the Curious function of DATEDIF. For most of us, (Irish or not), Excel handles our typical numeric data in an intuitive manner. Dates, however, can be a bit worrisome at times.

If you have been working with Excel for some time, I am sure you know that the way Excel handles dates can be a bit “Curious” at times. Finding the Difference between two Dates, for instance, is not readily intuitive, so for this day of green, we are going to look at the totally cool DATEDIF function!

Curious Note
One small curiosity about DATEDIF is the fact that it is not a “documented” function in Excel. Not in Excel 2007, 2010, 2013, or 2016. You cannot, for example, go to the Insert Function wizard and find it in any of the lists.

The Syntax of the DATEDIF Function is Entirely Simple:

=DATEDIF(“First Date”, “Second Date”, “Time Interval”)

Where the Time Interval is expressed as follows (Important Note: Unless referring to cell values for the dates, all arguments must be enclosed in Quotation Marks):

“d” (Days) = Number of days between the dates
“m” (Months) = Complete calendar months between the dates
“y” (Years) = Complete calendar years between the dates

An interesting and rather fun way to apply this function is to nest the TODAY() function into it and calculate a Person’s Age. The TODAY() function, of course, returns the Current Date, and when used with DATEDIF, it can produce an Excel calculator that you may find, well, curiously amusing.

=DATEDIF(BirthDate, TODAY(), “y”)

Another interesting way to use DATEDIF and TODAY is to make a dynamic Day Number Calculator. The elapsed number of days in the current year can be determined in a Single Cell with the formula:

=DATEDIF(“1/1/2014”, TODAY(), “d”)

By keeping this little hidden Green Gem in mind, you may find great many ways to use the DATEDIF function in the future. It really is Curious that it isn’t formally documented. Happy St. Patrick’s Day, All!

Funnel Charts for 2016

One of the latest additions to Excel 2016 is the ability to create Funnel Charts. This worthy new chart is excellent for illustrating processes that incrementally become narrowed or more focused as each stage is completed. In the illustration below, I am showing how business “Funnels” into an insurance company.

It should be noted, of course, that this feature is only available if you have an Office 365 subscription and you have updated it recently.

 

Here are the Simple Steps for Inserting a Funnel Chart:

1.   Set up your data similar to the above illustration, using one column for the process steps and the other for the amounts
2.   Select you data in the table you have created
3.   Click Insert > Recommended Charts > Funnel

That’s all there is to it!  I am confident that this new chart will be useful for a great many of us in the future.  Funnel Charts – Very Cool!

IFS

For Office 365 users, there are new features and functions are being added to Excel on a regular basis.  Some of them tend to be a bit esoteric, so unless you are part of a special group, you may not find much interest in the more obscure items.

On the other hand, some of the new functions can have a broad appeal to many Excel Enthusiasts!  One such feature is the new IFS function.

If you are like many Excel users, you have had experience with Nested IF Functions.  This is a powerful (albeit a bit cumbersome) way to extract information from your data subject to multiple conditions.  Happily, the new IFS function can help simplify your life in this regard, as allows you to specify a series of conditions in a single function!

Instead of being tasked with stringing multiple IF functions into a mega-function, (which can be confusing to others who might inherit your work), you can achieve the same results using this valuable new tool.

Using the classic grading system of assignments for an illustration, (which we are all familiar with from our school days…), let’s say that you have a student’s numerical grade in Cell A1.  Using our cool new function, we can construct a formula in Cell B1 as follows:

=IFS(A1>=90, “A”, A1>=80, “B”, A1>= 70, “C”, A1>=60, “D”, A1<60 b="" style="mso-bidi-font-weight: normal;">Let’s try this again…”)

If the student’s score in A1 is 82, then (this is quite clear cut…) the result in B1 will be B.  This really is a worthy little enhancement to Excel that can save you time, and make your formulas easier understood by others as well as yourself.  I hope you can give it a try sometime, and see if you agree. 

Cheers!