Array-Safe Clamp Value in Google Sheets

Question

I'm trying to clamp a value between a minimum and maximum value.

If this were a formula, I'd expect it's usage to be =CLAMP(value, min, max).

From what I know, there's two approaches, nether of which I like.

1) IF / ELSE

Use =IF(value<min, min, IF(value>max, max, value)). This is my current approach, but it has a huge downfall: the replication of value. In my use case, my value is not a short and sweet cell reference, it is instead a complex formula, which has nested "clamp" like features within it as well.

Because I want a single-cell solution, this requires that my formula be nearly 8,000 characters long. It could be made significantly shorter (about 9 times shorter in my case) without the replication of value.

2) MEDIAN I know of the Median Trick: =MEDIAN(value, min, max). Typically, this is what I use, but it too has a huge downfall: it doesn't play nice with Array Formulas (which I'm using).

All of my data is in a table. As such, everything is packed nicely in a grid. However, if you prompt the Median Function to find the Median of a grid, it will do just that, which isn't what I want.

Let's say that I wanted to add up the medians of three rows of data:

0 1 2 3 4
1 2 3 5 7
1 2 4 9 16

Now, I want the median of each row, individually:

0 1 2 3 4 -> 2
1 2 3 5 7 -> 3
1 2 4 9 16 -> 4

And I want to know their sum:

2 + 3 + 4 = 9

So I'll just use =ARRAYFORMULA(SUM(MEDIAN(A1:C5)), right? Wrong.

Instead, the median function returns 3, because that's the median of all 15 values. So clearly, that's not what I want.

Conclusion

I need an Array Formula-Safe method for clamping a value between a minimum and maximum without repeating the value, minimum, or maximum.

If you have a solution that does repeat, but it is better than the IF / ELSE option, I'm all ears. My minimum and maximum are just direct cell references, so repeating them wouldn't be as bad.

Note: I've tried doing this in Excel too, with no avail.

Answer 1

You can achieve the effect of CLAMP(value, min, max) with

=IFERROR(max-SQRT(max-IFERROR(SQRT(value-min)^2+min,min))^2,max)

which is compatible with ARRAYFORMULA and uses value only once. Explanation:

1. SQRT(value-min)^2+min returns value if value>=min, and gives an error otherwise.
2. IFERROR returns the first argument, unless an error occurred in it, which case it returns the second
3. Thus, IFERROR(SQRT(value-min)^2+min,min) clamps the value from below by min.
4. The other steps do a similar thing with max.

The above formula can be simplified, reducing the references to max and min from three to two of each:

=max-IFERROR(SQRT(max-min-IFERROR(SQRT(value-min)^2,0))^2,0))

Answer 2

In general, this can be done simply using MAX and MIN, and written in this way, min and max appear on either side of the value just like min < value < max.

=MAX(min, MIN(value, max))

Alt (to clump variables together for readability).

=MIN(MAX(min, value), max)

EDIT:

But to specifically answer your question about use with arrays, since min functions as the min of all values in the array, you could approach this pretty cleanly by adding your own function.

Just go: Tools -> Script Editor And make it so if your input is an array, it returns and array and clamps each value, like so:

function clampArray(val, low, high) {
  return Array.isArray(val) ?
      val.map(row => row.map(cell => Math.min(Math.max(low, cell), high))) :
      Math.min(Math.max(low, val), high);
}

In case you need an explanation, the script editor uses javascript. This is using the ternary operator (expr)? trueResult: falseResult; in the same way you use =IF(test, trueResult, falseResult)

also this is using the .map prototype function, see the documentation. Then you can just use it exactly how you wanted to in your question.

=ARRAYFORMULA(clampArray(A1:C5, minVal, maxVal))