Excel Right Function Dilemma

[trndlr]

Hi all,

I have a confusion with using RIGHT function. I need to create a complicated formula that depending on two digits after comma. I’ve used RIGHT function to do it but the step that you can see below, the result seems like isn’t true. I'm sharing formula with the sample values(not cellIDs ) and 1-0 logic (true/false) to make it more clear.
=IF((RIGHT(ROUND(6.14/5, 2), 2)<40), 1, 0) ,
Result: 0,
It means my logical expression is greater than 40. But when I check the result of the logical expression step by step, I can see all steps are return properly and value of the result is less than 40;
=ROUND(6.14/5, 2)
Result:1.23
=RIGHT(ROUND(6.14/5, 2), 2)
Result: 23
So, definetely 23 is less than 40. But the first formula below, returns 23 is greater. I have no idea about reason of this. Could you help me about this issue?

Thank you

 

[RoryA]

Welcome to the forum.

The result of the penultimate step is actually "23", not 23, and Excel treats text as greater than numbers. You can add 0 to coerce the "23" to 23.

 

[steve the fish]

RIGHT will produce text even if it appears as a numerical value. Text is always greater than a number to excel so this explains your problem. Try coercing the text number into a real number by adding zero to it such as:

=IF((RIGHT(ROUND(6.14/5, 2), 2)+0<40), 1, 0)

 

[trndlr]

Thank you so much for the quick responses and clarifiying. Now everything is more clear for me.