Min and Max of a Value Based from the result of a Formula

[minenskie]

Hi guys,

I just need some help what will be the best formula to put.. I tried my best but no luck.. Here's what I want to achieve.


A1 = Has the Given Data
B2 = to perform the formula and return the condition below
If the result is equal of less than 150, the result should stay at 150 as minimum.
If the result is equal or higher than 900, the result should stay at 900 as maximum.

Formula
(A1*3%)/2

Exceed Max
65,000 * 3% = 1,950 / 2 = 975 (should be replaced by 900 per the condition created)

Below Min
7,000 * 3% = 210 / 2 = 105 (should be replaced by 150 per the condition created)

Else
If the condition aren't meet, the value should return based on the formula
18,000 * 3% = 540 / 2 = 270 (GOOD)


Thanks in Advance!

[Limor_OP]

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[PaulM100]

Most likely =if(((A1*3%)/2)<=150,150,if(((A1*3%)/2)>=900,900,(A1*3%)/2))

[minenskie]

Most likely =if(((A1*3%)/2)<=150,150,if(((A1*3%)/2)>=900,900,(A1*3%)/2))

Thanks Paul will try that.

[minenskie]

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

Thanks for the advice..

[bebo021999]

B2:

=max(min(a1*3%/2,900),150)

[minenskie]

Hi Paul and Bebo..

Both are working. BIG thanks!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[minenskie]

Thanks AliGW. Will do