Index/Collect does not work with < symbol

Cesar Perez
Cesar Perez ✭✭
inFormulas and Functions

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.

Formula.png


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

Destination File:

https://app.smartsheet.com/b/publish?EQBCT=44a9480066c94f69899375439f3cf1f7

Source File:

https://app.smartsheet.com/b/publish?EQBCT=ca1acb5c634b4fb5b1a1ba8686638bb3


Tags:
· Share on Facebook Share on Twitter

Best Answers

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

    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.

    10xViz.com

    · Share on Facebook Share on Twitter
  • Hollie Green
    Hollie Green ✭✭✭✭✭
    Answer ✓

    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.

    · Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
You would have to insert a new header field into the form that has that particular verbiage formatted the way you want then use the form field logic to display this header row if one of the incorrect answers is selected.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108403,"type":"question","name":"Date formula to set cut off period","excerpt":"I am trying to set a date formula to for the following statement; if the [Next Due Date] is <= 27\/07\/2023 return \"Performed\" Example: =IF(ISBLANK([Last inspection]@row), \"\", IF(DATE(2023,7,27)) < [Next Due Date]@row, \"Overdue\", \"Performed\"))","snippet":"I am trying to set a date formula to for the following statement; if the [Next Due Date] is <= 27\/07\/2023 return \"Performed\" Example: =IF(ISBLANK([Last inspection]@row), \"\",…","categoryID":322,"dateInserted":"2023-08-02T11:05:55+00:00","dateUpdated":"2023-08-02T11:35:37+00:00","dateLastComment":"2023-08-03T08:51:04+00:00","insertUserID":125711,"insertUser":{"userID":125711,"name":"Adedeji Adebanjo","url":"https:\/\/community.smartsheet.com\/profile\/Adedeji%20Adebanjo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZrfDGXkxfL4!BfDCQc0uMJQ!nMYqiMpYuek","dateLastActive":"2023-08-03T10:26:54+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":91566,"lastUserID":125711,"lastUser":{"userID":125711,"name":"Adedeji Adebanjo","url":"https:\/\/community.smartsheet.com\/profile\/Adedeji%20Adebanjo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZrfDGXkxfL4!BfDCQc0uMJQ!nMYqiMpYuek","dateLastActive":"2023-08-03T10:26:54+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":19,"score":null,"hot":3382028219,"url":"https:\/\/community.smartsheet.com\/discussion\/108403\/date-formula-to-set-cut-off-period","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108403\/date-formula-to-set-cut-off-period","format":"Rich","lastPost":{"discussionID":108403,"commentID":388644,"name":"Re: Date formula to set cut off period","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388644#Comment_388644","dateInserted":"2023-08-03T08:51:04+00:00","insertUserID":125711,"insertUser":{"userID":125711,"name":"Adedeji Adebanjo","url":"https:\/\/community.smartsheet.com\/profile\/Adedeji%20Adebanjo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ZrfDGXkxfL4!BfDCQc0uMJQ!nMYqiMpYuek","dateLastActive":"2023-08-03T10:26:54+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-03T08:50:18+00:00","dateAnswered":"2023-08-02T12:58:11+00:00","acceptedAnswers":[{"commentID":388454,"body":"

Try removing one of the closing parenthesis after the DATE function.<\/p>"},{"commentID":388644,"body":"

Thanks! That sorted it!😀<\/span><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108391,"type":"question","name":"Can I use groups to create series?","excerpt":"I have this report and from this I have created this Chart but I want it to drill down to the show the individual Branches on a stacked column chart like this. is this at all possible?","snippet":"I have this report and from this I have created this Chart but I want it to drill down to the show the individual Branches on a stacked column chart like this. is this at all…","categoryID":322,"dateInserted":"2023-08-02T02:31:48+00:00","dateUpdated":"2023-08-02T11:36:34+00:00","dateLastComment":"2023-08-03T13:06:28+00:00","insertUserID":163489,"insertUser":{"userID":163489,"name":"Benny C","url":"https:\/\/community.smartsheet.com\/profile\/Benny%20C","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xq_RMfil_3w!!B9mU9H6NId1","dateLastActive":"2023-08-03T00:58:31+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-03T13:06:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3382013296,"url":"https:\/\/community.smartsheet.com\/discussion\/108391\/can-i-use-groups-to-create-series","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108391\/can-i-use-groups-to-create-series","format":"Rich","lastPost":{"discussionID":108391,"commentID":388682,"name":"Re: Can I use groups to create series?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388682#Comment_388682","dateInserted":"2023-08-03T13:06:28+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-03T13:06:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/UYHRDRG9I6TS\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-03T00:59:37+00:00","dateAnswered":"2023-08-02T12:47:07+00:00","acceptedAnswers":[{"commentID":388443,"body":"

This isn't possible in a report. You would have to use a specifically structured metrics sheet with cross sheet references pulling in your totals.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions

  • Smartsheet Behaviours

    I am trying to create a form that throws back a response when you tick a box like the example below. I have been told to use the logic but i cant seem to find a logic that will work, any suggestions? Additionally, if they tick an answer and it is incorrect, I want to show the text in red and if correct the text is in…

  • Date formula to set cut off period

    I am trying to set a date formula to for the following statement; if the [Next Due Date] is <= 27/07/2023 return "Performed" Example: =IF(ISBLANK([Last inspection]@row), "", IF(DATE(2023,7,27)) < [Next Due Date]@row, "Overdue", "Performed"))

  • Can I use groups to create series?

    I have this report and from this I have created this Chart but I want it to drill down to the show the individual Branches on a stacked column chart like this. is this at all possible?