How to Round Numbers in Alteryx

Numbers can be rounded in Alteryx with a Round() formula added to the Formula tool. It’s quick and easy but the syntax can be confusing because you have to define the precision of the rounding instead of the number of decimal places you want to round to like you would in other software such as Microsoft Excel.

Numbers can be rounded in Alteryx by changing data types or with the Round function. Other functions can round up or down.

Before diving into how to round numbers in Alteryx it’s important to point out that rounding can only be performed on numeric data types. Even if a column contains only numbers there are times that it is defined as a string (text) data type that can lead to errors when trying to apply a rounding formula.

Let’s jump in!

Only Numeric Data Types Can be Rounded

This sounds incredibly obvious, but it’s one of the most common reasons that a rounding formula doesn’t work as expected or a formula error occurs. If you attempt to round a field that is not one of the following numeric data types you’ll receive an erorr, “Type mismatch. String provided where number is required.”

  • Byte
  • Int16
  • Int32
  • Int64
  • Fixed Decimal
  • Float
  • Double

To avoid the error, change the data type of the target column prior to entering a Formula Tool using a Select Tool.

You can read more on the nuances of Alteryx data types on the official Alteryx help guide. As a general rule we default our data types to Double because they work similarly to how numbers work in Excel. However, there are many great reasons to use other data types, such as optimizing your workflows for efficiency and speed.

How to Round to 2 Decimal Places in Alteryx

To round to 2 decimal places in Alteryx, add a Formula tool from the Preparation section of the Alteryx Toolbar. Connect it to your workflow, and either select an existing column or add a new column. Use the Round formula with the following Syntax: Round([Column Name],.01)

The second part of the formula, where it defines .01 is the precision of the rounding instead of the number of decimal places to round to like you would in Excel or other programs.

Example of using a formula to round to 2 decimal places in Alteryx

Another way of thinking of the round formula is that you are giving the formula an example of how many decimal places you want to see like giving it a template.

How to Round to 4 Decimal Places in Alteryx

To round to 2 decimal places in Alteryx, add a Formula tool from the Preparation section of the Alteryx Toolbar. Connect it to your workflow, and either select an existing column or add a new column. Use the Round formula with the following Syntax: Round([Column Name],.0001)

Example of using a formula to round to 4 decimal places in Alteryx

You can add as many zeros as you want to the decimal place precision specifier when using the Round formula in Alteryx.

Rounding by Changing Data Types

Another way you can round numbers in Alteryx is by using a Select tool and changing the data type. A double data type will have continuous decimal places used for calculations, but converting it to an Int or Integer data type will round to the nearest whole number. A FixedDecimal data type allows you to define the precision.

In the example below, we’ve added a Select Tool to change the data type of our sales column to FixedDecimal and defined it with a size of 19.2. This means that the value will be a total of 19 characters, including the decimal point and go out to 2 decimal places.

A size of 8.2 would have 8 total characters, including the decimal point and have 2 decimal places.

Changing a data type to FixedDecimal in Alteryx to round values to 2 decimal places

Because of the added precision that comes with a FixedDecimal and the variability of numbers that can occur in many data sets we would caution people to avoid this method unless they are positive that the size of the defined number type won’t be exceeded as it can lead to calculation errors.

Rounding Up and Down in Alteryx with CEIL and FLOOR Formulas

To Round Up in Alteryx, add a Formula tool from the Preparation section of the Alteryx toolbar to your workflow and use the CEIL() formula. Rounding down uses the FLOOR() formula. The syntax is: CEIL([Column Name], 50) where 50 is the nearest multiple the formula will round up to.

These formulas are similar to the MROUND formula of Excel, and can only be used to round up or down to the nearest whole number.

The example below shows both formulas in use to round values up or down to the nearest interval defined in the formulas.

Example of how to use the CEIL and FLOOR formulas in Alteryx to round up or down.

If you need to round up or down based on decimal values, you can add multiple steps to a formula tool, where you can first multiply it by 100, 1000 etc. as needed, round it up or down, then divide the new rounded value to get it back to the initial number of decimal places needed.

Conclusion

Rounding in Alteryx can be done with data types or with formula tools. Our preference is to use the Round formula inside of a Formula tool as it is the most familiar for a majority of Alteryx users. You can alternatively use data type changes to adjust how numbers are rounded but be aware of the nuances of each data type as they can round unexpectedly when your data changes and lead to errors.

Rounding up and down is possible with the FLOOR and CEIL formulas. They round to the nearest multiple that’s defined in the formula. Rounding up or down to a decimal is possible by first multiplying the value, applying the formula, and dividing it back again.

Scroll to Top