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