Numeric Functions

• Basic arithmetic operators: + - * /
  Example: [Unit Price]*[Avg Daily Units]
• SUM([numeric field1 ], [numeric field2 ], ... ), returns the sum of a comma-separated list of values.
• AVG([numeric field1 ], [numeric field2 ], ... ), returns the average value of a comma-separated list of values.
• ROUND([numeric field ], n ), returns the value of the numeric field rounded to the n th decimal place.
  Example: Round([Total Amount]/12,2)
• FLOOR([numeric field ], n ), returns the value of the numeric field rounded down to the nearest integer multiple of n.
  Example: FLOOR([MyPrice],0.25) rounds down to the nearest quarter.
• CEILING([numeric field ], n ), returns the value of the numeric field rounded up to the nearest integer multiple of n.
  Example: CEILING([MyPrice],0.25) rounds up to the nearest quarter.
• RANGELOOKUP("ESM ModelName ", "ReturnFieldName ", "LowRangeFieldName ", "HighRangeFieldName ", {KeyFieldName1 }, ..., {KeyFieldNameN }, {KeyLookupField })

or

RANGELOOKUP("ESM ModelName ", "ReturnFieldName ", "LowRangeFieldName ", "HighRangeFieldName ", {KeyFieldName1 }, ..., {KeyFieldNameN }, {KeyLookupField }, Decimal_Precision )

This function returns the value of ReturnFieldName in ESM ModelName that corresponds to the row in ESM ModelName where the Key Fields match and where the value of KeyLookupField fits into the range of values between LowRangeFieldName and HighRangeFieldName.

• The Key Field Names are KeyFieldName1, KeyFieldName2, ..., KeyFieldNameN. You must have at least one Key Field Name. The Key Field Name(s) must exist in ESM ModelName and in the model that contains the RANGELOOKUP function. These key field names must be enclosed with curly braces { }. You can use an unlimited number of Key Fields, but a best practice is to use fewer than 10.
• KeyLookupField is a field of type Numeric or Formula (that resolves to a number). It contains the value that you want to look up in the range of values between LowRangeFieldName and HighRangeFieldName.
• LowRangeFieldName and HighRangeFieldName must be of type Numeric or Formula (that resolves to a number) in ESM ModelName.
  • LowRangeFieldName uses >= (greater than or equal to) when comparing to KeyLookupField.
  • HighRangeFieldName uses < (less than) when comparing to KeyLookupField.
  • Ranges should be continuous so they do not leave gaps: 0-5, 5-10, 10-15, and so on.
  • If the value of KeyLookupField is below the value of LowRangeFieldName , 0 is returned.
  • If the value of KeyLookupField is equal to or above the value of HighRangeFieldName , the value of [ReturnFieldName ] in the row with the highest range value is returned.
  • If the value of KeyLookupField does not fit in any range, 0 is returned. This implies that the ranges are not continuous, and the KeyLookupField value falls in a gap between ranges.
  • If the value of KeyLookupField is valid in more than one set of ranges (rows) in ESM ModelName , the value of [ReturnFieldName ] in the row with the highest range value is returned.
  • Decimal_Precision is optional. It indicates to what decimal place numbers should be compared. In other words, how far out to the right should the KeyLookupField be compared to the low range and high range. For example, if Decimal_Precision is 6, KeyLookupField is 0.12345 and the low range is 0.12 and the high range is 0.123456, then KeyLookupField is found in the range. If not provided, the default is 4. Valid values for Decimal_Precision are integers 0-9.
• HighRangeFieldName has a maximum value of 922 trillion, if using 4 decimal places.
• Lowering the number of decimal places from the default of 4 increases the maximum of HighRangeFieldName by a factor of 10 for each decimal place.
• Increasing the number of decimal places from the default of 4 reduces the maximum of HighRangeFieldName by a factor of 10 for each decimal place.
• As a best practice, using decimal precision greater than 4 is recommended only when the range values are small numbers.
• See Example: Using the RANGELOOKUP Function. To look up text instead of a number, see LOOKUP under Text Functions.

Example: Using the RANGELOOKUP Function

The RANGELOOKUP function allows two ESM models to communicate with each other. There must be one or more matching fields in both models, called the Key Fields.

In the following example, Company and Region are the Key Fields. Models Commission Ranges and Commissions Looked Up will interact. Model Commission Ranges contains the information that model Commissions Looked Up will look up.

Here is the list of fields in model Commission Ranges.

Here is the data for model Commission Ranges.

Model Commissions Looked Up will look up data in model Commission Ranges based on the common key fields, Company and Region.

Here is the list of fields in model Commissions Looked Up.

To calculate the RANGELOOKUP function, you need to load data into the first four fields. The formula fields are derived. Copy and paste the raw data into columns A-D, then click Load Data.

Click Refresh and then you will see the remaining columns calculated and filled in.

Explanation

• Reminder: here is the syntax for the RANGELOOKUP function:

  RANGELOOKUP("ESM ModelName ", "ReturnFieldName ", "LowRangeFieldName ", "HighRangeFieldName ", {KeyFieldName1 }, ..., {KeyFieldNameN }, {KeyLookupField })

• Model Commissions Looked Up has six fields:
• The first two fields are text fields that serve as the Key Fields: Company and Region.
• The third field is an informational text field: Sales Rep.
• The fourth field is a numeric field that serves as the Key Lookup Field: Sale Amount. This is the number that is used to look in the ranges of numbers in the model Commission Ranges.
• The fifth field is Commission % and contains the following formula:
  RANGELOOKUP("Commission Ranges","Comm%", "Sales Amt Low", "Sales Amt High", {Company}, {Region}, {Sale Amt})
  • Commission % is derived by matching the Key Fields Company and Region (from row 1 in Commissions Looked Up, "Company A" and "West"), to the fields of the same name in model Commission Ranges. In Commission Ranges, "Company A" and "West" are found in row 1.
  • Next, the value of Sale Amt is matched to the range of values between Sales Amt Low and Sales Amt High in row 1 of Commission Ranges. If Sale Amt >= Sales Amt Low, and if Sale Amt < Sales Amt High, the range is a match. Since the Sale Amt was 5000 and the Low and High Ranges were 0 to 10,000, then the range is row 1 is a match.
  • Next, the value in the field Comm% from the row where the range matches in Commission Ranges is returned and placed into the Commission % field in Commissions Looked Up. The value of Comm % is 0.1 and this is returned to Commissions Looked Up and placed into the field Commission %.
• The sixth field is Commission Amt and it is a formula of Commission % * Sale Amt. This formula takes place within the Commissions Looked Up model and results in 500. To further refine this formula, a rounding function could be added to it: ROUND([Sale Amt]*[Commission %], 2)

The rows of data in Commissions Looked Up demonstrate the rules for the ranges:

Example: Using RANGELOOKUP to Compare Dates

If you want to compare Ranges and get a value as the output based on the details configured in the model, you can use the RANGELOOKUP function.

The following use case provides details on how you can use the RANGELOOKUP function to compare Dates and other attributes in the model and get a Value as the output.

For example, let's consider a source model that contains data for a company and its products in a specific region. Suppose you have defined a date range with their respective Start Date and End Date and defined a Value for each date range. The following image shows the model with all data.

When you enter any date for comparing data (as the last lookup field argument), the RANGELOOKUP function uses the attributes defined in the syntax and searches for the date within the date range. If the date falls within any of the Date Range defined and all other attributes match, the RANGELOOKUP function returns the value specified for that Date Range. The low range is inclusive and the high range is exclusive, so 04/01/2021 would return the value

The RANGELOOKUP function validates the date and other model parameters such as region, product, etc., and returns the configured value as the output.

In the above example, when you enter the comparison date as 09/01/2021, the RANGELOOKUP function returns the Value as 3. The function validates all parameters defined as keys in the syntax and returns the Value only if all keys match, that is, Region, Product, Company, and Comparison Date. So, when the comparison date was 09/01/2021, the RANGELOOKUP function validates if the date “09/01/2021” falls in any of the specified Date Range (Start Date and End Date) and also validates if the keys Region - “West”, Product - “Laptop”, Company - “Company A” is available in the Model, and then returns the value “3” configured for the combination.

The following image shows the defined syntax for the function.

If any of the parameters are not available in the Model, the function returns 0 as the value. The last parameter must be the field used for comparison. For example, when the Comparison Date was 11/28/2021 and Region was “East” and Company was “Company B”, the function returned the value 0 as the Model did not contain the Region and Company details.You have the flexibility to define the keys in the function's syntax based on your requirements and model. If the Comparison Date falls below the ranges (12/01/2020), the value returned will be 0. If the Comparison Date falls beyond the ranges (01/01/2022), the value returned will be 4.