cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

SOLVED

## Rounding Discrepancies

Meteor

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

Alteryx Certified Partner

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,

Alteryx

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

Alteryx Certified Partner

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

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

Meteor

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.

Meteor

Still produces incorrect answer on one but not the other

Alteryx Certified Partner

For some reason, it seems to work this way.

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

Highlighted
Nebula

Hi @fmvizcaino , @aaronindm et al

Time for some Rounding Judo

The multiple rounding approach, Round(Round([Field1]*100,.1), 1)/100will 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

Alteryx Certified Partner

@danilang , that is great!!!!!

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

Meteor

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

Labels