An Elegant Solution to Setting Evaluation Constraints

[jase71ds]

This isn't a question - just wanted to share a solution I came up with. Others have probably come up with the same solution, but I was pleased with myself for figuring it out on my own. Plus, it was fun!

If I want to constrain a formula output, say between +100% and -100%, one popular way to do it (there are many ways) is:
= IF( A1/B1 > 1, 1, IF( A1/B1<-1, -1, A1/B1 ))

This is not idea because you are potentially evaluating the formula three different times. And the odds are that your formula will be much messier than "A1/B1" so the computational overhead (and the formula ugliness) will grow.
You could use LET, and it would be better computationally, but still not as elegant (in my opinion).

So I considered MINs and MAXs and came up with this:
= MAX( MIN( A1/B1, 1 ), -1 )

In this example, A1/B1 is evaluated only once, and the output is constrained within the parameters of -100% and +100%.
It's rather elegant looking and far less computationally intensive for complex evaluations.

If I wanted to constrain within 0% and 100%, it would be:
= MAX( MIN( A1/B1, 1 ), 0 )

Or constrain within 500 and 10,000:
= MAX( MIN( A1/B1, 10000 ), 500 )

I remember it with this phrase: "Max Min, put your formula, Max Min"
That's always enough to jog my memory.

As I said, I'm sure others figured this out long ago, but it was fun for me to "discover" on my own.
Anyway, maybe this will be of use to someone.

Jase.

 

[KenU]

Another method for consideration (for your first scenario):

=MEDIAN(A1/B1,-1,1)

This will avoid the pitfalls of getting things out of order.

Hope that helps.

Regards,
Ken

 

[jase71ds]

Another method for consideration (for your first scenario):

=MEDIAN(A1/B1,-1,1)

This will avoid the pitfalls of getting things out of order.

Hope that helps.

Regards,
Ken

WOW!
Even more elegant!!
Why is this not used more? When I googled "Excel set constraints" I was just greeted with lots of IF or IFS functions.
Thanks so much!!!! I'm using this!

Jase.

 

[Eric W]

For what it's worth, I read an article by MrExcel comparing the MAX(MIN with the MEDIAN formulas, and a little surprisingly, the MAX(MIN is a tiny fraction of a second faster than the MEDIAN. Not enough to worry about unless you're doing a bar bet. I still like the MEDIAN better for the brevity and elegance. I can probably find that article in the archives if you're interested.

 

[jase71ds]

Eric,
Regardless of a tiny calculation overhead, I'm with you - I'll be using MEDIAN for the brevity. Especially since I have some rather large formula that need all the brevity they can get!

 

[jase71ds]

Another method for consideration (for your first scenario):

=MEDIAN(A1/B1,-1,1)

This will avoid the pitfalls of getting things out of order.

Hope that helps.

Regards,
Ken

It's been 2 months since this post, and I just want to say how many times I've used this method to constrain outcomes. So much more elegant than all the IF/THENs I would use, and so much more elegant than the MAX/MIN I "discovered".

If there are any downsides to using MEDIAN to constrain an evaluation's outcome, I've yet to stumble upon it.

Anyway, thanks again @KenU for such an elegant and useful outcome-constraining method!!

Jase.