Round to two decimal places

[jagjit_singh]

Hi,

I have the below table and want to round Rent Balance to two decimal places and the result should match as in the Required column. I tried using the Round function but that does not give the desired result. Any ideas how I can use a formula or tool to achive this please.

Rent Balance	Required
356.4785714	356.48
356.4785714	356.48
356.4785714	356.48
329.0571429	329.06
301.6357143	301.64
274.2142857	274.21
246.7928571	246.79
219.3714286	219.37
191.95	191.95
164.5285714	164.53
521.0071429	521.01
521.0071429	521.01
493.5857143	493.59
466.1642857	466.16

Thanks

Jag

[MikeN · Accepted solution]

Hi @jagjit_singh, you can achieve this a couple of ways.

One, you can use a select tool and recast the type as a fixed decimal set to "19.2".

Two, you can use a formula tool and create a new field, referring to the original, again with a fixed decimal set to "19.2", where the 2 refers to the 2 decimal points.

See attached.

[dataMack]

Rounding is one of those things that sounds simple but can be complex because people mean different things when they say "rounding".  Ken Black wrote a nice piece last year describing some of the different approaches and how to execute the rules in Alteryx:

https://3danim8.wordpress.com/2015/06/24/rounding-to-the-nearest-dollar-using-alteryx/

[MikeN]

Great points, Jason. In my haste to answer the question (late at night), I missed the rounding part of the question, and focused on the 2 decimal points.

I believe my solution will get the answer, but the post you referred to is a great place to learn more about "all things rounding".

[jrosenstein]

Are you sure you you used the round function correctly? Its not like excel which you type number of digits, it ask for multiple. Therefore:

round([Rent Balance],.01)

[djcho]

Hello!

FixedDecimal works well in a case like this. But what if you're bringing in a large amount of data with many columns that you want to cast as fixed decimal set to "8.3"?

In select, I can select multiple columns and change data type to FixedDecimal, but they will all be set to the default "19.2". Clicking the Size field and pasting or typing in "8.3" for every Column field gets tedious.

Is there a solution that would automate changing the data types of a large number of column fields to FixedDecimal set to "8.3"?

[RCL1]

1. Transpose the desired columns

2. Make the decimal rounding change to the data field "value"

3. pivot the data back via Cross Tab

[Daniel_Aguirre]

@MikeN , Clear, precise and relevant. Thanks!

[JBLove]

Regarding the question: Is there a solution that would automate changing the data types of a large number of column fields to FixedDecimal set to "8.3"?

The answer is yes.  There is a multi-field formula tool.  When configuring this tool you will need to:

- Select the fields to be bulk updated

- Check box related to Change Output Type to 

- Select the data type FixedDecimal and provide the desired precision in the Size text box

- Enter the expression: [_CurrentField_] 

[Gapo]

Thanks for asking this question 🙂