Returns the 0-based index of the range the expression in the first parameter falls within, with the ranges indicated by each subsequent parameter.

Usage:

RangeIndex ( e, r1 [, r2] [, …] )

Parameters:

e

Any valid expression that resolves to a number. This may include standard formulas, function calls and question references, e.g. [q1] + [q2].

r1

The starting number of the first range. If the expression in e is found to be equal or greater than this value, but less than the value in the next range parameter (if provided), then the RangeIndex function will return 0.

r2…

Optional – The starting number of the second or subsequent ranges. If the expression in e is found to be equal or greater than this value, but less than the value in the next range parameter (if provided), then the RangeIndex function will return the index of this parameter.

Notes:

The index returned by the function is entirely dependent on which of the range parameters the expression falls within. If it’s the first range parameter (i.e. the second parameter in the function), then 0 is returned; subsequent range parameters will return 1, 2, 3 etc.

This is a useful function for scoring algorithms which calculate a score, and determine a final result based on ranges of possible scores. It can be useful for other scenarios as well, such as calculating a person’s age and determining which age range they fall into to affect the logical flow of the document being generated.

Example

Determine a range based on a scored questionnaire:

This scenario is based on a questionnaire made up of 5 multiple choice questions, each with 3 possible responses. Each of the possible answers is scored with a value of 0, 1 or 2 which is provided via the answer’s Tag property.

A variable question will be used to perform the calculation, returning a result that can be anything from 0 to 10. There will be 5 ranges of scores: 0 – 3 = Fail; 4 – 5 = Pass; 6 – 7 = Credit; 8 – 9 = Distinction; 10 = High Distinction. The formula to calculate the range is:

RangeIndex([q1] + [q2] + [q3] + [q4] + [q5], 0, 4, 6, 8, 10)

The parameters passed in do the following:

[q1] + [q2] …

This expression adds up the score for each of the multiple choice questions.

0

If the total of the first parameter is 0 to 3, RangeIndex returns 0.

4

If the total of the first parameter is 4 or 5, RangeIndex returns 1.

6

If the total of the first parameter is 6 or 7, RangeIndex returns 2.

8

If the total of the first parameter is 8 or 9, RangeIndex returns 3.

10

If the total of the first parameter is 10 or higher, RangeIndex returns 4.

Once the calculation is set up in a variable question, it can be used as a parent to drive other questions, such as a multiple choice question which alters the output of the final document depending on the calculated index.

Determine an age range based on a person’s age calculation:

This scenario is based on using an Address Prompt question with a custom Date of Birth field, which is used along with a current date variable to determine the age of the person. The age is then processed by the RangeIndex function so that information can be displayed in the document relevant to that person’s age range.

To simplify the formulas, the age calculation will be performed in a separate answer to the RangeIndex, and then referenced. For simplicity it is assumed that all individual calculations are separate answers that belong to the same Variable question. The age calculation is as follows:

DateDiff(0, [q1.DOB], Today(), 1, 1)

The age range calculation is as follows:

RangeIndex([Age], 0, 18, 25, 40, 60)

A multiple choice question can then be driven by the age range calculation by using it as its Dynamic Parent. The possible age ranges derived are Child (0 – 17), Young Adult (18 – 24), Adult (25 – 39), Middle-Aged (40 – 59) and Senior Citizen (60+).

Advanced RangeIndex Usage

Most of the time the RangeIndex function will be used with hard-coded range values. However, there might be a reason why the ranges can vary – for example, if a different scoring pattern is required depending on the type of person using the questionnaire.

To achieve dynamic range values you can simply place a question reference instead of a hard-coded range value in the parameters of the RangeIndex function. For example, consider the following RangeIndex formula:

RangeIndex([Score], [q2.Range1], [q2.Range2], [q2.Range3], [q2.Range4], [q2.Range5])

In this example, the question identified as q2 could be a data field question, set up to be dynamically answered based on an Address Prompt question. That data field question should be based on a data table which exposes fields called ‘Range1’, ‘Range2’, ‘Range3’, ‘Range4’ and ‘Range5’. Each record in that table would be related to which Address is selected, and would return the appropriate range values.

Further to that, the range values could actually be calculated by basing the data source on a view rather than a table, or by instead pointing them to other formula-based Variables. There are a wide range of possible uses for this kind of flexibility.

Keep in mind, though, that you are always limited to the number of ranges possible as defined by how many parameters are set up in the call to the RangeIndex function. To work around this limitation, consider creating enough possible range parameters to suit any need and ensure that in any given case unused range values are set to a number less than its preceding range value.

For example, if the RangeIndex formula above is used to point to a data table, one possible row of data might return the range values 10, 20, 30, 0, 0. This has the effect of having only 3 possible ranges instead of 5. You can even use this to merge two or more inner ranges, for example 10, 20, 0, 30, 40

 

Related Articles

 

Keywords

adddate