Google-sheets – Google Sheets If than statement doing whatever it wants

formulasgoogle sheetsworksheet-function

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?

Best Answer

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.

Related Topic