I recently imported some user and transaction data for a client into two tables - Users and Transactions.
Each imported record had the following fields: Client, Hours Credited, Hours Debited, and [Imported] Balance.
In the Users table, I imported the Client and [Imported] Balance values. While in the Transactions table, I imported the Client, Hours Credited and Hours Debited values.
Back in the Users table, there are two rollup fields for both Total Hours Credited and Total Hours Debited as well as a Credits Remaining formula field which is {Total Hours Credited} - {Total Hours Debited}.
But when I created a checksum formula to see if the Total Hours Debited equaled the [Imported] Balance value, about 16% of the records were non-matching although they are displaying identical values:
I thought it could have been a rounding issue with some of the imported values, so I manually re-entered the values for Hours Credited and Hours Debited in the Transactions table as well as [Imported] Balance in the Users table, but I still got the same non-matching issue.
Only when I entered totally different values into each of those fields, did they match up properly (which isn’t helpful for my purposes).
Has anyone experienced this anomaly for field values (manually entered or imported) not summing up correctly?
P.S. The client mentioned they had a similar issue when doing a checksum before the CSV export, but surely manually re-entering the values should have fixed the any data rounding issue.
Is it possible there are decimals?
Have you tried add ROUND(…,0)?
Maybe this is related to the issue when things are lookedup or rolledup, the result is an array, not a number.
If that is the case the ROUND() function above may also show error.
Have you tried to do CONCATENATE() on both columns before you are comparing?
I would try that and then compare them as string. Since you are not doing any additional logic with numbers, then comparing them as string would do the job.
Here @Kuovonne has summarized well the issue with arrays… It’s not a problem in Airtable, rather Airtable doesn’t tell much and there isn’t much obvious guidance how to resolve it. So that may be the case here too?
If nothing helps, maybe provide link to sample data tables and share the table here.
But the weird thing is that when I manually re-enter the figures in the Transactions table, I am still getting these extra decimals at the end which is quite strange.
In the Transactions table, I manually changed the values from 164.00 → 164.10 & 161.80 → 161.90 (as per screenshot below), and the rounding error is still occurring.
Just something to keep in mind … rounding individual numbers and rounding result, there may show different, not sure if this would apply here, but just mentioning it in case…
1.0015
1.0035
1.0045
Each of these rounded gives one.
However total is 3.95 and rounded is 4.
So that is always issue with rounding, so keep that in mind.
However, I would also check the roll up function because that is using some formula, no? Are you totalling the numbers per client? When you roll up numbers there using SUM function, check there if you don’t need to add the rounding there into the roll up field function.
I am not sure I understand how your table is setup but I assume is this:
Transactional table
Client A … Credits … Debits …
Client A … Credits … Debits …
Client D … Credits … Debits …
Client A … Credits … Debits …
Client D … Credits … Debits …
Client C … Credits … Debits …
Client table
Client A
Client B
Client C
Client D
Now you are rolling up the transaction of each individual client from Transaction table, right?
Also, when you say you have “Imported Balance” field, where does that come from, are these from some other place you keep track of it manually, or does the Imported Balance come from Transaction table?
Also, just make sure… there is a difference between ROUND(X,2) formula rounding to 2 digits and simply showing only 2 digits in a number field (formatting). Sometimes that causes a difference and it is not obvious on the first look.
As always, would help you to get answers if you share your table with sample data.
Yes, it’s a Rollup using a SUM(values) formula. But since there was only one record per client on the initial import, there was no summing of multiple transactions for now.
That was one of the imported field values too. But it was just imported as a ‘checksum’ to ensure the rolled up values from the transactions table matched it (which they didn’t).
I only used the number field display formatting for all the fields. The only ROUND formula I added was after your suggestion which enabled me to ‘fix’ the issue by making the rolled up values just have 2 decimal places.
I’ve actually been in touch with Airtable support about the issue when I change the numbers in the Transactions table still adding all those extra decimal places to the numbers. It’s been escalated and I’m just waiting on a response.
Thanks @bfrench. Airtable support also confirmed it is a floating point issue like you mentioned. They also suggested using the ROUND function (as mentioned by @itoldusoandso earlier) as a workaround for the issue.
Yep - it’s one of the many ways to deal with the reality that silicon is unable to handle the true nature of numbers.
In my view, Airtable should do more to make this less likely to be seen. It will always occur because of the architecture of the hardware, but mitigation should not be left to the no-codeists who vastly use this platform.
