Solved

# Min and Maximum values

Posted on 2013-10-15
Medium Priority
230 Views
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)
0
Question by:spudmcc
• 2
• 2

LVL 81

Expert Comment

ID: 39575144
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 Comment

ID: 39575447
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

LVL 81

Accepted Solution

byundt earned 2000 total points
ID: 39575529
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

Author Closing Comment

ID: 39576078
This is spot on!  Thank you so much for all of your time and sharing of your knowledge.   I appreciate this so much.

A
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
###### Suggested Courses
Course of the Month9 days, 8 hours left to enroll