Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 03, 2024 08:34 AM
I have multiple tables that deal with budgeting, and in each one I've got a similar setup. I track the total budgeted and then how much we've spent manually, then a formula determines the difference and another formula looks at that difference and determines the status of the budget. It works great 95% of the time and looks like the below.
Total Budgeted | Total Spent | Difference | Status |
$100,000 | $100,000 | $0 | Matches Budget |
$500,000 | $650,000 | -$150,000 | Over Budget |
$300,000 | $200,000 | $100,000 | Under Budget |
The problem I'm seeing is that every once in a while I will get a record where the Difference correctly shows $0, but instead of Matches Budget the formula will return Under Budget or Over Budget. I don't know what could be causing this since there's no rounding taking effect; it's all precise numbers and formatted as currency.
The formula I use to determine the Difference result is {Total Budgeted}-{Total Spent} and in the screenshot below you can see three different records, all with zero amounts for the difference, yet each giving a different status. The first is a negative zero amount which makes no sense to me.
The formula for the Status is below. It's basic math and yet Airtable occasionally seems to get confused and give unexpected results.
IF(
{Difference} > 0,
"Under Budget",
IF(
{Difference} < 0,
"Over Budget",
"Matches Budget"
)
)
Any ideas as to what might be causing this? I'm at a loss and it's making it difficult to rely on the Status since it isn't always right.
Solved! Go to Solution.
Sep 03, 2024 09:58 AM
As Bill mentioned, it is probably a rounding issue. When displaying numbers, Airtable rounds the values according to the display settings. However, when performing calculations, Airtable uses more decimal places.
Bill provides one suggested formula that involves comparing with a decimal. I suggest using the ROUND() function in your {Difference} function, then you can keep your original {Status} formula.
ROUND( {Total Budgeted}-{Total Spent}, 0)
Sep 03, 2024 09:17 AM
It's floating point arithmetic, even though you may see 0 or 0.00 you can't really be sure that it's not really 0.0000001. There's a lot of explanations out there, this link was on the builtonair slack channel last week
https://www.youtube.com/watch?v=2gIxbTn7GSc
You might want to try something like
IF(
{Difference} > 0.001,
"Under Budget",
IF(
{Difference} < 0.001,
"Over Budget",
"Matches Budget"
)
)
Sep 03, 2024 09:58 AM
As Bill mentioned, it is probably a rounding issue. When displaying numbers, Airtable rounds the values according to the display settings. However, when performing calculations, Airtable uses more decimal places.
Bill provides one suggested formula that involves comparing with a decimal. I suggest using the ROUND() function in your {Difference} function, then you can keep your original {Status} formula.
ROUND( {Total Budgeted}-{Total Spent}, 0)
Sep 03, 2024 10:01 AM
Agree, @kuovonne has a much more elegant solution.
Sep 03, 2024 01:07 PM - edited Sep 04, 2024 04:11 AM
How would this work if the other values aren't rounded? Or is the rounding so granular that it's not expected to change the actual values? For instance, I don't want $50,750.80 to become $50,750.00 and throw off the Status determination because the cents don't align.
What you're describing makes sense, even though with straight up addition and subtraction I can't imagine why Airtable is coming up with that many decimals out. Division, maybe, but not this math. It's why I've been scratching my head for days. 😄
Edit: I tried it this morning, changing the 0 to 2 in the round formula so I could maintain the cents, and it works. Now I need to go through the rest of my tables and update them as well. Thank you!