Index/Collect does not work with < symbol

Options

Hi there,

I am using the INDEX COLLECT formulas to get a value if a number is between two ranges. The issue is that I'm getting an #INVALID VALUE error in the following formula:

=INDEX(COLLECT({TotalRiskFactor}, {RangeStart}, >=[Risk Score]@row, {RangeEnd}, <=[Risk Score]@row), 1)

The problem is with the "<" symbol in the second range.


Here are links to the test files. Any help with this issue will be appreciated.

Destination File:

Source File:


Tags:

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    The issue is with your arguments overall. Using the Score of 40 (as in your examples)...


    The score start range has to be 40 or greater, but the range end has to be less than or equal to 40.


    Try reversing it so that the start is less than the score and the end is greater than the score.


    Take a look at the second piece in your destination file. The formula is not throwing an error, but it is also not pulling the correct value in. It should be pulling in Moderate because 40 is between 35 and 54, but it is pulling in Moderate-High because that is the first entry where the Range Start is greater than 40.

  • Hollie Green
    Hollie Green ✭✭✭✭✭✭
    Answer ✓
    Options

    You have your greater than and less than backwards. Your formula is saying to looking for the start date to be greater than 40 and the 35 is less than 40.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!