How to return a number between to numbers
Eg: if the value is 75, and I have set of prefixed ranges such as 1-10, 11-50, 51-100, 101-200 etc.
Since this value lies between the range 51-100, i want the value to be returned as 51-100.
Answers
-
You'll need to create nested IF formulas with AND logic expression. such as:
=IF(AND(Number@row>50, Number@row<101), "51-100", IF(...
-
One approach is to use the IF() function. It works like this:
IF( logical_expression , value_if_true , value_if_false )
...another way to write it so that it's easier to follow:
IF( logical_expression , value_if_true , value_if_false )
You'll need to use nested IF() expressions since you're evaluating for 4 conditions: "1-10", "11-50", "51-100", "101-200".
Assuming that the number you're evaluating is in a column named, "thisNumber", your process in near-plain English might be:
...IF thisNumber is greater than 100 , True! it must be "101-200" , False! now let's check IF thisNumber is greater than 50 , True! it must be "51-100" , False! now let's check IF thisNumber is greater than 10 , True! it must be "11-50" , False! it is "1-10"
Written in syntax that Smartsheet will understand:
IF( thisNumber@row > 100 , "101-200" , IF( thisNumber@row > 50 , "51-100" , IF( thisNumber@row > 10 , "11-50" , "1-10" ) ) )
Flattened for cut-paste into Smartsheet:
= IF(thisNumber@row > 100, "101-200", IF(thisNumber@row > 50, "51-100", IF(thisNumber@row > 10, "11-50", "1-10")))
You can find a list of all Smartsheet functions and related documentation here:
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives