How do I constrain a value to a range in excel?

Question

In C++ I would use boost::clamp for this. Basically I have some excel function

A1*B2+C3+D4

I want to do constrain it to +/- some number, call it X1. The obvious way is this:

MAX(-X1, MIN(X1, A1*B2+C3+D4))

But I want to be able to do this:

CLAMP(A1*B2+C3+D4, -X1, X1)

Does this or something similar exist? I'm just curious - obviously the workaround works, it's just ugly.

Answer 1

This can be done using MEDIAN. MEDIAN picks the middle of the three values, thus effectively restricting the lower and upper limits.

For example say your minimum is 5 and your maximum is 10:

=MEDIAN(5,0,10) is 5

=MEDIAN(5,7,10) is 7

=MEDIAN(5,12,10) is 10