How do I calculate within a range?

J

Jdd561

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
 
J

JMB

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)))
 
Top