trndlr

New Member
Joined
Jan 24, 2017
Messages
4
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


 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,175
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 
Last edited:

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,558
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 24, 2017
Messages
4
Thank you so much for the quick responses and clarifiying. Now everything is more clear for me.
 

Forum statistics

Threads
1,137,151
Messages
5,679,897
Members
419,861
Latest member
AceDaMace

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top