Macro for rounding (Hint: Alteryx Round() does NOT do this correctly)

[jdminton]

I ran into this issue when a co-worker was having trouble with the Round() expression in the formula tool.

After some research on the community, I found the reason, which seems to be how Alteryx stores the values in a binary format. There was a pretty complicated explanation of why that you can see here if you really want to know.

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

On this other post, https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Rounding-Discrepancies/td-p/50... there was a possible solution that worked most of the time. It's an ingenious way to resolve the issue, but I was confused as to why this remains an issue for Alteryx. 

If you are working with data where the rounding in the last decimal place is not important, you can likely just rely on the Alteryx round formula. I have worked with many CFOs in the past, however, and know that any detectable "error" will bring into question everything else in your analysis. Due to this, I developed a macro, which is really just a formula tool, but I set it up as a macro to be able to share with others who may not want to change the formula tool to meet their needs every time. 

The included macro has a browse tool in it as well to display how it is working and comparing to the results provided by Alteryx's Round() expression. The build design is simple (which rounding should be), but instead of using the number as a number, I break it down into its components.

First, I set the number of decimals to round to. Next I convert the number into text. This way, I can pull the specific digit that the rounding is based upon. Using this digit, I can manually test if it is 5 or more (round up) or less than 5 (round down). This is what you would have to do if manually rounding each number by hand.

After that, I am pulling the part of the number that precedes the rounded digit. This was done by using the Floor() expression of the original amount times 10 to the power of the number of rounding places. So 2 decimal rounding would be times 100, 3 times 1000, and so on. Finally, I add a 1 (round up) or 0 (round down) based on the rounding test times 10 to the power of the number of rounding places. In the data included in the macro, you can see this would add .001 for the amounts where the rounding would round up.

I'm curious if anyone who has experienced this rounding issue in the past could run the macro against the data to see the results. I am pretty confident though that this method will provide 100% accuracy on rounding where other methods seem to fail.

Enjoy! 😀

[mkhtran]

This is a fundamental shortcoming of the underlying double data type used in many computer programs. When doing operations with numbers with decimals, you have to accept that there is some degree of error. When a computer says 0.1 + 0.2 = 0.30000000000000004, you should interpret that as "0.1 + 0.2 is about 0.30000000000000004, plus or minus some error of about 1e-10" (and the more operations you do, the bigger this error range accumulates). So when you're given that the result of an operation was 0.4999999999869943005, the actual answer may be a little higher, or a little lower, and it's impossible to tell which way you are supposed to round. This gives us some options to fix the ambiguity

1. When the value you want to round is right on the boundary within some arbitrary degree (say 1e-10), then instead of rounding, throw an error saying rounding is ambiguous. It's not the macro's job to disambiguate.

2. Use a data type that will never lead to imprecise calculations. This could be converting your data to integers (if the data can be represented as such), or using some arbitrary Fraction data type where all the operations deal with and return exact fraction values.

3. Making sure that the accumulated error is always less than the precision you're calculating with. This scenario is probably the most common, and is why hacks like Round(Round(()) and Round(ToString(()) work. But this is dependent on the operations present, and our base-10 world.

[Still]

This macro doesn't work with rounding for negative numbers

[TeePee]

Happy New Year! Does anyone know if this issue has been addressed by Alteryx yet?  If it hasn't been addressed, does anyone know how we can petition Alteryx to build this macro (or similar) into their backend data handling?  It's irrelevant whether random rounding is *technically* an acceptable way for Alteryx to handle data because, as far as us users are concerned, it is *mathematically* incorrect and there is not a single one of us who would not be very happy with a fix! Thank you

[ChrisTX]

This isn't an Alteryx issue.  It's an issue with how all computers store floating point numbers.

See the attached files with data examples.  The first page of the PDF file has a section for Solutions.

From Alteryx Support, March 2022, Question on ROUND function:

Since this is an issue with all computers, Microsoft explains the issue for Excel users in the following article:

https://docs.microsoft.com/en-us/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-resu...

One of their examples: Enter the following into a new workbook:

A1: =(43.1-43.2)+1

Right-click cell A1, and then click Format Cells. On the Number tab, click Scientific under Category. Set the Decimal places to 15.

Instead of displaying 0.9, Excel displays 0.899999999999999. Because (43.1-43.2) is calculated first, -0.1 is stored temporarily and the error from storing -0.1 is introduced into the calculation.

The only way to correct this is to stick with integer math, as all base-10 integers can be represented correctly in base-2.

Chris

[Qiu]

So this is not related to the the banker’s rounding (also known as round half to even) used in Round() formula?

https://knowledge.alteryx.com/index/s/article/How-To-Round-Numeric-Values-with-the-Formula-Tool-1583...

Round()function in Alteryx Designer uses banker’s rounding (also known as round half to even).
This means numbers that are exactly halfway between two possible rounded values are rounded to the nearest even number.

If you need “round half up” behavior — where.5values are always rounded away from zero (2.5 → 3, -2.5 → -3) — you can achieve it using a custom formula.

For example, If you only need to round to the nearest integer, use this formula: 

[TeePee]

@Qiu and @ChrisTX thanks for your workarounds and insights; really appreciate you taking the time.  However... Excel fixes this issue automatically for end users; as you say @ChrisTX, users would have to manually change Excel settings to get it to do it the Alteryx way.  I expect Alteryx to have this as the default setting too. What would ever be the business case for the current way Alteryx does it?  It's bizarre... surely whether it's technically right/wrong is irrelevant; there are fixes (thanks guys) and to me it's on Alteryx to add this default behaviour, not end users.   

[Gaurav_Dhama_]

Hi all,

How numbers work in designer are different from how we would normally assume they do. Following two blogs cover some concepts of how numbers are interpreted and used in Designer, though it may not directly answer your question, it may give some background how they work.

• How Numbers Work in Designer: Decimals, Rounding T... - Alteryx Community
• Handling Unusually Large Numbers in Alteryx Design... - Alteryx Community

[ChrisTX]

Alteryx doesn't have a "true value" and a different "display value".

Depending on a cell format, Excel may display 0.9, or the "true value" of 0.899999999999999

This may be what you mean "Excel fixes this issue automatically".

Even if Excel displays 0.9, in a calculation Excel will use the "true value" of 0.899999999999999

So the user-friendly display value of 0.9 may be a little deceptive, because the "true value" will be used in calculations.

Chris