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

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/89086

On this other post, https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Rounding-Discrepancies/td-p/501512 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! 😀

Rounding Macro.yxmc

Best way to round.yxmd

Best way to round.yxzp

Workflow

Reporting

Output

Tips and Tricks

Custom Tools

Macros

Common Use Cases

Best Practices

Bug

Preparation

Expression

Accepted answers

All comments

ArnaldoSandoval

Hi @jdminton

I downloaded your workflow but it does not load properly, check the screenshot below:

Then looking at the workflow's XML you are referencing the macro from a local folder of your environment, I suggest you package your demo to work properly so it wont deter other for trying it.

BTW: I like the macro,

Best regards,

Arnaldo

jdminton

Thanks @ArnaldoSandoval . I had forgotten it would unlink the macro from the original workflow. I added the packaged workflow now. The macro itself was what I wanted to share though. The workflow was included to show how the macro would appear in the workflow. Thanks for the assist.

ArnaldoSandoval

Hi @jdminton

It worked from the package 👍 but I don't like its icon (just kidding)

Arnaldo

fn-01.png

fn-02.png

jdminton

@ArnaldoSandoval 😂

caltang

Thanks for raising this @jdminton ! I always brute force with CEIL / FLOOR and it's quite lengthy. ROUND() doesn't always cater to what I need as well.

jdminton

I never knew this existed as an issue until my co-worker noticed it! I wonder how many other methods have been used to get around this in the past?

jdminton

I realized when I went back to my 2022.3 version of Alteryx, that the macro might provide a message to users not on the latest Alteryx version. I don't think there's any impact to the execution, but here is a 2022.3 version of the macro for those not upgraded yet.

Rounding Macro.yxmc

mkhtran

The edge case (where this macro does rounding "wrong") is when the underlying value is actually correct.

Often the underlying value has a higher precision than the intended calculation, so the display value (which is rounded to a degree) ends up being the "correct" value that you want to round based off (which this macro does). E.g., I want to round 0.1 + 0.2 which the computer calculates to be 0.30000000000000004. This is displayed as "0.3" which turns out the be the correct value.

But the other situation is that the underlying value is accurate. That I truly want to round the value 1.024999999999999911182158029987476766109466552734375 which ends up being displayed as "1.025". In this case (when rounding to 2 decimal places), Alteryx's Round() is correct, whereas this macro rounds an already rounded value, and ends up wrong.

jdminton

@mkhtran Great catch. I'm wondering now if the values provided that "don't round correctly" have this issue. Do you know of any way to display the data in full or at least more than the handful of digits showing in the record? Also, I'm curious why Alteryx does not identify that value as having been truncated as it sometimes does.

Although going back to it again, I typed the numbers in so they definitely don't have the issue of being a longer number truncated for display. So this does truly sound like an edge case. I'm going to explore fixing for this in the macro. Thanks!

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-result

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

ROUND function - summary of issues and solutions.pdf

ROUND function examples.yxmd

Output - ROUND function examples.xlsx

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-1583460195020

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.

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703