Tutorial Using Cell Values in Queries & Rules

Top     Installation     Introduction     Samples     Tutorial     Reference     Release Notes

Cell values can be used in both queries and rules. This allows variable inputs from the user both in the reasoning and query processes.

Using Cell Values as Inputs to Queries

The query used in the example so far contained the input value for Quantity. It would be better if the query picked up the quantity value from another cell.

That is done by adding place markers of the form _N in the query. The place markers are then replaced with optional cell arguments to RQuery(). To get the Quantity from cell A7 in our example, the query would be:

=RQuery(PriceRules, "Find UnitPrice when Quantity    = _1", C7)

To do this:

1. Delete the old RQuery() in A6.
2. Just for readability, put the headings "Quantity" and "UnitPrice" in cells C6 & D6.
3. Enter a quantity in C7, say 11.
4. Enter this query in D7, that uses a place marker, _1, which is replaced with the value of the quantity in the cell C7:
   =RQuery(PriceRules, "Find UnitPrice when Quantity = _1", C7)

Because C7 is a parameter in the query, the query becomes dependent on C7, so whenever the quantity in C7 changes, the query is recalculated. You can have any number of place holders, numbered sequentially (_1, _2, _3...), with corresponding cell arguments as parameters to RQuery().

The spreadsheet should look like this:

Using Cell Values in Rules

Just as rule queries can be dependent on other cells, the rules themselves can be as well.

In the example so far, the discount quantity of 20 is stored directly in the rules. It would be more flexible if that value was stored in a cell and the rules picked up the value from the cell.

A function, RCell() allows you to do that. It takes a cell value and assigns it to an ARulesXL fact.

RCell(Fact, Cell)

• Fact - a fact name enclosed in double quotes
• Cell - the address of the cell with the value

Here's how to use RCell():

• Put the heading "Discount" in the cell C1, and a quantity, say 22, in D1
• Change the two PriceRule rules in A4 and A5 to read
  UnitPrice = 10 when Quantity < DiscountQuantity
UnitPrice = 8 when Quantity >= DiscountQuantity
• Insert a new cell in the rule set, A4, and add this call:
  =RCell("DiscountQuantity", B1)

The spreadsheet now looks like this:

Turning on dependency arrows (blue in the screen shot) in Excel (using Tools | Formula Auditing), you can see how the query in D7 depends on C7 and the range PriceRules, and that PriceRules depends on D1.

Experiment with different values in C7 and D1 to see how the rules and spreadsheet work together.

Best Practices

Cells should be linked to rules when the rules themselves depend on the cell, as in the case of discount quantity.

Cells should be linked to queries when the cell contains data for a specific case to be analyzed, such as the quantity ordered.

Copyright ©2005-7 Amzi! inc. All Rights Reserved. ARulesXL and Amzi! are trademarks or registered trademarks of Amzi! Microsoft and Excel are trademarks or registered trademarks of Microsoft Corporation.