Quick Stats

Statistics. As Mark Twain once said, there are “Lies, damned lies, and statistics”. Nonetheless, Statistics are integral parts of our everyday lives. We are bombarded by statistics in advertising, news stories, and the popular media. They are, frankly, Essential to our modern way of life.

As Excel users, we are much more likely to use statistics than the Average Joe. A very Quick and Easy way to obtain most of the common stats used in business is to go to the Data Ribbon in Excel and choose Data Analysis (on the far right of the ribbon).

You can then try this out by choosing Regression from the Data Analysis dropdown. Using the data shown above, put the Sales Data in the Input Y Range and the Month Data in the Input X Range, and choose a single cell for the Output Range.

The results will Instantly give you the following Regression Statistics:

• Multiple R
• R Square
• Adjusted R Square
• Standard Error

You will also get grouped ANOVA stats for Regression and Residual.

For those of us who live with statistics, this technique can be a boon for obtaining a Quick Summary of your Stats. 4 out of 5 Excel Gurus Recommend It!

Military Time

As anyone who has been in the service (or watched enough movies) knows, the military operates on the basis of a 24-hour clock, beginning at midnight (0000 hours). So, 1:00 AM is 0100 hours, 2:00 AM is 0200 hours, and so-on up until 11:00 PM which is 2300 hours.

Not only is it used in the military, this system is the most commonly used time notation in the world today, and is the international standard for noting time.

When it comes to Excel, some of us may be faced with translating Military Time to our typical U.S. Standard Time. This isn’t terribly difficult, but a couple of Cool Formulas really help out!

First of all, let’s assume you have Cell A1 populated with military time expressed in the typical 4 digits. Using the TIMEVALUE function, which very neatly converts time represented by a text string into the Decimal Number Excel can work with, we can use the following to perform our quick bit of magic:

=TIMEVALUE(LEFT(A1, 2) & “ : ” & RIGHT(A1,2)

What this formula does is to simply choose the left two digits and put a colon between them and the last two digits. Cool!

Another bit more sophisticated approach is to use an embedded TEXT function to return a formatted string, and then use TIMEVALUE as we did in the previous example:

=TIMEVALUE( TEXT(A1, “00\:00” ))

Whichever method you use, you will, of course, want to be sure and Format your result cell to Time. For instance the military notation of 2345 will result in the decimal, .98958 (approximately), which converts to 11:45 PM with the proper formatting.

You may not need to do this type of conversion soon, but when you do, You’ll Stand Out from the Rank and File!

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!