Index/Collect does not work with < symbol
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:
Best Answers
-
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.
-
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
-
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.
-
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.
-
Thank you @Paul Newcome and @Hollie Green for the quick response. Both recommendations worked. I appreciate the help.
-
Help Article Resources
Categories
Check out the Formula Handbook template!
