How to RoundUp?

[KarlWang]

I saw there are functions CEIL() can change data from ceil(6.54) to 7 and  Floor(6.54) to 6.

I would like to use roundup() functions.

Such as:

324 to 400

3425 to 4000

3425.123 to 4000

Anyone know how to do it? Thank you.

[Philip]

The quickest way I know is to divided by the place you want to round to, use CEIL to round up, then multiply back by the place:

324 to 400

CEIL([Number] / 100) * 100

3425 to 4000

CEIL([Number] / 1000) * 100

3425.123 to 4000

CEIL([Number] / 100) * 100

[dataMack]

Ceiling and floor just go to the nearest integer.  The ROUND(x, multiple) function will allow you to specify the level of precision on which to round, very similar to Excel.  But you can't specify the direction, so in your example if your did ROUND(3425.123,1000) it would return 3000.

[KarlWang]

The problem is sometimes we do not know the input data. So I can not
simply /100
or /1000 or /10000.

[s_pichaipillai · Accepted solution]

Karl,

i converted both Jason and Philip idea into more dynamic 

i have created a text input to indetify the mapping for the conversion as below and Joined with length of source input the apply formula 

the final output is as below 

please test the attached workflow

[jdunkerley79]

 If you would like a single formula which does this:

Ceil([Val]/Pow(10,Floor(Log10(Abs([Val]))))) * Pow(10,Floor(Log10(Abs([Val]))))

Attached as a workflow

The Pow(...) part works out the power of ten for the input number.

For the sake of self promotion, did a blog post on rounding Tableau and Alteryx a little while ago:

http://jdunkerley.co.uk/2015/07/29/rounding-calculations-in-tableau-and-excel-and-alteryx/

[dataMack]

Great article James!  I think they key with rounding is to first understand that different people/organizations all have different definitions on what they mean by rounding.  You must understand specifically what you are trying to accomplish first, then select from one of these detailed methods to accomplish.  If you simply round using 'out of the box' functions, it may be at odds with the accepted truth inside your organization.

[KarlWang]

Thank you, guys.

Your solutions and ideas are great helpful. 

[aMac]

Thanks for providing this! Are there any recommended adjustments you would make if I am rounding at the decimcal level, but I want to round up to the nearest integer?

For example., I want to round all of these values:

0.31, 0.25, 0.38 all to 1,

and

1.38 ,1.25,  1.38 all to 2

How would I modify this table to accomplish this?

[KenMorrill]

Have a need to Round Up to the nearest penny.  This seems to work. The value I'm rounding is [Unit Price]

IF (([Unit Price] - FLOOR([Unit Price])) * 100) - FLOOR((([Unit Price] - FLOOR([Unit Price])) * 100)) = 0 THEN [Unit Price] ELSE FLOOR([Unit Price]) + (FLOOR((([Unit Price] - FLOOR([Unit Price])) * 100) ) / 100) + 0.01 ENDIF

$23.00 = $23.00

$23.10 = $23.10

$23.2000001 = $23.21