Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

How To: Round Numeric Values with the Formula Tool

ChristineB
Alteryx Alumni (Retired)
Created

Whether for mathematical or formatting purposes, you may want to round your numeric data. Depending on the type of rounding you're looking to do, Alteryx has a solution for you!



2019-02-28_17-13-24.png



To demonstrate rounding methods, we’ll use some sample data for distances that a truck traveled between two locations([Total Distance]).As you can see, the data includes 11 values after the decimal point. That level of precision is probably unnecessary for our analyses. So, to better represent this data, let's 1) round it to the nearest mile, 2) round to the nearest 100 miles, 3) round to two decimal places and 4) round it upto the nearest mile.



1)Round to the nearest mile (integer):

The current data reflects the distance a truck traveled from one location to another. We want to round this data to the nearest mile. To do this, we’ll use the Round function (found under the Math category of functions in the Formula Tool). The Round function requires two parameters: the data you want to round, and the multiplier to which you want to round. In the case of rounding to the nearest integer, we will simply round to the nearest multiple of 1.

2019-02-28_17-14-33.png

2)Round to Nearest 100 miles

If, say, we wanted to round to the nearest 100 miles, we’d simply adjust the multiplier to 100:

2019-02-28_17-15-20.png

3)Round to Two Decimal Places:

If we needed to round to a certain number of decimal places (let’s say 2 decimal places*), we can change the multiplier to 0.01 :

*Note that the data type has been set to Double for accommodating decimal places in the rounded results.

2019-02-28_17-15-20.png

4)Round-Up to the Nearest Mile (Integer):

You may find yourself with the need to round up (not necessarily to the nearest integer). In this case, we’ll need to turn to the Ceil function (also under the Math category of functions). The Ceil function will return the smallest integer greater than or equal to a value [x] or expression [Value]/[x]. So, to use an example from the sample data, Truck 1 traveled 567.712005 miles. To round up to the nearest mile, we’ll use the expression CEIL([Total Distance]/1) Because 567.712005 divided by 1 is itself, the smallest integer that is greater than or equal to 567.712005 is 568.

2019-02-28_17-16-28.png

Interested in other discussions on rounding? You may want to check out these Community discussion threads on rounding up, rounding to a certain number of decimal places, and rounding to a multiple of (insert value here).

Attachments
Comments
lindsey
5 - Atom

I found that I needed to also ensure that the numbers being rounded all have the same number of decimal places, which should be the max needed for any of them for rounding, before rounding, or it produces unexpected results.  

For instance, if I have .8355, .0635, and .936 as 3 Double fields, using Round([_CurrentField_],.001) or changing the data types to FixedDecimal 19.3 both round them to .835, .064, and .936.  You would think, since the 4th decimal place of .8355 and .0635 is 5, they should both round up, but they do not.

 

First, I needed to change the data type of all 3 to FixedDecimal 19.4 to get .8355, .0635, and .9360then use the Round() function (not changing the FixedDecimal length), and finally I got .836, .064, and .936.

Salicea4
5 - Atom

If I have to round multiple columns, do I need to create a formula for each column? or is there a more effective way to do so?

Thanks!

NeilR
Alteryx Alumni (Retired)

@Salicea4 you can use the Multi Field Formula tool.

aaronindm
8 - Asteroid

Adding on to this thread instead of starting a new one.  I have some interest rates (that are being read in as x.xx instead of 0.0xxx) and noticed that Alteryx is doing rounding a bit differently than expected.

 

For example:

1.255 rounds to 1.25 in Alteryx

4.255 rounds to 1.26 in Alteryx

 

Both of these round to x.26 in Excel.

 

I noticed that when I hand enter these numbers, Alteryx actually stores these  as something like 1.254999999999999893418589635984972119331359863281250000000000000000000000000000000000000000

 

There is a definitive rounding formula to eliminiate the 999999999 issue?

 

aaronindm_0-1576076836933.png

NeilR
Alteryx Alumni (Retired)

@aaronindm a little background reading on the issue: Floating point numbers are surprisingly strange

That being said, I can't actually replicate the issue you're having. Perhaps you should post a new thread in Designer Discussions so that you can attach your workflow.

 

 

Sara94
7 - Meteor

great examples thanks! @ChristineB you made it pretty much clear. 

cpinhei7
6 - Meteoroid

Very helpful, thank you!

phdataviz
7 - Meteor

Very helpful; however, your image for example 3 does not show Rounding to 2 decimal places. It is a duplicate picture from example 2. Great post and thanks for sharing :)