The Problem

In attempting to “write the right software” we’re routinely faced with a prose (or worse, spoken) request that the client believes is clear and proper but in fact can’t be unambiguously implemented.

I wrote earlier (Easy and Simple Isn’t) of my attempt to clarify a desktop lamp’s behavior only to discover the electronic lamp had a dimmer built into its power switch.

What does the dimmer do to the requirement? It makes something that was true or false into a range of discrete values.

The simple logic of a light switch (without a dimmer) is functionally this:

     next state = not(prior state)

So, if it lit, it becomes not lit. If not lit, it becomes lit.

That’s no longer true if it has a range of values. The behavior of “trigger the switch” (or just “switch” the lamp) actually goes through a sequence that loops on each use of the switch:

This still requires the logic for handling the power cable. If this electronic lamp is unplugged, it always starts on its brightest setting when plugged back in, regardless of what setting it had when unplugged (including off).

The simple requirement to “handle plug in, unplug, and switch the lamp” as prose is clearly insufficient. I could certainly write a more detailed prose requirement, but honestly, the problem with prose is that it can’t be tested. To be tested requires execution — and to execute requires a programmer write it! We have a catch 22 … or do we?

Understandable to the Non-Programmer Client

Those who noticed that I presented the light table in Excel get a cookie.

Without using VBA (or the Office .Net interop) and restricting usage to just formulas in cells, Excel is powerful enough to represent a tremendous amount of logic, even when not using numeric tasks. This lamp is obviously non-numeric, yet can be represented in Excel (or LibreCalc, or other spreadsheets — but I use Excel).

Formulas in cells are live. This has tremendous implications for using Excel for requirements. The requirement can be exercised by providing input and immediately generating the result of the computation. In many ways, the Excel can be not just a requirement but a full Proof of Concept (PoC).

Obviously, I’m not proposing to deploy an Excel spreadsheet! It can’t scale, has no auditing features, doesn’t persist beyond the last values entered, etc. I’m a developer, I’m aware of all of these issues.

But as a PoC and an expression of the requirement, Excel captures not “some general idea” but “a working model of the intention.”

Also, the Excel can act as the basis for a User Acceptance Test (UAT). UAT has to come from requirements, not from implementation, and the client can “feed in” each UAT case into Excel manually, and then into the resulting production candidate, and if there’s a discrepancy, one (or both) of the Excel or candidate must be broken. What won’t happen is the “That’s not what I meant!” argument. The Excel represents “what was intended.”

This makes it far easier for all the parties (client, analyst, developers, QA, etc.) to communicate.

Who Does It?

Creating a spreadsheet that represents a requirement is more complex than just pasting values into a sheet as a container for a CSV file or output from another system.

If the client has someone that uses sheets to do financial analysis, that person might be able to create a sheet for a requirement without any problem. Anyone comfortable with “=if()” and the use of the boolean expressions has most or all of what a requirement needs.

Often, though, the client doesn’t have such a person so the burden of creating the sheet falls on the analyst. Granted, today, that term is often out of use, so they might have some different title. But the analyst is technically adept in logic even if not a programmer. The analyst doesn’t need to know Python, Java, JS, or whatever project language is in use. They should be comfortable with the idea of functions (inputs produce outputs) and they should be meticulous and precise.

Of course, a developer can certainly do this — if they are willing to spend a few hours learning some basic Excel features:

• named ranges
• vlookup and hlookup
• creating sheets and naming them
• if, and, or, not, xor, mod, etc. (basic logic and arithmetic functions)

Also, programmers must remember that Excel uses copy/paste instead of loops, so “the loops are fully unrolled.”

It’s reasonable to teach someone “enough Excel” to learn to do this. It’s far less effort to learn to express intentions in Excel than the equivalent effort in Python for instance.

Example with Just Clicking the Switch

Now, here is an example of capturing the actual behavior of that lamp that caused me such astonishment for what should have been a simpler example. I’m going to ignore the plug in/unplug logic, and assume the lamp is always powered up. Thus, this is just handling “what happens when the switch is pressed.”

First, when the lamp is plugged in, where does the sequence start? By testing, the lamp starts in the bright setting. To capture this, we’ll put the sequence of lights and the starting number onto a sheet all its own.

Next, name the ranges so that we are free to make mistakes and not have to fix everything manually later.

I called the range for the sequence “lights.” I made the range all of B:C. I did that so that I can add (or remove) entries and the range will still include them. This is a common technique: do not assume you have all at first, you might have only a few of the many.

I did the same (not shown) for light_start to refer to F1.

Both of these are on a sheet named Tables. They are on their own sheet (which you can call anything) to separate the logic from the constants. It’s reasonable to allow clients to edit the constants if they wish … the logic will still work, so they can “try things” without involving even an analyst. Also, the constants on the Tables page can become configuration data by programmers.

To add logic, we go to a clean sheet. For most systems, we want to delineate an “input area” where the input data from the domain is entered, and then we provide an “output area” where the result(s) appear. The rest of the sheet is the “intermediates” area where all the formulas do the work.

Since this is only handling ‘click’ the result of the sheet is a sequence of changes.

This is where the greatest difference between a program and Excel manifests: Excel uses new rows per “loop iteration.”

So, each row in our new sheet is one click of the switch.

In a normal program, input is provided and the results are shown. In Excel, each input is provided in a new row, and the results are shown on that row. This lets us have “streaming” or “stateful” requirements, such as the lamp. Later (in another post) I’ll show how to externalize the state so that Excel can show a streaming behavior without multiple rows.

A Row Per Click

What does the first row look like?

It starts with the current value (which is light_start) and results in the next value as output.

This example has no “input data” because we’ve “fixed” the input to be the action “click.” In the next post, I’ll add support for plug in/unplug and then ‘switch’ becomes an input of its own.

The first row looks like this:

The word ‘start’ is just text. B1 and C1 are formulas. They refer to the named regions defined on the other sheet. And their values show that when the light starts (after plugged in) it’s bright.

A switch press is represented by a new row based on the prior row.

A key aspect of a sheet that represents requirements: no formula should ever look below the current row or to the right of the current column on the current row. This avoids an error I hit often when people use Excel to model trading algorithms: it avoids “leaking the future.”

Therefore, when the switch is clicked, it’s legal to look at the entire prior row value and operate on it. In this case, the operation is just to advance through the list.

Now, “lights” refers to the sequence. Some lamps have only “off and on.” To avoid “assuming” we have a perfect list, we do not hard code aspects of the tabular data.

Here is a naive (and wrong) attempt to show what happens on the next click. The logic is “advance through the list one element at a time” so +1 is used:

Hitting enter will give 4, and since 3 is the highest entry in the list, the vlookup when copied will just show the highest value, as this wrong result shows.

The error isn’t an Excel error. This is actually a failure from not using “clock counting.”

If it’s 2 o’clock, and an hour is added, it becomes 3 o’clock. If it’s 12 o’clock and an hour is added (unless using military time) it becomes 1 o’clock. If it’s 23 hours and an hour is added in military time, it becomes 00 hour. Even military time has clock counting. It means looping the count instead of growing without bounds.

To implement clock counting, we use the modulus of the addition with the number of entries in the table as the counter. It seems weird, but it allows any number of sequenced steps to be advanced through cyclically. The number of entries is found using the “count” function so it’s not hard-coded.

Here is how it looks (notice the formula in the formula bar showing the formula in cell B2 highlighted):

So each click (which again is just text label, not part of computation) the counter advances by one through the list.

Plug in the lamp, it comes on bright. Click the button, and it turns off.

To do another click, copy row 2 “a few times” and paste. Each row is another click.

Now, by inspection reading down the rows, it turned on bright, went off, dim, normal, bright, off, dim, normal, bright, off, and finally dim.

That seems to be the full cycle and properly wraps around each time. The sheet reflects the peculiarity of this particular lamp.

How would a lamp that had no dimmer and started off be done?

Editing to Show the Simple Case of On and Off starting with Off

Making no changes to the sheet with the logic changing the data sheet to have only off and on, starting with off, looks as follows:

And with zero changes to the logic sheet, the following is now shown:

Conclusion

The requirement was inadequate when stated in prose. It could have been extended to have a table of values and the instructions to cycle through, and restarting at the first entry when advancing the end. Given enough prose, it’s possible to make anything “precise.”

But the use of Excel let the client interact with their real data. And 100% of the logic is now available to the developer.

The creation of the sheet wasn’t as easy as just “pasting sample data” would have been, but the resulting ability to change the cyclic data and see the effects isn’t possible without a “live” something to handle the update.

Could this be a PoC instead of a requirement? Sure. It is also a good basis for a UAT.

Download the sample excel from above to play with it yourself.

And please: let’s help the developers “write the right code.”

Keep the Light,
Otter
Brian