Create a Rule Table In Excel

When you add an Excel document to your project, it will contain a rule template on the Rule Table worksheet. The template is shown in the image below.

Image of an Excel rule template on the Rule Table worksheet of a newly created Policy Modeling Excel rule document.

To write a simple rule in Excel which contains a single set of conditions and a single set of conclusions, follow these steps. In this walkthrough we will be concluding the admission price payable by an individual based on the number of whole years that they have been a member of a loyalty program.

  1. In Policy Modeling on the Data tab, add:
    1. the number attribute "the number of whole years the individual has been a member of the loyalty program" and
    2. the currency attribute "the admission price payable by the individual".

    For more information on creating attributes on the Data tab, see Create a New Attribute In Policy Modeling.

  2. Switch back to the blank rule template in your Excel rule document. By default this template table is created with two condition columns and two conclusion columns. As we only need one set of conditions and conclusions in this example, delete the first condition column and the final conclusion column.
  3. In the remaining condition column, replace the text condition with "the number of whole years the individual has been a member of the loyalty program". This cell is already in the correct Condition Heading style.
  4. In the remaining conclusion column, replace the text conclusion with "the admission price payable by the individual". This cell is already in the correct Conclusion Heading style.
  5. Type "1" in the cell below the "the number of whole years the individual has been a member of the loyalty program" cell. This cells is already in the correct Condition style.
  6. Tab across to the next cell (the cell below the "the admission price payable by the individual" cell) and type "$17.00". This cell is already in the correct Conclusion style.
  7. In the row below, enter another condition "2" with the associated conclusion "$14.00".
  8. Follow this on the next row with another condition "3" and conclusion "$10.00".
  9. In the next row, type ">3" for the condition and "$5.00" for the conclusion. The condition in this row performs a comparison to check if the number of whole years of membership is greater than three, and sets the admission price to $5.00 if this is true. For more information on using comparisons in rule conditions, see Write Rules Using Comparisons.
  10. Type "$20.00" in the cell next to the else condition. This applies an alternative conclusion of "$20.00". The effect of this alternative conclusion is as follows:
    • an individual who has been a member of the loyalty program for less than one whole year, or
    • an individual who has not yet joined the loyalty program

    will be charged $20.00 admission.

  11. Select all the conclusion cells, including the header cell (in other words, select the cell containing "the admission price payable by the individual" and all the table cells in the column underneath it). On the Home tab in Excel, click the Format button and select Format Cells from the drop-down menu. Then in the Format Cells dialog, on the Number tab, select Currency.

Your rule table should look like that in the following table.

Table 1. Excel rule table inferring the admission price for an individual from their number of years in a loyalty program
the number of whole years the individual has been a member of the loyalty program the admission price payable by the individual
1 $17.00
2 $14.00
3 $10.00
>3 $5.00
else $20.00

Tip: To quickly check that your table is operating correctly, firstly validate your rules. Then place your cursor in the conclusion header cell and click the Go To button on the Oracle Policy Modeling toolbar. Then select View in debugger from the pop-up window. In the Debugger, enter a value for "the number of whole years the individual has been a member of the loyalty program" and check that the "the admission price payable by the individual" is concluded correctly. For more information on using the Debugger, see Investigate a Goal.