Use Constant Values In Excel Rules
When working with numbers, currencies, dates and time in Microsoft Excel, the regional setting of the computer should accord with the project region setting. This is because Microsoft Excel formats the data types using the templates in the regional setting. The following table outlines some other things to consider when using constant values in Excel rules.
| Type of values | Considerations |
|---|---|
| Text | If declarations or attribute types are explicitly defined (in Excel or Policy Modeling), then text attribute values can be referred to in condition columns without using quotes. Otherwise quotes are required. Tip: You can add quotes quickly to a group of text cells in Excel. To do this, select the cells and in the Format Cells dialog, on the Number tab select Custom, and in the Type field enter \"@\". |
| Currency | To format a cell as a currency value, do not use the dollar sign button on the Excel ribbon in the Number group. Instead in the Format Cells dialog, on the Number tab select Currency. Do this for both the header cell and the values in the column underneath it. Note that the currency formatting of the project region determines what acceptable currency symbols are for numeric constants in expressions. |
| Number | If a cell contains a number formatted with a restricted number of decimal places, be aware that the underlying full-precision value, rather than the formatted display value, is used in calculations. |
| Date | Date values in Excel rule tables get recognized as dates when they are formatted as dates. International date format (yyyy-mm-dd) are recognized as dates regardless of the Excel cell format. |
| Uncertain | Generally, uncertain should only ever appear in an Excel rule as a conclusion, not in a comparison. If uncertain is a condition in a rule (where uncertain as a value is shorthand for x is uncertain), it should be the first condition in the rule. This is because the cascading logic applied to tables requires all previous rows to be disproven (that is, evaluate to false) for a row to trigger. Any comparison of an uncertain attribute to an actual value will yield the result uncertain, therefore resulting in no further table rows being checked. |