Search code examples
google-sheetsgoogle-sheets-formula

Is there a function that prevents a integer to be bigger or smaller than values (range)?

I want to do something like this:

=MYFUNC(my_number, limit_down, limit_up)

If the number goes out of limits, bring it to the closest limit and some examples, what I want:

=MYFUNC(1, 4, 8) ==>  4
=MYFUNC(5, 4, 8) ==>  5
=MYFUNC(6, 4, 8) ==>  6
=MYFUNC(8, 4, 8) ==>  8
=MYFUNC(9, 4, 8) ==>  8
=MYFUNC(0, 4, 8) ==>  4

I can use MAX and MIN for this task, but maybe there is a more elegant way?

Solution

  • I know you state that you've already used MIN & MAX, but have you tried nesting them? You can't get a more compact expression than:

    =min(max(number,lower_limit),upper_limit)