Everyone! We've moved to WordPress.

Consulting Services

1
You may have noticed that I tend to update this blog less frequently than some of the more prominent Excel and VBA bloggers. The reason is that I blog on the side rather than full time. In fact, I work for a wonderful startup consulting company called The Perduco Group. Here’s what we’re about:

Perduco is Latin for "to lead through" or "leadership.” The Perduco Group leverages a number of key competencies in data structures, programming, operations research, and business intelligence to provide an overall analytical solution to the customer − delivering performance from data to decision. The fundamental goal is to provide a useable product founded in credible analytics to support organizations and change the way by which they do business. The Perduco Group is focused on providing organizations value by leveraging our technical skills and industrial and defense experience with our client-focused, results-oriented delivery approach.

If you’re interested in taking your spreadsheets to the next level, optimizing your efficiency with operations research, or implementing a new BI system drop me line at my work email, Jordan.Goldmeier (@) ThePerducoGroup (.com) and I can let you know what we’re all about.

As a follow-up to the above, I am gathering resumes on folks with experience in Excel, VBA, data analysis, and data visualization. If you’re interested in this type of work, feel free to send me your resume either at the email above or my personal one at the bottom of this page. The only condition is that you must be a US citizen.

Turn Off Cell Background Error Checking with VBA

3
When I'm all done with my Excel application, I'll usually want to flip off background error checking - that is, I'll tell Excel to stop showing those little green triangles that appear in cells. Don't get me wrong, those little green alerts can be useful - but they are rarely so in a finished product where I know my layout and formulas are correct. I just want to tell Excel, "thanks for the help, but stop annoying me already!" Indeed, these green alerts appeared on other computer screens when users opened my Periodic Table of elements file, which I found annoying.

The problem is that while I can tell those green triangles to go away on my instance Excel by going into Excel Options (or simply by clicking "ignore"), that won't fix the problem when my file is loaded onto other computers. The way around this is some VBA and the workbook open and close events. 

So, in my ThisWorkbook object in the VBA window, I wrote this:

Option Explicit

Private Sub Workbook_Open()
    Application.ErrorCheckingOptions.BackgroundChecking = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ErrorCheckingOptions.BackgroundChecking = True
End Sub

There are actually several types of background error checking that go on in a workbook. Excel allows you to disable these separate error checks by themselves if don't want to disable everything. Specifically, you can modify background checking options for empty cell references, error calculations, inconsistent formulas, and omitted cells, among others. For example, you might simply write:

Application.ErrorCheckingOptions.OmittedCells = False

if you only want Excel to stop monitoring for formula patterns that appear to omit cells that Excel thinks should be included in the formula. This will work so long as BackgroundChecking is still True. If you set BackgroundChecking to False like in the example above, Excel will cease all attempts to second guess your work (which can make your life easier, sometimes). 

To read more, see:

Newspapers - Read Your Sources!

1
Update - Apologies for the bad proofreading job in the original blog post. Before writing this, I had spent the entire day writing a book proposal for a technical publisher. I was a bit burned-out.

It's an exciting time for the United States. In the forthcoming days (possibly tomorrow), the Supreme Court of the United States will issue a ruling on whether all or part of the Affordable Care Act is constitutional. Surrounding the hype is an attempt by the media to figure out--that is, predict--the court's ruling before it's released this week. One factoid passed around is that a group of former Supreme Court law clerks and attorneys surveyed after arguments a few months ago suggest that the law will be overturned.

In an article by Business Insider, author Brett LoGiurato writes:

Most former Supreme Court attorneys and clerks believe that the individual mandate to buy health insurance — the signature provision of President Barack Obama's Affordable Care Act — will soon be struck down as unconstitutional by the high court.

That's according to a joint poll by the American Action Forum, Center Forward and Purple Strategies. An astounding 57 percent of clerks and attorneys polled now think that the mandate will be ruled unconstitutional. That's up from just 35 percent in March, before the court held oral arguments on the case.

Read more: http://www.businessinsider.com/obamacare-unconstitutional-supreme-court-barack-obama-health-care-2012-6#ixzz1yko2eVxa

The pic below appears in the actual Business Insider article and is a snapshot from the actual survey.


I'm not sure how Business Insider misunderstood the study, especially because a picture of it (the very same picture above) appears in their article. Clearly the survey does not say that 57 percent of clerks and attorneys think the mandate will be ruled unconstitutional. Rather, it says the average probability given by all the respondents is 57 percent. But can we say that "most" of the survey respondents believe that the mandate will likely be struck down? No, that's wrong, too. We really don't have enough information to draw that conclusion. More on that point in a minute.

This article, by the Washington Post, gets it wrong too:

A new poll of 56 former Supreme Court clerks finds that 57 percent think the individual mandate will be overturned. That’s a 22-point jump from the last time the same group of clerks was surveyed, right before oral arguments. Back then, 35 percent thought the court would toss out the required purchase of health insurance.

Ezra Klien (who loves charts) gets it wrong.

Here, the Wall Street Journal's Joseph Rago, too.

Did anyone get it right? Yes. Laura Green, of the Palm Beach Post, writes:

A poll of former Supreme Court clerks and lawyers who have argued before the justices found that many switched their initial prediction that the court would uphold the sweeping law. The oral argument performance led them to now speculate that there is a 57 percent chance the court will strike down the heart of the law, which requires virtually every American to buy insurance.

Read more: http://www.wptv.com/dpp/news/national/health-care-act-studies-of-oral-arguments-predict-court-will-rule-against-reform-this-week#ixzz1yksclk3m

What can we say about the survey's results?

Not a ton. I question the usefulness of engaging folks on the probability that a dependent, nonrandom event will occur. When the poll's respondents came up with their "probabilities," they were really giving a score to how confident they are in their belief that the Supreme Court will strike down the mandate. Calling this a "probability" is misleading.

The average is above 50%--can't we say that most believe the mandate will be struck down? 

Not really. Consider this scenario. Of the 56 respondents, nine said the probability of the Supreme Court overturning the mandate was 100%; that's a rather slim, if certain, margin. Now consider that the rest -- 47 respondents -- giving the probability of the court overturning the mandate a 49%. In other words, the 47 feel the likelihood is slightly less certain than flipping a coin. Do the math; you'll see that it averages to about 57%. Do those nine respondents in this example constitute a "most believe"?

Survey Source:
Affordable Care Act Survey, June 2012
Sponsored by: American Action Forum, Center Forward, and Purple Strategies



Interactive Periodic Table of Elements in Excel

46
I've been on a real rollover kick lately. I'm really trying to figure out if it can be useful. Earlier today I started making a Periodic Table of Elements using Excel. I employed the rollover technique to allow the user to gain information about an element simply by rolling over a cell. Well, for some reason, I couldn't stop there. So what was meant to be a small project ballooned into something larger. Unfortunately, my sticking to good coding practice didn't keep up with craving to do more. So what I present to you below isn't really a polished product. If you poke through the named ranges and the rollover indexes, you'll probably see that I add and subtract one to them somewhat randomly (a cheap trick - this is  due to my trying to reconcile the table copied from Wikipedia with my indices).

As you can see below, you can not only gain information about an element but you can also toggle on and off different element groupings.

Unable to display content. Adobe Flash is required.

If you want to "crack" the file, the first thing you'll need to do is reset the ScrollArea (Click on a cell. Go to the Developer tab, click Properties. Delete the reference in the ScrollArea box.). Then just unhide everything.

Good luck.
Periodic Table.xlsm

Update -
Reader Dario found an error in the spreadsheet (see the comments) - this is the result of some carelessness and cheap tricks on my part. An updated version will be released tonight. In the meantime however, you can still poke around the file :).

Another Update -
I've since fixed the bug described in the reader comments. If you find anything else, let me know!

How to: highlighting cells using the rollover technique in Excel

8
In my last post I used my patented rollover technique to create an effect similar to the one shown below:

Unable to display content. Adobe Flash is required.

Neat, huh? When you place your mouse over a cell, it changes color to show that you are selecting it. It's a true "rollover": no mouse-clicks required. In my last post, I show how that might be useful.

Wondering how I did it? Checkout this file:
Rollover Surprise.xlsm