Comparison error while trying to round like Excel

[Enrique]

I have two different but related issues. First: my LabVIEW application is not passing my client's validation because the results in LabVIEW are not exactly the same as the ones my client get when using MS Excel. The problem is with rounding: LabVIEW and Excel round numbers differently and (unfortunatelly) I must round the numbers the Excel way.

Because I need to round after the decimal point, I started using a nice VI I found here at ni.com called "DecimalRoundingLV8.5vi". However, because of the difference between LabVIEW and Excel, I can't use that VI as is. For example, rounding to the third place after the decimal point:

Number to round	LABVIEW	EXCEL
0.0045	0.0040	0.0050

So, my first issue is that one, I need to round like Excel... however, the solution I implemented brought up a weird issue and I am not sure if I am witnessing a bug in LabVIEW.

I modified the decimal rounding VI. Below is an image of the code and I am also attaching it:

First, if the number is negative I am changing it to positive to round. At the end I return the sign.

Like the original VI, I multiply the number to round times 10^x, where x is the place after the decimal point I want to round to, I do the rounding and then divide the result by 10^x.

However, I also multiply the number to round times 10^(x+1) and divide the result by 10 to get the unit as the reminder and to evaluate that number. If the number is less than 5, I round as usual; if not, round toward +infinity.

So far, so good and everything seems to be fantastic. But when I tested this code I found the following weird scenario. For example, rounding to the third place after the decimal point:

Number to round	LABVIEW	EXCEL
0.0855	0.0860	0.0860
0.0856	0.0860	0.0860
0.0857	0.0860	0.0860
0.0858	0.0860	0.0860
0.0859	0.0860	0.0860
0.0860	0.0860	0.0860
0.0861	0.0860	0.0860
0.0862	0.0860	0.0860
0.0863	0.0860	0.0860
0.0864	0.0860	0.0860
0.0865	0.0860	0.0870
0.0866	0.0870	0.0870
0.0867	0.0870	0.0870
0.0868	0.0870	0.0870
0.0869	0.0870	0.0870
0.0870	0.0870	0.0870
0.0871	0.0870	0.0870
0.0872	0.0870	0.0870
0.0873	0.0870	0.0870
0.0874	0.0870	0.0870
0.0875	0.0880	0.0880

Note when rounding 0.0865. LabVIEW got it wrong! However, LabVIEW did not got it wrong for 0.0855 or 0.0875. When I ran the code using the highlight feature, it turns out the problem was the comparison function. It is literally saying that 5<5 is true.

I tested this in LabVIEW 8.2 and LabVIEW 12. I am using Windows XP SP3.

These numbers are not the only cases with this weird behavior. Other numbers are 0.1425, 0.1725, 0.1745, and more. I don't see a pattern (or a reason!!)

Can somebody please test my code and report if you experienced the same behavior?

[johnsold · Accepted solution]

Enrique,

This is a fundamental issue with using finite binary representation for numbers.  Event though you display and think of the numbers as decimal representations, the computer stores them in a binary format.  Numbers like 0.0045 or 0.004 cannot be represented exactly in the binary format used by LabVIEW.  Place a numeric control on a new VI. Set the display format to show ~ 20 significant digits.  Type in 0.004 or 0.0045 then hit enter.

0.0045 -> 0.0044999999999999996600

The correct rounding for that value is 0.004.  Of course typing 0.004 yields 0.004000000000000000080.

I do not have Excel so I do not know what it does, but it will have the same issue with approximate representations of numbers.

There are many posts on the Forums about the number representation issue.

Also, this is the reason that exact equality comparisons on non-integer datatypes should be avoided or used with great caution.

Lynn

[RavensFan]

It is not a bug with LabVIEW.

Put an indicator on that wire and set it for 15 digits of precision.  You will see that due to the limititations of floating point numbers that it is actually slightly less than 5, that is why the comparison is true.

Your original number is not .0865, it is actually 0.0864999999999999936.  So rounding down to .086 is actually correct.  You can't exactly represent 0.0865 with a double precision floating point binary number.

I think you might be better off if after doing the multiplication, you coerce the value to an integer, then proceed with the rounding process from there.

[Enrique]

Excellent. Before performing the comparison I converted the number to an integer and the problem went away. Thanks!