6

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.

  • Title "Clamp Value in Google Sheets" is misleadingly general. The answer to that question is simple: "MIN(MAX(value, minValue), maxValue)". However, this question seeks a subtle Array Formula safe method for clamping. – Vincent Scheib Jun 15 at 16:04
  • I've updated the title to scope the content a little better. – Boom Jun 15 at 17:37
  • I've also updated my answer to address the array problem. – johnb003 Jun 15 at 19:44
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))
| improve this answer | |
  • 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 '19 at 2:43
  • while clever, sqrt and ^2 are expensive operations, and on a large array this would slow things down. – johnb003 Jun 15 at 19:40
8

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))
| improve this answer | |
  • 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
  • I edited it to add an answer for working with arrayformula – johnb003 Jun 15 at 19:38

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.