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

i want to do something like this:

=MYFUNC(my_number, limit_down, limit_up)

if number goes out of limits, bring it to 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 taks, but maybe there is a more elegant way?

CodePudding user response：

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)

CodePudding user response：

If you would like to do the conditional checking without using apps script, you can use LAMBDA function like this, or just setup a named function.

=LAMBDA(INT,MIN,MAX,
 IF(INT <= MIN, MIN, IF(INT>= MAX, MAX, INT))
)(my_number, limit_down, limit_up)