Rounding Discrepancies

[aaronindm]

I am seeing some strange results when rounding - see attached workflow.

You can see that Alteryx is storing some inexact (and slightly less) version of those numbers.  When I attempt to round those numbers to the 0.01 spot, I get unexpected results.

Does anyone have an explanation and/or foolproof method to deal with this issue?

Orig Entered        Expected       Alteryx

1.255                    1.26              1.25

4.255                    4.26              4.26    (only correct one)

2.005                    2.01              2.00

2.255                    2.26              2.25

[Thableaus]

Hi @aaronindm 

This response from KevinP actually explains this behavior pretty well.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Alteryx-Odd-Behavior/td-p/89086

It's due to how binary values are stored and computers perform these calcultations with decimals.

Cheers,

[echuong1]

It has to do with the way Alteryx stores values and internal representation. When rounding to a fixed decimal, Alteryx accurately rounds to the nearest number based on the internal representation of the number. (For example, if 74.876925000 is internally represented as 74.8769249999999999955, converting to a string with five significant digits results in 74.87692, not 74.87693.)

https://help.alteryx.com/current/Reference/DataFieldType.htm

[CharlieS]

Dumb answer: Round twice.

Round(Round(ToNumber([Field1]),0.001),0.01)

Edit: That doesn't always work. I'll try again.

[aaronindm]

I did see that, but not sure I can pull a solution from that.  I am working with interest rates, and 1 basis point on hundreds of billions can make a difference.

Perhaps said a different way, is there a fool proof rounding function (or series of embedded functions) that replicates what Excel is doing?  Excel's formulas seems to always return the expected result.

[aaronindm]

Still produces incorrect answer on one but not the other

[fmvizcaino]

For some reason, it seems to work this way.

Round(Round([Field1]*100,.1), 1)/100

[danilang · Accepted solution]

Hi @fmvizcaino , @aaronindm et al

Time for some Rounding Judo

The multiple rounding approach, Round(Round([Field1]*100,.1), 1)/100, will work most of the time, but you'll always find an edge case that will break it. 

A better approach is the use the weakness of the double field format against it. 

In your examples, you see that the values are consistent to about 14 decimal places.  This is just about the limit of accuracy of the internal floating point representation.  If you add an epsilon value of 0.0000000000001(13 decimals) to your values before rounding you force the value to be marginally larger so that rounding operation will return the correct results. Since Epsilon is so much smaller than the value itself, it never affects the materiality of the operation, i.e the values will never end up so large that it rounds up to the next decimal. 

Here are the results of applying this technique to multiple values, including some where the internal representation is actually a little larger than the value.  2.555->2.5550000000000001598721...

The formula I used in the RoundedWithEpsilon columns is

Round([Field1]+0.0000000000001,.01)

The last 2 entries show that the epsilon is small enough to not force the rounding up to the next decimal.

This method will work when rounding all the way down to about 12 decimal places.  For values where you need more than 12 decimals of rounding accuracy, you'll need to roll your own routines 

This also doesn't work to give you Banker's Rounding where 0.xx5 is rounded to the nearest even decimal 

Dan      

[fmvizcaino]

@danilang , that is great!!!!! 

My attempt was only to make it work without any knowledge whatsoever! hahahaha

[aaronindm]

@danilang thank you this worked perfectly and matches Excel results for 100,000+ interest rates I am working with.