Rounding is quite challenging. It's not specifically an issue with Alteryx. It's how computers represent floating point numbers. Details below.
==================
From Alteryx Support 3/22/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.
==================
Examples:
Round(1.25, 0.1) = 1.3 Correct: rounds UP as expected
Round(1.575, 0.01) = 1.58 Correct: rounds UP as expected
Round( 1.255, 0.01) = 1.25 Incorrect. Should round UP to 1.26
==================
Avoid these proposed solutions, which do not always give the correct result
multiply by 10, use the FLOOR function, then divide by 10
FLOOR([MyNumber] * 10) / 10
multiply by 100, use the FLOOR function, then divide by 100
FLOOR([MyNumber] * 100) / 100
Round([MyNumber] * 10, 1) / 10
Round to 1 extra decimal place before rounding to the level of precision you are looking for. To round to 1 decimal place, first round to 2 decimal places, then round to 1 decimal place.
Round(Round([MyNumber], 0.01), 0.1)
See https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Rounding-problems/td-p/486194
If you want to round to an integer, you can change the data type with a SELECT tool and it will round to the nearest whole number.
If you want to round to a number of decimal places, you can use a Fixed Decimal data type and change the number after the decimal to however many places you would like
Use a SELECT tool with configuration set to data type Fixed Decimal and size 19.1
For one value in my data set, "Type:Fixed Decimal and Size 19.1" unexpectedly rounded the value UP.
All numeric values get converted to data type Double on entry to the Formula tool, so Fixed Decimal gets converted to a Double and falls afoul of the inexact representation problem.
==================
Solutions
round to 1 decimal place
FLOOR(Round(Round([MyNumber], .0001), .1) * 10 ) / 10
Recommended for Math operations:
- round your number to 2 decimal places first
- Or in some cases: round to 4 decimal places, then round again to 2 decimal places
round to 2 decimal places
FLOOR(Round(Round([MyNumber], .0001), .01) * 100 ) / 100
round to 1 decimal place
ROUND([MyNumber] + 0.00000001, 0.1)
To consistently be able to round values that end in xxx5, add a small amount to each number before rounding. The small amount should be several orders of magnitude smaller than what you round to, but several orders larger than the internal representation.
round to 2 decimal places
ROUND([MyNumber] + 0.00000001, 0.01)
==================
Chris
