Round number to specified last decimal
- 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
Hi Alteryx Community,
A quick question, hope someone can help out!
I'm working with a list of numbers, which I want to round up to two decimals with specific conditions, e.g.
The only allowed latest decimal should be 0, 5 or 9.
0.136 should be rounded to 0.15
0.168 should be rounded to 0.19
0.193 should be rounded to 0.20
If you have any ideas, would be great!
Susan
Solved! Go to Solution.
- Labels:
- Output
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Susan,
Here's one approach:
- Round the original number to 2 decimal places and extract the last digit (i.e. everything after the decimal place) keeping the integer in a different column. You'll need to convert to text to apply the Right formula and then convert back to a number.
- Apply a conditional (IF THEN) formula to replace the last digit with 5, 9 or 0 based on the ranges that apply to each number.
- Add the rounded remainder to the integer.
Hope this makes sense. I would have built out an example but the logic for step 3 (i.e. what range of numbers applies to 0, 5 or 9) wasn't immediately obvious to me.
Here's the formula you could use for step 1:
Right(ToString(ROUND([Number],0.01)),1)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Jamielard,
Thanks so much for your suggestion, it works partially, but there is still an error. I've included a subset of my current workflow here.
To give a bit more context: the goal is to create prices - where a price point should always be **.*5; **.*9 or **.*0. (e.g. 1.05, 2.19, 5.99, 16.00, but not 1.04;2.18;5.97 or 16.01).
Input data is e.g.
8.40212
28.23395
19.8
5.445
6.695
15.45
15.45
11.0145
5.6175
5.445
2.06
Would you have an idea here?
Many thanks in advance!
Susan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Susan123,
Not the most elegant workflow i've ever produced, but i think it's doing the trick.
My method involves rounding to 2 decimal places, materialising the 3x rounded equivalents of the same as per the 2nd decimal digit: 0, 5 and 9 and finding the minimum one of them based on absolute difference compared to the first's operations output (rounding).
I'm sure this workflow can be further optimised (even for clarity of naming conventions) and there will be different methods to achieve the same, but i hope this can get you close to your ultimate solution.
Thanks,
Rafal
#Excuse me, do you speak Alteryx?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Susan123
After your Round() function in the Formula tool, you're left with one of 5 possible cases for Price Point which is now a string
1. The number is a integer with no decimals, i.e. "2" In this case, add ".00" to the end to give "2.00"
2. The number has one decimal, "2.1" Add "0" to give "2.10"
3. The number has 2 decimals and the last one is 1 to 4, "2.13" Change the last one to "5", "2.15"
4. The number has 2 decimals and the last one is 6,7,8, "2.17" Change the last one to "9", "2.19"
5. The number has 2 decimals and the last one is 0, 5 or 9, "2.19" Leave it as is
The attached workflow contains the following formula
It handles each of the 5 cases resulting in
Let me know if you want any further explanation about the formula
Dan
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
This worked perfect. Thanks so much for your efforts @danilang!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have the same problem and your advice was really helpful for me! Thank you!
