How to return a number between to numbers

manasaMS

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.

Mike TV

@manasaMS

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(...

Toufong Vang

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:

https://help.smartsheet.com/functions