community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx Designer Knowledge Base

Definitive answers from Designer experts.

How To: Round Numeric Values with the Formula Tool

Sr. Instructional Designer
Sr. Instructional Designer
Created on

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 up to 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
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.

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!

Sr. Community Content Manager
Sr. Community Content Manager

@Salicea4 you can use the Multi Field Formula tool.