How do I calculate within a range?

[Guest]

B5 100000
B6 150000

I'm trying to work out a ratio of the above data that fits within specific
limits. I need to have B6/B5, but only if the result is greater than .8 or
less than 1.2. If this ratio is less that .8, I want .8 to be the answer.
If the result is greater than 1.2, I need 1.2 to be the answer. In english,
my IF statement is below

IF (B6/B5)>.8 and <1.2, then B6/B5
IF (B6/B5)<.8, then .8
IF (B6/B5)>1.2, then 1.2

 

[Guest]

=IF(AND(B6/B5>.8,B6/B5<1.2,B6/B5,IF(B6/B5<.8,.8,IF(B6/B5>1.2,1.2)))

Dave

 

[Guest]

=MIN(MAX(A1/A2,0.8),1.2)

 

[Guest]

using your cell references
=MIN(MAX(B6/B5,0.8),1.2)

 

[Guest]

You will need to account for B6/B5 = 0.8 or =1.2, or it returns FALSE.

=IF(AND(B6/B5>=0.8,B6/B5<=1.2),B6/B5,IF(B6/B5<0.8,0.8,IF(B6/B5>1.2,1.2)))

 

[Dana DeLouis]

Maybe another way:

=MAX(MIN(B6/B5,1.2),0.8)