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

  • Mike TV
    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
    Toufong Vang ✭✭✭✭✭
    edited 12/02/22

    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