Solved

# Min and Maximum values

Posted on 2013-10-15

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)