Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Incorrectly getting different formula outputs for ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Solved

Jump to Solution

0
311
4

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 03, 2024 09:58 AM

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)

Reply

4 Replies 4

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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"

)

)

Reply

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 03, 2024 09:58 AM

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)

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Sep 03, 2024 10:01 AM

Agree, @kuovonne has a much more elegant solution.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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!