4

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.

4

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))
  • Now that I've read this brilliant answer, of course it makes mathematical sense. I don't need to clamp to a max value, but I am using array formulas and wanted to clamp the lower bound to zero. So now I just have =ARRAYFORMULA(IFERROR(SQRT(value)^2,0)). Works exactly as needed without repeating myself, thanks! – monkey0506 Jan 12 at 2:43
2

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))
  • This is the perfect answer for the question stated in the title, which I think is what most of 1000+ visitors to this page are after. So +1 for that, even though your formula doesn't work for arrays. – user135384 Nov 28 '17 at 22:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.