model depicted by a cog

Developing spreadsheets involves is a balancing act between how much you express in formulae within a single cell and how much you break-up formulae across a number of cells. In many cases a single formula captures much of what needs to be computed, but it is so complicated that it is hard to explain how it works. Modelling with spreadsheets gives one way of looking at breaking up formulae. Take a simple applied maths example, how much change do you get from £5 buying three apples (26 pence each) and two oranges (34 pence each). The formula =5-0.26*3-0.34*2 gives the answer.

(Formula visualized in EQUS)

However, explaining this formula can be a difficult, you have to understand that the “3” is the number of apples and 26 pence is represented as 0.26 pounds, and so forth. In fact, it is only easy to read the formula because the numbers and prices are all different in this case. Tracing the links between the problem and the formula is so important, the formula =5-(0.26*3+0.34*2) might be preferred simply because the purchases are now accumulated and then subtracted.
(Formula visualized in EQUS)

By contract, imagine how hard it would be trace the links if the problem involved five apples and five oranges, and they both cost 50 pence. It could end up looking like this: =5-0.5*5-0.5*5, and explaining how the problem has been captured would be harder.

When it gets hard to explain or read, you need to break up the formula, and you need to start modelling. You might have separate cells to compute the total apple price, the total orange price, the total cost. The result would then be another cell subtracting the cost from the 5 pounds. You can go further, such as separating out the unit cost of the apples and oranges. By “modelling” the problem in this way, it is easier to explain, check and also changes are easier. For instance, adding bananas at 13 pence would be relatively easy, or updating the price of the apples. In short, to make such changes you only need to alter a bit work you have already done.

However, modelling does result in a coordinating inter-linked cells. This creates good deal of structure which can be rather difficult to manage. The structure makes some things easy and others hard. You might find yourself thinking: “If no other products are likely to be added, why I am putting in the work needed to allow them to be added easily?”

Structure is enables changes of one sort, but other changes can become really difficult. Imagine, we wanted to change the example problem by introducing a discount deal, such as “buy one get one free”. Just working it out with a single formula it might be easier than ensuring the model is well structured for this type of discounting. A model where the number of items bought was made more explicit would probably help.

So, in general, spreadsheet development runs a balance between relatively hard to read and explain expressions in formulae and well structured and nicely labelled inter-linked cells. Is there an obvious middle ground? Not really! It depends on what you are wanting to do, and how your problem might change: more products? discounting?, etc..

If you are teaching best practice in spreadsheet use, then good modelling is strongly endorsed encouraged. Though any good modelling is founded on a good understanding of the problem being modelled and what is possible within spreadsheets.

By contrast, if you are teaching and using spreadsheets to solve problems then less structure and formula means more ideas can be easily tried out. For example if you are working with new data set and wanting to find out more about it, formula can be more useful simply because different ideas or perspectives can be played with, with a lot less effort.


Share This