How to RoundUp?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Expression
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The problem is sometimes we do not know the input data. So I can not
simply /100
or /1000 or /10000.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you, guys.
Your solutions and ideas are great helpful.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
