Search code examples
google-sheetsgoogle-sheets-formula

convert the values if it falls between specific number


I want to convert the values if it falls between specific number then convert them into specific number.

For Example

IF(AND(Cell>0.0, Cell<=0.25),0.25,"")
IF(AND(Cell>0.25, Cell<=0.50),0.50,"")
IF(AND(Cell>0.50, Cell<=0.75),0.75,"")
IF(AND(Cell>0.75, Cell<=1.0),1.0,"")

Is it possible to convert the values into given values i would appreciate your help thanks.

Sheet


Solution

  • updated answer:

    =index(ceiling(A2:A22,0.25))
    

    Alternative:

    =index(lambda(z,xlookup(A2:A22,z,z,,1))({0.25,0.5,0.75,1,1.25,1.5}))
    

    enter image description here