Simple balance calculation with debits and credits in the same column, either plus or minus sign.
The Amount number column looks like this
But a rollup (sum) of that column shows some values like this
Having that first long decimal behind the scenes for $5.42 isn’t really a problem, but ending up with balances of -$0.00 is giving me some trouble because I’d like to use a positive or negative balance as a condition for workflows.
When I double click on the actual Amounts in the data table to inspect the precision, I’m not finding anything more than 2 places. To prevent this going forward, I’m guessing the amount always needs ROUND or TRUNC applied first before adding a row. But is there any way to completely eliminate those extra decimal places from what’s already entered? Replacing amount with the rounded amount in the whole table didn’t make any difference.
Plus, I thought we just got rid of the penny, why do I have to deal with trillionths of a cent???
Are any of those numbers coming from a math column, or is it just a number column and a rollup? Are you suspecting that the roll-up itself is applying the floating point?
1 Like
Just the number column and a roll-up sum, and yes, I suspect that the roll-up is applying the floating point.
Also, when I try rounding the roll-up result, say -0.00000000000008526512829121202 to 2 places, I still get -$0.00, which is why I was trying to address the floating point further upstream. I am hoping to solve this problem without a workaround. There are a couple other places I’ve run into something similar and if I knew why, that would be super.
Guess I haven’t run into that with Rollup, or at least haven’t really noticed, but I guess I have occasionally seen negative zero in the past without fully understanding why.
Still a workaround but what I would do is add a Math column after the Roll-up that applies rounding in the formula.
Round(X,2)
Tried that already so I guess I’m stuck with a workaround like if less than 0.01 and greater than -0.01 then 0.00 to get rid of the -0.00
So you are saying Round(X,2) didn’t work? I’m not saying to rely on the precision option in the math column alone. I mean to actually use the the formula as I wrote it and replaced X with the roll-up column value. That should apply true rounding instead of visual rounding and remove the floating point decimals.
1 Like
Yes, I’m saying I tried that and it didn’t work.
Here’s what it looks like if you inspect the rounded -0.00
Try something like. round(x,2)+0
1 Like
Neat, that does the trick here. I’ll see if I can use a similar approach to coerce some other pesky floating points into two places.
Thanks for your time, Jeff!
1 Like
+0 usually does the trick for me, if not: -0+0+actual value.
1 Like
