cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

## another rounding question

Asteroid

Hi,

I have looked through a few of the rounding questions on here and cant quite figure out the answer....

I have a value 163.575 and I want to round to 2 decimal places, simple I thought with the round function but it does not seem to round this up to 163.58 but instead rounds it down to 163.57.  How can I get 163.5755 to round up to 163.58?  I have the precision set before going into the round function to 3 decimal places so it's not like Alteryx has the figure as 163.574999999 and is displaying it as 163.575 onscreen (or is it and im missing something?)

round([163.575, 0.01)

Atom

Try this.

Best regards

Asteroid

Hi,

So what does/how does changing the multiplier from 0.01 to 0.02 actually do?

Nebula

Hi @craigja

I've seen this before in this post.  It has to do with the floating point representation of .575.  It can't be exactly stored.  If you convert it fixed format  25:20 you get

``163.57499999999998863132``

I know that you said that you specified a fixed decimal with 3 digits, but if you check this post, specifically the reply from AdamR from Alteryx

you find that all numeric values get converted to doubles on entry to the formula tool, so Fixed Decimal get converted to a double and falls afoul of the inexact representation problem.

The only way that I've found to consistently be able to round values that end in ...5 is to add 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([Number]+0.00000001,0.01)``

BTW:  @Dario2k19 your solution rounds to multiples of .02.  It works for .575 giving  .58, but then .585 rounds to .58 as well as opposed to .59

Dan

Highlighted
Atom

Sorry, in a hurry I have put 0.02 instead of 0.01. Although 0.02 gives correct value for that number, it wouldn't give proper rounding on other numbers later on.

Anyway, point from screenshot is that you should set Round([Field name], 0.01) but make sure that field type is double.

Asteroid

That still does not work -

ROUND(167.846, 0.02) = 167.84 and not 167.85

ROUND(168.414, 0.01) = 168.412 and not 168.414

Asteroid

Sorry just seen the latest reply, will change to double precision and see if that works

Bolide

The key is understanding how "mult" works....

Round(x, mult): Returns (x) rounded to nearest multiple of (mult)

If you take 163.575, and you want a maximum of 2 decimal places, the nearest "multiple" of "1" is 163.57

If you take 163.575, and you want a maximum of 2 decimal places, the nearest "multiple" of "2" is 163.58

Round(163.575, 0.01) = 163.57
Round(163.575, 0.02) = 163.58

Round(34.9999999999, 10) = 30
Round(35, 10)                      = 40
Round(39.88, 10)                 = 40

Asteroid

OK, I see what you mean but how can I make it work?

I need to round to 2 decimal places with 0.5 and above rounded up and 0.4 and below rounded down - would have thought this is an easy task for Alteryx!

Nebula

hi @ChrisTX

The problem is that the round isn't consistent

Because of the underlying float representation in the formula tool, both .565 and 575 round to .57.

Dan

Labels