Incorrectly getting different formula outputs for the same result

[Mouser-IB-Admin]

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.

[BillH]

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"
)
)

[kuovonne · Accepted solution]

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)

[BillH]

Agree, @kuovonne has a much more elegant solution.

[Mouser-IB-Admin]

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!