Standard Deviation Revisited

It is interesting to note how mysterious Standard Deviation is to most people.  Not everyone is a math geek, of course, but with a little effort, understanding Standard Deviation and its possible applications can be a true boon to a great many business people.  It is such a useful measure that I feel it is worth examining as we did a little over 2 years ago.

So, what is Standard Deviation?  In basic terms, it is a measure of how widely values are dispersed from the average value.  Depending on your business, it is potentially a very important statistic.

My favorite example as to how this measure can be applied is in the case of Call Centers.  Managers and analysts of call centers routinely track the amount of time it takes for their customer service people to handle the incoming calls.  Other measures of quality are, of course, also very important, but the Average amount of time taken per call is obviously very central to the effective operation of nearly any center.

So let’s suppose that you are such a manager or analyst, and you track weekly results of your department.  Let’s also say your records show that the representatives can provide your customers good service in a Mean Average of 6-8 minutes.  So, what would better to see, a range of actual talk times of 1-65 minutes or a range of 4-10 minutes?  (This is an example of the relative dispersal that Standard Deviation measures.)

Quite clearly, you would prefer to see Smaller values versus Larger values (tighter grouping around the average times), as it would indicate a more uniform, trustworthy approach by your reps.

So how can you use Standard Deviation in Excel? If you are still using Excel 2007 (or an earlier version), using the old STDEVP may be your preferred function. This assumes that you are using the Entire Population of data (therefore, the “P”). If you are using just a Sample of your data, then you will want to use STDEV.

This was made more intuitive in Excel 2010 and Excel 2013, where the function for finding standard deviation for the Population is STDEV.P and the Sample is STDEV.S. (A worthy clarification on the part of Microsoft...)

To make this data more visually accessible, this type of information can be more Effectively Illustrated with a Line Chart or a Bar Chart. In the blink of an eye, you can then see whether your data (talk times in our example) are under control in this regard.

The call center example is, of course, merely one of the virtually countless ways of using Standard Deviation in your Excel reports.  It is just another way that Excel can improve our business lives.  Give it a try some time and find out how simple it is to use this metric to further understand your data.

Excel 2016 Available Today!

It has just been announced that Microsoft Office 2016 (including Excel 2016, of course) has now been released.  It is available as a one-time download or you can subscribe to Office 365 for a monthly charge and regular updates.  I know I’ll be downloading it later this afternoon (what Fun!).

So, what is New in Excel 2016?  Well, many things, actually.  Here are a few of my personal favorites:

  

Enhanced Pivot Tables

Pivot Tables are, as most of us know, hugely powerful and convenient tools that enable quick analysis in minutes that may otherwise take hours.  Although the last iterations brought Power Pivot, the 2016 version has introduced the following cool new features (plus more…):

•   Automatic Relationship Detection and Time Grouping
•   PivotChart Drill-Down Buttons
•   Searchability

More Sophisticated Forecasting

The FORECAST function in the 2016 version of Excel now includes the option for doing Exponential Smoothing.  Having extensively used smoothing in the past, I can attest to the desirability of this new addition!

  

New Chart Types

Being a big fan of visual representation of information, I naturally was delighted to see the addition of the new:

• Treemap
• Sunburst
• Histogram
• Box & Whisker
•  Waterfall

3D Maps

Speaking of “visual representation”, Power Map has renamed to 3D Map, and is innately available to all Excel users.  As with its predecessor, the 3D Map tool provides an excellent way for making discoveries in data that might never be noticed in traditional two dimensional maps.

There are, of course, many other new and worthwhile features in Excel 2016.  Get your copy and find out which ones are your favorites!

Database Best Practices Revisited

There are a few Excel Best Practices that separate accomplished Excel Gurus from the masses of business folk who struggle unnecessarily with spreadsheets.  It is, consequently, a good idea to occasionally review these gems.

The design, construction, and information-mining of Good Databases are the keystones of knowledge for any advanced Excel user.  It is, therefore, very important to follow some easily applied Database Best Practices.  Although, depending on your needs, there may be other worthwhile techniques, I have broken out the following Six Practices that will serve you well.

1.  Data on One Worksheet; Information on Another Worksheet
Whenever possible, put all of your Data in one worksheet, and your Reports in another worksheet. The fewer the worksheets you have, the easier it will be for your users to navigate and garner the information they are seeking.  It also looks much more professional.

2.  No Blank Rows or Columns
Avoid blank rows and columns in your data table. The formulas you create on a separate Report Page will have more integrity.

3.  Deconstruct Your Data 
Always try to divide your data down to its minimum components. You will be able to use more powerful functions and search your data much more effectively. For example, if you have a database of employees, create separate fields for the first, middle, and last names (you can always easily combine them later if you wish).

4.  Sort Your Data 
This is a good habit to adopt, and certain Lookup functions will work only if your data being sorted in a logical order.

5.  Columns are for Fields
Excel obviously has far fewer columns than rows, so keep things simple by using the columns for the fields and the rows for the individual records.

6.  Everything in Its Place

Make sure the data is entered in the proper field. If the data entry person (maybe you) cannot find the right place for a piece of data, perhaps the database needs some redesigning.

If you adopt these practices, you will be well on your way to being the Excel Guru everyone relies upon. Organization = Simplification = Information.