# Min and Maximum values

Hi Experts

Need some help with a scenario that has come up.  There is a formula that has worked for many years but now the company wants to change the criteria and I can't get it to work under the new scenario.

Column G==Adjusted Amount  (Recommended new value by our system) (Example: 200)

Column I==Max Change Allowed  (This is the amount I need to calculate)

Column J==Current Amount  (The current amount that is in the system)  (Example: 300)

Column E==Frozen Amount (Maximum amount of value allowed)

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
=IF(ISERROR((LOOKUP(J4416/MIN(E4416,G4416),{0,0.9,1.1;0.9,1,1.1})*MIN(E4416,G4416))),0,(LOOKUP(J4416/MIN(E4416,G4416),{0,0.9,1.1;0.9,1,1.1})*MIN(E4416,G4416)))

This is in column I

This is the formula that we've used.  What I need to change is that we need to look at the value in column J and if the difference between J & G is 10% more or 10% less than use the 10%.  If it is less than 10% more or less than 10% less than the difference use this amount.  The rub is with column E.  If column E has a value you can make a change up to the amount in E.  If it is recommending going over the value in E then use E.

What it does currently is to look at the value of column G and calculate 10% over or under based on that amount.

I've tried to make this simple and easy to follow at least it is to me.  Any help my friends would be greatly appreciated.

Thanks

spudmcc (Andy)
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mechanical EngineerCommented:
Andy,
If I am understanding you, the following formula should do the trick:
=MIN(E4416,LOOKUP(J4416/G4416,{0,0.9,1.1},{0.9,1,1.1})*G4416)

If this formula is not working, please post several sets of sample values and desired results.

0
Author Commented:
I've created a sample document and gave examples of what I am trying to accomplish.  I hope this makes more sense.

Thanks again for your patience and help!

Andy
example.xlsx
0
Mechanical EngineerCommented:
Andy,
Thanks for making that sample workbook!

I believe the following rules apply:
If G2 < 90% J2, use 90% J2
else if G2 > 110% J2, use 110% J2
else use G2
If E2 is not 0 and is less than amount calculated above, use E2
If that understanding is correct, you can use a formula like:
=MIN(IF(E2=0,"",E2),IFERROR(1/(1/(LOOKUP(G2/J2,{0,0.9,1.1},{0.9,0,1.1})*J2)),G2))

In the above formula, the LOOKUP returns 0 if G2 is between 90% and 110% of J2. By taking the reciprocal twice, the formula returns a DIV/0! error, which is trapped by IFERROR, which then returns the value of G2.

If G2 is < 90% of J2, or more than 110% of J2, then LOOKUP returns 90% or 110% of J2. Taking the reciprocal twice does not return an error, so the formula returns 90% or 110% of J2.

The IF tests whether E2 is 0. If so, it returns an empty string, which is ignored by MIN.

Note that the results in rows 5 and 8 differ from what you said were the correct values. If my formula is still wrong, the rules stated at the very beginning need to be tweaked--please advise.

exampleQ28267849.xlsx
0

Experts Exchange Solution brought to you by