Google Sheets – Array-Safe Clamp Value

google sheets

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.

Best Answer

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))
Related Topic