Long Formulae

My number one rule for spreadsheets is a simple one:

A long formula is a bad formula.

Long formulae are the bane of spreadsheets. On more than 1 occasion I have been tempted to write to Microsoft and ask them to impose a 100 character limit on formula length (or at least provide such an option). But what is the problem?

Let's look at this the other way round for a moment. How many short formulae do you see that are difficult to understand after a little thought? I'll bet not many. Maybe there's a new function you need to look up in Excel help but that's about all. A long formula can suggest many things:

• There are lots of parts to a single calculation.
• A simple calculation is being performed in an overly complicated way.
• The author is basically showing off (frankly the worst crime of all!)

For sake of illustration I'm going to calculate a person's Body Mass Index (BMI).  BMI is defined as:

BMI = (Weight in kilograms) / (Height in metres) ^ 2

My spreadsheet is reasonably flexible. It allows people to choose the units they use for entering height and weight. Height can be entered in metres or feet. Weight can be entered in kilograms, stones or pounds. (I'm assuming that users are capable of entering fractions, e.g. 10 stones 6 pounds would be entered using =10+6/14.)

B1 = height value
C1 = height units ("metres" or "feet")


B2 = weight value
C2 = weight units ("kilograms", "stones" or "pounds")

To calculate BMI in one go you could use this formula:

=B2*IF(C2="kilograms",1,IF(C2="stones",1/0.1575,1/2.205))/((B1*IF(C1="feet",1/3.281,1))^2)

This isn't too bad but it could definitely be easier to follow! Think about how you would check it. You would probably have to break it down as follows and check each part somehow (e.g. using 'Evaluate Formula' and stepping through a couple of example calcs):

1. Convert height into metres
2. Convert weight into kilograms
3. Calculate BMI

Surely it would be clearer if these steps were shown separately?

Let's say we put the height in metres in cell B4. To convert to metres we just pick out this part:
=B1*IF(C1="feet",1/3.281,1)

Now let's put weight in kilograms in cell B5. Now we just pick out this part:
=B2*IF(C2="kilograms",1,IF(C2="stones",1/0.1575,1/2.205))

Finally the BMI formula goes from that first formula above to something that resembles the definition much more closely:
=B5/B4^2

Notice how much easier this is to check too.  You can easily check that each conversion is working correctly. Then checking the BMI formula itself is simplicity itself!