1
B7 = A number

B8 =IF(B7 > 1.25,((B7-1.25)*2)+1.25,IF(B7 < 1.25,1.25-((1.25-B7)*2),"1.25"))

B9 =IF(B8 > 1.5,"1.5",IF(B8 < 0.5,"0.5",B8))

So basically, B7 is whatever number you want. If that number is above 1.25, or below it or equal to it, the number is changed and stored in B8. B9 then bounds the number between 1.5 and 0.5

Now, this works like a charm for basically every single number except 1.25. If I place 1.25 in B7, for some strange reason B9 will wind up being 1.5. I don't see the logic in this. In fact, if I change the number by a tenth up or down, it follows the rules of B8 and B9 correctly. How do I make this work properly for 1.25 to not get increased to 1.5?

1 Answer 1

1

Here are your formulas:

B7 = A number
B8 =IF(B7 > 1.25,((B7-1.25)*2)+1.25,IF(B7 < 1.25,1.25-((1.25-B7)*2),"1.25"))
B9 =IF(B8 > 1.5,"1.5",IF(B8 < 0.5,"0.5",B8))

The problem is that at the end of the formula for B8 if the value in B7 is 1.25 you set the result to a string. Then the formula in B9 compares the string to 1.5 which isn't behaving as you expect. As an experiment change "1.5" in the formula to "Hello world" and B9 will always end up as "1.5" when B7 = 1.25.

These is the formulas you want:

B7 = A number
B8 =IF(B7 > 1.25,((B7-1.25)*2)+1.25,IF(B7 < 1.25,1.25-((1.25-B7)*2),1.25))
B9 =IF(B8 > 1.5,1.5,IF(B8 < 0.5,0.5,B8))

I just removed the quotation marks.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.