Hi, currently I am working with pricing data and I want to round the numbers up to 4 decimal places as Excel usually do. (Rounding up if we have 5 or more than 5 and round down if we have a number less than 5)
I used Round([Field1], 0.0001) formula but it doesn't do the justice to all the record. (for some it rounds up and for some it rounds down, not consistent). I have attached a workflow as well. I really appreciate your ideas/ suggestions on this.
Hi @Nathashawije,
This is down to Alteryx (and computers in general) not being able to store decimal numbers exactly. so 0.048850 is actually 0.04884999999999999. One quick (and dirty) work around could be to use something like,
Round([Field1]+ 0.000000001, 0.0001)
This looks like it gives a fairly good explanation of the phenomenon of computers representing binary numbers as decimals: https://www.exploringbinary.com/why-0-point-1-does-not-exist-in-floating-point/
Following @PhilipMannering post and (dirty) solution, here's another post that might help you understand a bit about the background of it
Thanks @AngelosPachis.
Just realized, a less dirty solution might be just giving your digits enough precision,
Oh that's a nice one as well. Still in my head the "dirty" way still seems easier to explain to a third person for some reason 🤷
Thanks all. I really appreciate your responses.
I actually used the dirty trick, it was not the best (still there are rounding up and down issues, Alteryx still hasn't the capability to do that correct) but I could convince my manager. He is okay with having a tiny difference on the numbers.