Rules in Excel

Can you imagine that spreadsheets existed before computers existed? It was a sheet of paper used typically by an accountant to record information or a response form for students to record answers.

I can’t imagine a world without software to help make, organise and print those sheets.

Spreadsheets help to store and analyse data in tabular form but the major benefit is that you may change values and see the effect on calculated values. Since it is handy to annotate the values you may also store text in a cell.

Can you calculate with strings?

Yes, you can! String manipulation functions and algebra are used to calculate new values based on string values. This turned the computerised spreadsheet into an all-rounder.

It’s used to manage contacts, overview a music collection, write requirements, make an invoice, calculate remaining stock, plan a project … what can’t you do?

The well-known, most used (and abused?) spreadsheet software named Excel is an office staple.

How does Excel deal with rules?

This is the fourth post in a series on the way rules are used in apps and software ‘for the millions’. Earlier posts dealt with Outlook, Salesforce and Shopify.

Formulas are rules

Each value may be calculated by a formula and the formula may express a rule. A typical example is the following formula in the following table:

The rules express the meaning that a regular customer gets 20% discount. Probably it should say ‘each’ regular customer must get 20% discount. Copy the formula to all relevant cells. Et voila, change the customer type and all values are updated!

Conditional formatting with rules

Another use of Rules in Microsoft Excel is to change the formatting for a cell based on some criteria. For example you can say that all products that are in stock should be marked orange in the table below:

Generic rules in tabular format

There are big differences with the rules typical for rule engines because the Excel formula is written for a data instance. That’s also why you can’t understand the rule expression if you don’t see the table. Why have a rule if there is no data to apply the rule to? Excel has a point here, although it feels counterintuitive from my perspective. The solution is to use named cells. Instead of referencing in a formula to B:2 we may now reference to Customer_type (which is the name for B:B). It’s an improvement but extra work to maintain.

Another solution is to make look-up tables that look-up the discount based on customer_type:

And if you want to cheat, there are add-ins that turn Excel into a real rule engine for decision tables.

Do Excel rules really help to adapt the spreadsheet to personal usage? Yes, I believe it does. But there is a drawback. Read the endless papers, articles and analysis on the risks of end user programming.

I believe end user programming does have a future.

So the question is: what could be improved in Excel from a ‘business rules’ perspective?

Ask why? Answer because…

For a calculated cell value you would like to know how it is calculated and what rules where involved. In Excel this is named Trace Precedents and Trace Dependents. The limitation of this feature is that it goes only one level deep (so you have to click ‘Trace Precedents’ again), or you have the option to show all dependencies …

But I like to have a nice hierarchical overview of all the rules that contributed to the calculated cell value.

Ability to name, manage and re-use rules…

Formulas expressed in terms of cell addresses are hard to keep straight and hard to audit. The use of named variables in formulas makes the rules more reader friendly. But from a rules perspective I would also like to name the rules, get a list and record their source, use and status.

Add semantics to easily deal with empty cells…

Very annoying is the N/A error. It indicates that a formula can’t find what it’s been asked to look for. Remember that formulas reference cell values. If there is no value in a referenced cell you get the N/A error. The resolution in Excel is to change all your rules to something like:

IFERROR(FORMULA(),0)

which says: IF(your formula evaluates to an error, then display 0, otherwise display the formula’s result). And what if I need the sum of all values in a column except for the ones that have no value? I need to write something like:

SUMIF(B:B,”<>#N/A”)

That makes your rules even harder to read and review. Why not indicate per sheet or column that empty cells may exist and should just be ignored? That is usually what I want it to do, so why is there no easy way to express it?

So the business rules perspective provides some ideas to improve the widely used Excel.