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.