Standard Deviation

When it comes to common statistical functions, Standard Deviation is perhaps the most overlooked and underused. This has always struck me as being unfortunate, since with very little imagination, it can be easily used and adopted to a wide range of everyday business purposes.

For those of us who have not been in a math class in a while, Standard Deviation is simply, (and I do mean, “Simply”), is a measure of how widely values are dispersed from the average value.

For instance, let’s say that you oversee a Call Center and you would like to use Excel to monitor a new metric regarding talk times. Suppose that you already track weekly results and know that your company’s representatives can provide your customers good service in a Mean Average of 6-8 minutes. When it comes to Standard Deviation of your Week-over-Week monitoring, you would undoubtedly prefer to see Smaller values versus Larger values (tighter grouping around the average times).

So how can you use Excel to track this useful statistic? If you are still using Excel 2003 or Excel 2007, using the old STDEVP is your Go-To function. This assumes that you are using the Entire Population of data (ergo, 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. (Hooray for Microsoft for continuing to make these sorts of refinements.)

Rather obviously, this type of information is Most Effectively Illustrated quite effortlessly with a Line Chart or a Bar Chart. You can then see at a glance whether talk times are under control in this regard.

This is, of course, a mere example of the virtually countless ways of using Standard Deviation in your Excel reports. Just another way that Excel can improve our business lives (which, as Martha Stewart would say, “Is a Good Thing”.)

Calculating Workdays

Do you ever find yourself Counting Workdays until your next vacation, holiday, or other event? If you are like my sister who is retiring in a few weeks, the answer may be a resounding “Yes!”

If you have a set of dates in Excel and you want to calculate the number of Business Days, (excluding weekends and holidays), you can easily do this using the NETWORKDAYS function. The syntax is as follows:

=NETWORKDAYS(StartDate, EndDate, ListOfHolidays)

If you are working with an older version of Excel (prior to Excel 2007), you should note that the NETWORKDAYS was available only after you installed the Analysis ToolPak add-in.

Also, starting with Excel version 2010, if you wish to calculate the workdays using something other than the standard weekend days of Saturday and Sunday, you can use the new NETWORKDAYS.INTL. With this new function, you can customize your weekend days as simply as your holidays.

But let’s suppose you have a start date for Project and you know how many workdays it will (theoretically, at least) take to complete it. How do you calculate the Date that it will be done? Here is where the WORKDAY function comes into play. Using the following syntax, you can easily calculate When the project will be complete:

WORKDAY(StartDate, #OfDays, ListOfHolidays)

If you wish to ascertain that the “StartDate” is interpreted correctly by Excel, (this is a recommended Best Practice), you can use the DATE function in its place, and alleviate any concern in this regard.

Calculating Workdays: As with most anything we encounter, when you use the right tools, it’s like being on vacation…

Readers’ Request: Easter Eggs

I find it interesting that one of the Most Popular Excel subjects among readers of this blog is Easter Eggs. As any of you who have been with me for a while knows, I addressed this a little over a year ago, and I regularly get requests to update this topic.

Virtual Easter Eggs are, of course, hidden games or messages that have been built into various software by cunning developers who have a sense of humor (utterly lacking at Microsoft these days) and enjoy building in a bit of intriguing fun into Excel. In years gone by, users who were “In-the-know” felt smug knowing how to reach the cryptic, and often entertaining, secret content.

The term “Easter Eggs” is attributed to one of the founding fathers of computer games, Warren Robinett. While working for Atari in the late 1970s, Robinett created a hidden screen which read, “Created by Warren Robinett”. Back then it was not uncommon for game designers to be given little credit for their work, so he probably felt his small ruse was justified (I agree…).

“Old-timers” of Excel will likely remember that Excel 97 had an ambitious Flight Simulator hidden within the application (it was pretty cool!). Using a simple combination of keyboard commands brought you to this remarkable (considering the era) simulator game.

Although a good deal more difficult to access, Excel 2000 included a Car Racing Easter Egg which resembled the classic Spy Hunter game (which, if you are interested in the “oldies”, can still be found online).

Excel 2003 included an Office Quiz featuring the Crabby Office Lady (remember her?). If you still have a copy of this version, you can access this egg by typing in “Tortured Soul” in the search box.

Although there have been occasional rumors to the contrary, I don’t believe there are any more hidden gems in the Excel versions after 2003. In fact, it has been reported that Microsoft will fire any employee who is caught inserting an Easter Egg into any of their applications.

That’s a pity, in my opinion. Sure, I fully understand the potential for security problems, and the inherent lack of professionalism in playing around with Easter Eggs, but I also find the days of Easter Eggs to be a charming time in software development. An age of innocence, if you will. Ah well, here’s a nod to the good old days…

Back to Basics!

Basics. I have heard many supposed Excel Experts scoff at the idea of revisiting the topic of Basics. The fact is, however, as with so many other disciplines, an occasional review of some of the fundamentals is inevitably a worthwhile Tune-up.

Navigating your worksheets, for instance, can be Drag unless you know a few simple tricks. For instance, if you wish to go to your last entry at the bottom of a list that contains 30 records, scrolling to where you wish to go is a Breeze! When you have a list containing 30,000 records, however, it is a bit tedious.

As is true with so many Slick Moves in Excel, keyboard shortcuts rule when it comes to saving time moving from one location to another on your spreadsheet.

Here are few Slick Moves you can make without ever touching a mouse:

1. Control / Down Arrow: Goes to last cell in column with data

2. Control / Right Arrow: Goes to last cell in row with data

3. Control / End: Goes to last row, column and cell

4. Control / Home: Returns to cell A1

When using your mouse, another way to navigate to the end of your data (whether in a column or row) is to carefully place your pointer on the adjacent border of cell in your range and double-click. For instance, if you wish to navigate to the last cell in a column of data that starts with cell B1, you can select B1 and double-click on the bottom border of the cell.

Want one additional way to navigate when you know the exact address of some remote cell? Simply enter the address (e.g. ET30000) in the Name Box and Zap: you have beamed directly to that location (without the help of Scotty...).

And how about Entering Repetitive Data?

While it is very intuitive to copy and paste Repetitive data to a set of noncontiguous cells (copy data, select the cells and paste), entering Newly Typed data doesn’t work quite as slick (I admit, I like “Slick” moves…)

To enter Repetitive New Data into a series of noncontiguous cells, simply hold down the Ctrl key and select all of the cells into which you want to enter your new data. Then type the text you want to enter and (a little drum roll, please…) press Ctrl+Enter. Bamm! Your Data is Entered!

Basics. Excel has a multitude of easily-mastered Slick Tricks that can save you time, and make you look good at the same time. It pays to revisit them once-in-a-while…

Imaginary Numbers

As they used to say on the old Monty Python Flying Circus show, “And Now for Something Completely Different”!

Imaginary Numbers are not something that most of us run into, and only a small percentage of us will ever find a use for them in our jobs. They do come up as a topic in most Math Curriculums, however.

For those of us who have not had a math class in a long while, (or if you were not a Math Geek like me), an Imaginary Number is a number that can be written as a real number multiplied by the imaginary unit i. The Square of i is -1. An imaginary number has a negative or zero square. For example, 5i is an imaginary number, and its square is -25.

In terms of imaginary numbers, the Square Root of -4 is 2i.

Weird stuff, I know. Real-World applications can be found in Engineering and Scientific fields. They are nothing terribly new, as they were conceived as far back as the ancient Greeks (by a smart guy be the name of Heron of Alexandria).

Imaginary Numbers in Microsoft Excel

Interestingly enough, Excel can handle these quirks of the math world. It even has a couple of specially designed functions to deal with Imaginary and Complex (expressed in the form a + bi, where a and b are real numbers and i is the imaginary unit, where i2 = −1).

The Imaginary function operates with the syntax:
   =IMAGINARY(inumber)

The Complex function uses this syntax:
   =COMPLEX(realnumber, inumber, [syntax])

There are also several innate Excel tools for using imaginary numbers with your typical arithmetic functions of addition, multiplication, etc.

I suspect that I know what you may be thinking, “What does this have to do with me?”. Well, I wouldn’t be surprised if your son or daughter encounters Imaginary Numbers in school some time, and wouldn’t it Rock if you could show them how to do this in Excel?

He or she might even think you are pretty smart. Imagine that!

FORECAST: Sunny Skies!

Forecasting can be a tricky business (just ask anyone whose job it is to predict the weather). The problem with coming up with an accurate Forecast is the fact that you are dealing with Historical Data which doesn’t necessarily correlate with the future.

Over time, however, much data is essentially Linear and can, therefore, be used to predict reasonably viable future outcomes.

The good news, of course, is that when calculating Forecasts using historical data in Excel, our favorite spreadsheet program has a Built-in Function for this purpose. The FORECAST function has the following syntax:

=FORECAST(X, Known_Y's, Known_X's)

X is the data point for which you want to predict a value.
Known_Y's is the dependent range of data.
Known_X's is the independent range of data.

Stated in Easier-to-Understand English:

=FORECAST(ValueToForecast, RangeY, RangeX)

ValueToForecast is the point in the future which you need to forecast.
RangeY is the list of values which contain the Historical Data to be used as the basis of the forecast, (Sales Figures are classic…).
RangeX are the intervals used when recording the Historical Data. Months, for instance (Just Be Sure to express the Months as a Number!)

Let’s say that you wish to forecast the Sales for October-December using the Example below. Here is what you should do:

1. In B11 put:
=FORECAST(A11,$B$2:B10,$A$2:A10)

2. Copy/Drag the above formula to B12:B13

Notes:
1. Many Excel users find this Slightly Counterintuitive, so you may wish to spend an extra minute examining the syntax example.
2. Remember that the underlying assumption for your Forecast is Linear Data. If your data does not fit this model, you may wish to explore other avenues.

FORECAST – Give it a try. You may find it says, Sunny Skies Ahead!

SUMIFS (Not Just SUMIF)!

As you may know by now, I am a big proponent of using Interactive Reports. Reports of this nature can be made with a variety of innate Excel tools, including DFunctions, Sumproduct, Vlookup, etc.

There is one Typically Overlooked function for making interactive reports, however. The SUMIFS, (Note the plural), is a surprisingly powerful tool for this purpose.

First of all, the Syntax for SUMIFS:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

Unlike its close cousin, SUMIF, the SUMIFS function allows you to conditionally Sum values with Multiple Conditions, rather than just one.

For instance, let’s say you have a table/database that has multiple entries of Sales for Months and Weeks. Using a SUMIFS formula, along with dropdown boxes for the Month and Week, you can make an Interactive Report that is contingent on the parameters you choose.

Bottom Line: If you have ever used the Little Cousin, SUMIF, and wanted more flexibility, you owe it to yourself to try out the vastly more powerful SUMIFS.

As I have said before, Interactive Reports – They are so easy, They'll Make You Smile!