8

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.

3
  • 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. Commented Jun 15, 2020 at 16:04
  • I've updated the title to scope the content a little better.
    – Boom
    Commented Jun 15, 2020 at 17:37
  • I've also updated my answer to address the array problem.
    – johnb003
    Commented Jun 15, 2020 at 19:44

3 Answers 3

5

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))
2
  • 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
    Commented Jan 12, 2019 at 2:43
  • while clever, sqrt and ^2 are expensive operations, and on a large array this would slow things down.
    – johnb003
    Commented Jun 15, 2020 at 19:40
9

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))
2
  • 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
    Commented Nov 28, 2017 at 22:43
  • I edited it to add an answer for working with arrayformula
    – johnb003
    Commented Jun 15, 2020 at 19:38
0

I know your question is primarily for Google Sheets, but you also wrote

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


If you are willing to do it in Excel:

An Array-Safe Clamp Value in Excel

You can define your own CLAMP function:

  • In Excel, go to Formulas > Name Manager, and click on "New"
  • Add the following:
    • Name: CLAMP
    • Refers to: =LAMBDA(value;min;max; IF(value<min; min; IF(value>max;max; value)))
  • Now use it in your spreadsheet, also works in array formulas

Sum the median of each row (individually) in Excel

In Excel, use the BYROW formula:

  • To get a vertical array of each row's median: =BYROW(A1:E3, LAMBDA(row, MEDIAN(row))
  • To get the sum of all of them: =SUM(BYROW(A1:E3, LAMBDA(row, MEDIAN(row)))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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