Do your Excel formulas fill down far enough?

Special guest post today! Thanks Dave for sharing, and Ian for, um, volunteering. -- Jim

From: SD-Dave Brann
Sent: Monday, August 27, 2007 6:23 PM

How many times have you received this phone call?

Client: "My Excel reports are messed up."
You: "Ian must have messed them up."
Client: "Don't care. How can we fix them?"
You: "Send it to me and I'll figure out what's wrong. After I'm done flogging Ian."

And then after 15 or 150 minutes, depending or your skill, enthusiasm for flogging, and/or the complexity of the Excel workbook, you realize that the formulas on your "formatted" sheet don't go down far enough to cover all the rows on your "data" sheet.

Your first thought should be: Ian really should have used a Sheet Type of "TextFillReport", which would have taken care of this quite nicely.

But for argument's sake, let's say that's not an option - maybe this is an Inputs sheet issue. Or maybe you're dealing with a RMS issue (the "R" stands for "Restaurant" for any of you who aren't familiar with company history), where you don't have access to the wondrous capabilities of the modern Modeling Studio. What then?

Well, your second thought might be that we have VBA macros that try to help cover this case. But maybe you forgot to put them in. Or maybe you're not sure you remember what VBA stands for in the first place. Or maybe you think that your client would never, ever run 45 reps of your simulation model.

And so, we humbly submit the following suggestion:

Somewhere on your formatted sheet (or anywhere that you're confident the user will be looking), insert the following formula into a cell:

=IF(COUNTA('MyFormattedSheet'!A:A)<COUNTA('data(MyDataSheet)'!A:A),"Too Many Rows: Need to do a fill-down on MyFormattedSheet worksheet, columns A:S","")

To translate: count the non-blank cells in the specified columns of my formattted and data sheets (and yes, the COUNTA function counts formulas as non-blank), and if the formatted sheet has fewer than the data sheet, show the message. Preferably in a bright red, bold font. Otherwise, show an empty string.

Come to think of it, if you want to be really helpful with your message, you could tell them how many rows the formulas should cover:

=IF(COUNTA('MyFormattedSheet'!A:A)<COUNTA('data(MyDataSheet)'!A:A),"Too Many Rows: Need to do a fill-down to row " & COUNTA('data(MyDataSheet)'!A:A) & " on MyFormattedSheet worksheet, columns A:S","")

And that should put an end to those annoying client phone calls. (Feel free to flog Ian anyway.)

[Note: No Ians were harmed in the writing of this entry.]

David M. Brann
Senior Analyst
TranSystems | Automation Associates, Inc.

Testing and Software Quality at TranSystems

Thanks to everyone in San Diego and Nashville who attended the brown bag on Testing and Software Quality on 8/14. I know it was a refresher for most of you, but I hope that it will help you think creatively about the testing process on your next project.

Some of the key points we discussed include:

Testing can be boring, how can we avoid it?

While you can't completely eliminate testing, you can reduce the need for the tedious and lengthy formal testing phase at the end of development, by adopting a "zero-defect mindset" and building quality into the development process itself.

Use an iterative "Milestones" approach that includes testing in each milestone -- starting with the basic "skeleton" of the complete model or application, and adding more detail as you go. Design before you code. Share your work with your project team. Share your work with your customers to confirm your project requirements. Try using prototypes.

Test-Driven Development

One intriguing concept from the agile software world is "test-driven development". This involves writing the tests first, before you write a single line of code.

Why is this useful? We went through a spreadsheet example of converting integers to Roman numerals, and showed how not only did we automate the testing process, but how it helped us think through the complete set of use cases while we were writing the code itself.

The next challenge -- how might we incorporate this into our simulation models or software applications?

The Testing Process

We covered the traditional definitions of unit testing, structured testing, and system testing, and how they fit into a project. This included an example of writing a test plan.

Finally, we talked about how a "bug" is not necessarily bad -- when you think of it not as a shameful blight on your otherwise perfect coding, but as a natural issue to be addressed and resolved in the course of a project. They can help you plan your work, and give you a realistic picture of how close you are to being done with your project. TestTrack is our standard application for tracking the lifecycle of these issues.

The complete presentation is available on the San Diego server.