# ComparativeValuePosition ( VList ; Comparator ; Value )

Quickly search for a comparative (>, <) number in an ascending number value list. Returns the value closest to the boundary that meets the criterea

Sample input:
ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; ">=" ; 5.27 )
ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; "<=" ; 75 )
ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; ">" ; 75 )
Sample output:
2
8
0

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

# Quickly search for a number in a ascending-sorted list of number values, to get the position of the value in VList
# For instance, looking in VList for the first value >= 79
# Result is the position of, for >, the first value larger than the Value; for <, the last value less than the Value
# So, it finds the values just above or just below (or, for =, at) the Value boundary
# This is most useful when VList has >100 values, particularly when they're roughly linear
# This assumes that the values ARE linear, and computes where the boundary should be, and jumps to that position
# If it hits it, it gives that as an answer; otherwise it throws out the extra values from the side of the boundary it is on, and tries again
# The 'throwing out' is what makes this work quickly, and this alternates throwing out early or late values
#
# Parameters
# VList must be an ascending sorted ¶-delimited list of numbers. It must have no empty values and no leading or trailing ¶s,
# except for the one trailing ¶ often used in FM value lists
# Value is the target number
# Comparator can be >, >=, ≥, <, <=, ≤
# If VList contains no values that meet the criteria (all outside the range), the result is 0
#
# Example: ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; ">=" ; 5.27 ) => Result 2 (because 6, in position 2, is the first value higher than 5.27)
# ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; "<=" ; 75 ) => Result 8 (because the highest number less than 75 is 12, position 8)
# ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; ">" ; 75 ) => Result 0 (because no numbers in list are > 75)
#
# Comparisons: for a 3648-item value list, choosing two values that were inexact matches and evaluated as items 791 and 2915,
# ValuesLessThanOrEqual gave wrong answers, took 2600ms; ValuePlaceInSList took 1611 ms; this CF took 13 ms
#
# Idea comes from ValuePlaceInSList (Steven Zeidel), which steps through one by one;
# I wanted a faster method for large value lists that were roughly linear, such as wavelengths from a spectrometer