The Case Against Maths

December 19th, 2014 by datapig 7 comments »

I didn't want to finish out the last full work week of the year without a blog entry.

So let me bring up something that has been bothering me for quite some time.

Take a look at these two statements, then point to the one that looks right to you.

.

If you're American, you probably chose the statement on the right. If you're English, I bet you chose the statement on the left. In researching articles for some of my statistics posts, I often encounter variations on how the abbreviated word for mathematics is used. American authors use the word math as in – "Do the Math". The English use the word maths as in – "Do the Maths".

.

As a proud overweight American, I find it odd to say "Do the Maths".

What's bothers me the most is the seemingly arbitrary plural designation given to the abbreviation for mathematics.

.

Here is the way I see it. Mathematics is not a pluralized word for Mathematic. You don't have 1 Mathematic and 5 Mathematics. You simply have Mathematics. In fact, the word Mathematics (the overarching name for the study of all things mathematical) is used solely in singular verb forms.

You would say "Mathematics is useful in business".

You would not say "Mathematics are useful in business".

.

Even the English agree.

They say "Maths is fun".

No one says "Maths are fun"

.

That's right, even though there is an S in Maths, it's not treated as a plural.

It's obvious that the English use the abbreviated term "maths" (with an S at the end) to account for the s in Mathematics. Ok, I see that. So the question now is why is there an S at the end of Mathematics in the first place? After all, we don't say Literatures or Musics.

.

Well, according to Wikipedia, The most probable reason is that the origin of the word Mathematics is the Greek word mathematika. In Greek, when a word ends in the letter A, that typically denotes that the word is plural. So when mathematika was being translated to English, the letter S was added to the end.

,

In short, the term Maths is a singular word that looks plural.

And that, my friends, gives me the heebee-jeebees.

.

Ahhh…it feels good to get that off my chest…finally.

Well, have a great weekend – and remember:

Office Update Breaks ActiveX Controls

December 11th, 2014 by datapig 5 comments »

I checked my email today and saw a bevy of emails from friends and clients claiming that their workbooks broke over-night. When one person contacts me saying something is broken, my general attitude is "they're on crack". But when I get a flood of emails, that's a horse of another color.

.

Apparently, a recent Office Update introduced security "fixes" that break any workbook or code involving ActiveX Controls. I have to admit, I didn't even notice it. I guess my workbooks are sorely lacking in ActiveX Controls.

.

The ever amazing Jan Karel Pieterse shows us how to fix the issue at Daily Dose of Excel.

.

His fix addresses the set of .exd files that prevent the addition or use of ActiveX Controls. These .exd files are added to your machine through the update.

The answer is to delete these .exd files (which are located under C:\users\[your name]\App Data\local\temp).

Here are the ones I found on my machine. I deleted the nasty buggers and my Excel is back to normal.

.

I bet it's a great day to be on the Microsoft Help Desk today.

.

Thanks Jan Karel for the fix!

 

 

Changing the Look of Embedded Objects

December 4th, 2014 by datapig 5 comments »

Most of us know that you can embed objects like Word Files, Outlook Messages, and even other Excel Files. Here's an example of a Word object I embedded in my spreadsheet. My customers can double-click on this object to open the Word document I've embedded. This is cool, but it sure does come out ugly. I'd like to make it more attractive.

.

.

Here are some steps I can take to make a better looking Embedded Object.

  1. Click Insert->Object to open the Object dialog box.
  2. In the Object dialog box, go the Create from File tab and click the Browse button to find the file I want embedded (I selected a document on the mating habits of koalas).
  3. Click the Display as Icon check box.


    .

  4. Click the Change icon button.
  5. In the Change Icon dialog box, select the icon that is just a blank space, and then clear the caption input so that no caption will be shown.

    .
    Don't see a blank icon option?

    You can click the Browse button on the Change Icon dialog box and choose any strange file type that doesn't have associated icons. My go-to file is the bootstat.dat file under the Windows directory (c:\windows\bootstat.dat). Again, you can pick any strange file type.
    .


    .
    .

  6. After pressing OK, I see an empty square. I can click on that square and go to the Format tab. On the Format tab, I click Shape Fill->Picture.


    .
    .

  7. I can select a picture I want to use instead of that lame Word icon. I've got a cool picture of a Koala. I choose that and press the Insert button.

    .

.

And voila! I've got a nice looking embedded object. When I double-click the object, my document on the mating habits of koalas opens.

.

Of course, you don't have to use pictures of over-sexed koalas.

You can use this technique to display your own application icons, info-graphics, or any other image that fits the theme of your report or dashboard. These embedded objects are great for distributing different kinds of files with your Excel workbook. You can use these to include things like help files, back-up documentation, change logs, etc.

.

New Comments Filter on Bacon Bits

December 3rd, 2014 by datapig 3 comments »

Over the last few days, I've been fighting an issue with the comments on this blog.

Apparently, the spam robots have been posting over 25 comments per second on my blog, causing time outs and overloads. I'll never understand why anyone would think this practice of shot gunning comments would be an effective way of driving revenue.

.

In any case, I had to do some back-end cleanup of my WordPress SQL database and install a new Captcha mechanism to hopefully curb the spam bots.

.

I typically don't like Captcha tools, but I found one that fits the irreverent style of this blog

If and when you enter a comment, you'll need to solve a small puzzle.

Simply look at the picture on the right and click the image on the left that best corresponds with the picture.

.

Here's to the end of my WordPress woes.

Happy commenting.

Happy Thanksgiving 2014

November 25th, 2014 by datapig 5 comments »

No posts this week, but here's a gift for you. I made a Thanksgiving GIF you can share with the kids. I used a bunch of Excel shapes to make a cozy Thanksgiving movie. Who says Excel doesn't appeal to kids?

.
thanksgiving
.

I'll be back next week. Enjoy your time off!

The Best Reviews on Amazon

November 21st, 2014 by datapig 1 comment »

Over at Amazon, you'll find the Samsung 105 inch 4K Ultra-HD Curved TV.

The price tag on this substantial purchase is $120,000.

.

If you're apprehensive about taking the leap on this purchase, rest assured.

It's getting some of the best reviews I've seen.

.

.

.

Check out all the reviews here.

.

.

 

 

 

 

Prevent Worksheet Delete without Workbook Protection

November 20th, 2014 by datapig 8 comments »

My friend Tim sent me a message yesterday asking:

"I would like to allow people to insert and re-order worksheets but not delete any. Is that possible?"

Searching all the forums, I found that this question comes up with some frequency. There seems to be a common need to prevent the deletion of a worksheet, but still give users the ability to change the structure of the workbook. Excel does have a Workbook Protection feature, but that feature doesn't give you many options when you protect a workbook. That is to say, you can't specify that you want to prevent one action, but not other actions. So you're stuck with either protecting the workbook structure or not.

.

The answer will have to come from VBA.

In my search, I discovered that Excel 2013 added a new Worksheet Event called BeforeDelete.


.

Wow. I hadn't noticed this event before.

Of course, the Microsoft help files are as useful as ever (sarcastic tone).

.

Judging by the utter lack of examples on how other folks are using this, I'm assuming this new addition to the Worksheet events has gone largely unnoticed by many of us.

.

Apparently, this event triggers when you attempt to delete any worksheet in your workbook. Unfortunately, this event does not come with a Cancel method. Meaning that when a user deletes a worksheet, this event triggers, but then Excel goes ahead and deletes the worksheet anyway. It seems that the purpose of this event is to do some action before the worksheet is delete – not give the developer an opportunity to cancel the delete. So with this event, you can do things like: log the time the worksheet was deleted, save the workbook before deleting the worksheet, send an email before worksheet is deleted, or anything else you can think of.

.

I decided to solve Tim's problem with this small bit of code in the Worksheet_BeforeDelete event. This code simply renames the worksheet, then creates a copy with the original name. So before the worksheet is deleted, you essentially create a copy of it. No matter how many times the user tries to delete the worksheet, it will always be there.

Private Sub Worksheet_BeforeDelete()

Dim MyName As String

'Capture the original worksheet name
MyName = ThisWorkbook.ActiveSheet.Name

'Rename the worksheet
ThisWorkbook.ActiveSheet.Name = Left(MyName, 30) + "#"

'Create a copy of the worksheet
ThisWorkbook.ActiveSheet.Copy _
After:=Sheets(ThisWorkbook.ActiveSheet.Index)

'Name the copy to the original name
ThisWorkbook.ActiveSheet.Name = MyName

End Sub

.

If you have Excel 2013, you can right-click on the worksheet you want protected, then select the View Code option. Then simply paste this code into the VBE. Note that you'll have to do this for every worksheet you don't want deleted.

.

For those of you with Excel 2010 or prior versions, you're basically out of luck. You don't have the BeforeDelete event available to you. In those version, you'd have to employ messy tricks to hide the Delete Commands from the interface. These require VBA and even some RibbonX customization that I'd rather not get into.

Update: Jan Karel Pieterse, observing that my technique will zap any formula pointing to the deleted worksheet, has provided an excellent solution that is far superior to the one I proposed here.

In a normal module, paste this code:

Sub UnprotectBook()
ThisWorkbook.Unprotect
End Sub

Then for every worksheet, right-click, select View Code, and then paste this:

Private Sub Worksheet_Deactivate()
ThisWorkbook.Protect , True
Application.OnTime Now, "UnprotectBook"
End Sub

And this works in any version of Excel! Thanks Jan Karel!

 

Feel free to comment and tell us if and how you're using the BeforeDelete event. Like I said, I've just now noticed it. I'd like to know how anyone else is using it.

Methods of Measuring the Skewness of Data

November 17th, 2014 by datapig 2 comments »

Skewness is essentially how data is clustered together within a dataset. Every dataset can have some gradient of three skew states: No Skew, Positive Skew and Negative Skew.

  • In some datasets, the values tend to cluster around the Mean (the Average). These datasets are said to have a Normal Distribution and no skew.
  • In some datasets, the values tend to cluster below the Mean, giving you a distribution with a "tail" that tapers toward higher values. These datasets are said to have a Positive Skew.
  • In some datasets, the values tend to cluster above the Mean, giving you a distribution with a "tail" that tapers toward Lower values. These datasets are said to have a Negative Skew.

.

Understanding the Skew of your dataset can help make sense of the statistics that come out of it. For example, if you knew that your data included extreme values that skew to the negative, it could help guide you in deciding whether you should anchor your analysis to the Average, Median, Inter-Quartile range, or some other key statistical value.

Today, I'd like to give you several quick methods for determining the skewness of data.

.

Method 1: Graph your Data

Visualizing your data can quickly show you how your data is skewed. Two excellent ways to see the skew of your data are to visualize your data with a Histogram chart or a Box Plot.

.

Histogram charts are essentially visual representations of the frequency distribution of your data. With a histogram chart, you can get a good sense of how your data is distributed. My good friend and charting guru Jon Peltier has several excellent tutorials on building histograms.

.

Box Plots are another useful way to visualize the distribution of data. These charts plot the Mean of your dataset then creates a "box" around the Mean using the Quartiles. This method essentially gives you a visual representation of the location of key statistical points. Using this visualization, you can quickly see where your data bunches up and where there are long tails. Again, Jon Peltier has an excellent post on how to create Box Plots in Excel.

.

Method 2: Calculate using the Mean Median and Mode

If you don't want to take the time to create a chart, you can use some of the simple statistical points to get a general sense of how the data skews.

Specifically, you can review the location relationship between the Mean, Median and Mode of your dataset. If you don't know:

  • Mean is the average of your dataset
  • Median is the central data value of your dataset
  • Mode is the value that has the highest frequency (occurs most often)

The idea is to use some simple comparison measures.

If the Mode, Median, and Mean are around the name number, your dataset has No Skew.

If the Mode is less than the Median and the Median is less than the Mean, your dataset has a Positive Skew.

If the Mode is greater than the Median and the Median is greater than the Mean, your dataset has a Negative Skew.

.

Method 3: Calculate using the Quartiles

Another easy way to mathematically get a sense of the skew is to compare the Quartiles in your dataset.

A quartile is essentially a division of data into four defined intervals based upon the values of the data and how they compare to the entire set. These intervals each contain 25% of the entire population. Values falling below 25% of all the data analyzed is said to be in the 1st quartile. Values falling between 25.1% and 50% are in the 2nd quartile. Values falling between 51% and 75% belong to the in the 3rd quartile. And the remaining values are allocated in the 4th quartile. Feel free to read my previous post on Quartiles  to get a detailed primer.
.

In a dataset that has No Skew, Quartile 3 minus Quartile 2 will be equal to Quartile 2 minus Quartile 1.

In a dataset that has Positive Skew, Quartile 3 minus Quartile 2 will be greater than Quartile 2 minus Quartile 1.

In a dataset that has Negative Skew, Quartile 3 minus Quartile 2 will be less than Quartile 2 minus Quartile 1.

.

Method 4: Use the standard Skew Calculation

You can also use the standard statistical calculation for Skew:

3*(MEAN – MEDIAN)/STANDARD DEVIATION

This calculation is used in statistics to get a quick glimpse at the Skewness of a dataset.

Just for reference:

  • Normally Distributed datasets have a Skew result of 0 (no skew).
  • Datasets with a Positive Skew result in a number greater than 0.
  • Datasets with a Negative Skew result in a number less than 0.

The closer the resulting Skew calculation is to 0, the more normal the distribution is.

Take the example below. You can see in cell Z21, 1.11 is the calculated Skew for the dataset in column W.

Since the Skew is greater than 0, this tells us that we have a Positively Skewed dataset.

The intensity of the Skew is determined by how far it is from 0. In this case, 1.11 tells us that our dataset has a fairly strong skew to the positive.

.

Method 5: Use Excel's SKEW function.

Some of you statistics nerds will point out that in the previous example, we calculated the Skew on only 11 data points. The standard statistical calculation seems to exaggerate the result of Skew when you're dealing with small sample sizes. A more robust measure of Skewness would take into account the number of observations. This is where Excel's SKEW function comes in.

.

Excel's SKEW function will give you a different answer from the statistical calculation shown in Method 4.

This is because Excel' Skew calculation accounts for the number of observations in the dataset.

Excel's SKEW function tells us that the dataset is indeed positively skewed (the result is greater than zero). But you'll notice that Excel tempers the intensity of the skew, showing a lower number than the standard statistical calculation (.08 vs 1.11).

.

Method 6: Use Excel's Descriptive Statistics Feature

You can use Excel's Descriptive Statistics feature to output key statistical data points from your dataset.

SKEW is one of the calculations that output.

If you're interested, here is a clean tutorial on Excel's Descriptive Statistics feature.


.

So which one of these methods are best? Well it depends on what you need to do and how much time you have. I would say that it's always best to visualize your dataset before working on it. I typically build a histogram of any dataset I have just to see the general makeup of the data. If you don't want to go through that rigmarole, and just want a general sense of the Skewness of your data, Excel's SKEW function is probably the easiest method.

.

I guess that's it then. I don't know about you, but all that math talk has made me hungry. I'm off to get to get a breakfast sandwich.

New Dashboard Templates from Chandoo

November 13th, 2014 by datapig 1 comment »

The ever creative Chandoo has just release a new offering in his treasure trove of Excel goodies. If you don't know Chandoo, he's an Excel MVP, father of twins, lover of tea with milk, and owner of one of the most popular Excel blogs today.

.

I call him can-do Chandoo. No matter what crazy idea you throw at him, he has a knack of making it work.

Need an example? How about an Excel Podcast?

A long time ago, I thought about doing an Excel podcast, but I figured talking about Excel would be too boring without seeing the tips and tricks. Yet, can-do Chandoo made it work. He's on his 25th episode and seems to be going strong. Like that brown kid on a boat with a tiger, he makes any challenge look easy.

.

Well, today Chandoo beats me at yet another idea I've been toying with for years: ready-to-use Dashboard Templates.

With his templates, you simply enter your data, set up few calculation options, decide how your dashboard should look, and poof – like magic, you have a dashboard that synthesizes your data into easy to consume insights.

.

I spent the better part of 3 hours dissecting his template and here are my thoughts:

  • Overall – very impressive. It does indeed do what you think it should do. I jammed some of my data into the dashboard, selected the view I wanted and it produced that view.
  • Having this template is like having an advanced mock-up tool. With this template, I can mock up all kinds of views for my clients. I may not use this as my final dashboard, but this template definitely saves loads of time putting together a straw-man to show to my clients.
  • No VBA! Given the very dynamic nature of the dashboard, it's hard to believe there is no VBA in it. Chandoo uses a whole host of tricks from Slicers to Named Ranges to keep the final dashboard VBA free. I think this is especially handy if your organization uses Office 365 or Excel Services on SharePoint. Why? You can publish your final dashboard to the web!

 

In fact, there are so many tricks used in this dashboard, you can learn all kinds of advanced techniques and best practices just by reverse-engineering the formulas and named ranges. I'll give you an example. In the set up page, you can define the number formatting for each of your metrics. This means you can change the format of a metric to (let's say) a percent with two decimal places. This immediately changes the dashboard to show the selected format.

My question was how the heck did he change the format based on a dropdown selection without VBA? The answer is he uses the TEXT Function to dynamically change formatting. This is a clever trick that I'll be using in my dashboard models. Small tricks like these make Chandoo's template worth more than the resulting dashboards themselves.

.

Ok. I'm starting to feel a little dirty.

Jeez - it sounds like I'm ready to take a shower with the guy.

.

The bottom line is that this new offering from Chandoo is pretty damned cool, no matter who you are.

Take a moment this week check it out.

Getting Rid of the Stubborn AutoFill Options Menu

November 6th, 2014 by datapig 4 comments »

Here's a bit of Excel trivia for you.

The other day, I was filling in a series of numbers (type in two numbers, select the two numbers, and then drag the fill handle down to fill a series), when I noticed something I hadn't paid attention to before. The Autofill Options menu (that icon you get when you fill a series) doesn't respond to any of the usual actions you would take to make something go away.

I pressed the Escape key.

It wouldn't go away.

I switched to another sheet and came back.

It wouldn't go away.

I pressed F2 and Esc.

I wouldn't go away.

.

Naturally, being the Excel nerd I am, I set out to experiment and list some of the actions that make this stubborn menu disappear.

  1. You can close your workbook then reopen it. Seriously? Not event an option.
  2. You can start typing anywhere in the sheet. This one seems obvious, but I don't like the idea of needing to type something just to make a menu disappear.
  3. You can zoom out then back in. I guess that's a bit better than typing, but it still seems like a waste. Why would I ever zoom to get rid of a menu?
  4. You can click in the formula bar. Easy enough. You still have to click in the formula bar then out again, but this option is better than zooming.
  5. The best option I've found is to click any of the lines between columns or rows. This is a quick one-click action. Simply hover your mouse over any of the lines separating column letters or row numbers, then click the mouse when your cursor turns into a cross.

.

It would be so much better if a simple press of the Escape key worked.

Anyway...time to do some real work.