Perfectionism, and an Excel bug

Welcome, and thanks for stopping by! Subscribe to my RSS feed so you don't miss a single exciting post (or any of my regular ones). Thanks for visiting!

Perfectionism can be a killer

My last post was July 29? Oh, that’s not good. Not good at all. What happened?

Well, it goes back to the post about the necessity of using a graph wherein I promised to show a nifty way to generate simple bar charts on the fly in Excel. The problem is, I think I discovered a bug in Excel. I was stymied at just how I wanted to approach this, and it derailed the whole blog.

Oops. Sorry.

The Excel REPT bug

Let me say at the start that I’ve only seen this behavior in my copy of Excel, which is v. X for Mac. I can’t speak to other versions–which was part of the problem. I wanted to thoroughly address the issue. But it was becoming abundantly clear I wouldn’t be doing a thorough investigation any time soon, so here’s what I do know.

Excel features a nifty command called REPT. The command “=REPT(”|”,number_of_reps)” will type out the specified character (in this case, the vertical bar | ) however many times is stated in number_of_reps. So =REPT(”|”,6) should give ||||||.

By using the command =REPT(”|”,Dn), where Dn would be a cell in the D column, such as D4 or D23, the number of bars should be whatever number appears in that cell. And here’s where I found the problem. Look at this screengrab:

The REPT columns should plot the death rates given in column B. REPT only works with integers, so in the top graph I divide the column B values by point one to make them whole numbers. Once I saw the problem, in the the bottom graph I have it plot strict integers (typed by hand) from column D. Both graphs are flawed. Do you see the problem?

The death rate of 0.6 should be graphed with six bars ( |||||| ), and the death rate of 1.4 should be graphed with 14 bars ( |||||||||||||| ). Both are a bar short.

This is the kind of thing that drives me crazy, and that can derail a blog until I track down the problem. But I haven’t had time to do that, and it was becoming clear I needed to move forward here.

So that’s what I’ve got.

What’s your experience?

Anybody have any idea what’s going on here? Please share your insights in the comments. And if you use a different version of Excel (Mac or Windows)–or even the same version of Excel–I’d love to know if you experience the same problem. Let me know in the comments!