There are several ways in which you can make your Excel rules easier to understand.
Shorten attribute names in Excel workbooks
Simplify rule table layout by merging cells
You can create an abbreviated way of referring to an attribute in Excel using a Legend Key. Specification of this abbreviated form is optional.
To specify a legend key:
For example, if you have the following declaration:
Attribute Type | Attribute Text | Legend Key |
Text | the country of citizenship | Country |
Text | the nationality of the individual | Nationality |
you could have the following rule table:
Country | Nationality |
USA | American |
Scotland | Scottish |
Japan | Japanese |
else | uncertain |
You can also use legends with tables which use boolean attributes.
For example, if you have the following declaration:
Attribute Type | Attribute Text | Legend Key |
Number | the individual's age | Age |
Boolean | the individual is disabled | Disabled |
Boolean | the individual is entitled to compensation | Compensation |
you could have the following rule table:
Age | Disabled | Compensation |
<18 | TRUE | TRUE |
>65 | TRUE | TRUE |
FALSE | FALSE | |
else | FALSE |
Looking at the multiple condition example below, we note that the values for the Adults condition cells consist of only three unique values 1, 2 and 3.
Attribute Type | Attribute Text | Legend Key |
Number | the number of adults in the group | Adults |
Number | the number of children in the group | Children |
Text | the ticket type | Ticket |
Adults | Children | Ticket |
1 | 0 | Single |
1 | 1 | Double |
2 | 0 | Double |
2 | 1 | Family |
2 | 2 | Family |
2 | 3 | Family |
3 | 0 | Family |
else | Combo |
We can choose to merge the cells in this column that share the same value. To merge cells in Excel, select the cells that you want to merge and then click the Merge & Center button on the Excel formatting toolbar. You may get a warning that advises that merging will keep the upper-left most data only. Click OK.
Adults | Children | Ticket |
1 | 0 | Single |
1 | Double | |
2 | 0 | Double |
Family | ||
3 | Family | |
else | Combo |
This table is equivalent (in function) to the original table, but allows us to emphasize that only three distinct values are used for Adults, and the rows that they cover.
You can also merge conclusion cells, however note that this will change the structure of the rule logic slightly. See Prove the same set of conclusions using multiple conditions and Allow rule conditions to evaluate in any order and handle missing values for further details.
Typically a rule table will be specified with the conclusion and conditions listed left-to-right in separate columns, and each set of conditions and conditions listed in separate rows, as shown below. (NOTE: In this example "can be trusted" represents the boolean attribute "the user is of a trustworthy nature".)
condition | taxable income | the user can be trusted | the risk level of the user | |
the user is applying for some money | <=100 | > 0 | FALSE | high |
<=2000 | > 100 | high | ||
<=50000 | > 2000 | TRUE | medium | |
>50000 | low |
It is possible to rotate a rule table such that the rows and columns are swapped. This effectively means that we represent a rule table in the Y-X orientation rather than the X-Y orientation. For this example, the rotated rule table would be:
condition | the user is applying for some money | |||
taxable income | <=100 | <=2000 | <=50000 | |
> 0 | > 100 | > 2000 | >50000 | |
the user can be trusted | FALSE | TRUE | ||
the risk level of the user | high | high | medium | low |
Both rule tables will generate the exact same rules when compiled.