Reference functions are essentially look up operations, meaning that they are used to find values using a certain criterion. They can be used in all calculation types as long as the output is consistent with what the calculation type expects. For example, when using a reference type function in a numeric calculation, the result of the calculation should be a number, not text or a date.

• contains: determines if the first value contains the second value

contains("Three green pairs of shoes", "green") = 1
contains("Three green pairs of shoes", "red") = 0

• firstnonzero: returns the first value from a range that is not zero

firstnonzero(0,0,3,2,4) = 3

• firstpositive: returns the first positive value from a range

firstpositive(-2,-3,2,6,7) = 2

• firstvalue: returns the first value from a range

firstvalue(4,2,6,7,9) = 4

• index: returns a specified item from a range

index(3, "blue", "orange", "purple", "red", "violet", "yellow") = purple

• lastnonzero: returns the last value from a range that is not zero

lastnonzero(7,2,5,6,0) = 6

• lastpositive: returns the last positive value from a range

lastpositive(4,6,1,3,-5) = 3

• lastvalue: returns the last value from a range

lastvalue( 4, 7, 2, 6) = 6