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
Was this page helpful?
Let us know if this content is useful for you. We're always looking for ways to improve our support.