Skip to main content

Hello,


I have a table that rolls up sums from two other tables and displays them in currency format. My hope and expectation is for these values to match, and if they do not, the record is flagged. I figured this would be a simple IF function that looks something like this:


IF({Rollup 1}={Rollup 2},“ ✅ ”,“ ❌ ”)


The result is what is confusing me. I find that even if the two rolled up values match with an eye test, they might still be flagged as being unequal, I’ve tried changing the formatting and expanding out the number to see if it has to do with some hidden decimal but have had no luck. By all appearances, the numbers match exactly. I’m not sure if this has something to do with how the records in the linked tables are formatted either but figured that if the field being rolled up is in the same format, this shouldn’t matter either.


Chances are that I’m missing something incredibly simple but if anyone has any advice on why this is happening and how I can get a proper flagging formula in this table, that would be great.


Thank you


You are missing something, but I wouldn’t quite say it’s simple. In fact, the way Airtable compares values behind the scenes vs how it displays them to the user is quite unintuitive.



You have hit on the issue here, but solving it isn’t a matter of using the formatting tools, as one might think.


Behind your fields, Airtable is still holding the full float (decimal) value of your rollups, down to some unknown, but pretty precise decimal place - and when you make the comparison {Rollup 1} = {Rollup 2}, Airtable is using those full float values in the comparison, rather than the rounded values you see in those fields.


In order to perform a rounding that actually applies to the values used in the comparison, you need to use the ROUND() function in your formula. This version will round to whole numbers and then compare:


IF(

   ROUND({Rollup 1}, 0) = ROUND({Rollup 2}, 0),

   "✅",

   "❌"

)

Supply your own rounding precision as needed:


You are missing something, but I wouldn’t quite say it’s simple. In fact, the way Airtable compares values behind the scenes vs how it displays them to the user is quite unintuitive.



You have hit on the issue here, but solving it isn’t a matter of using the formatting tools, as one might think.


Behind your fields, Airtable is still holding the full float (decimal) value of your rollups, down to some unknown, but pretty precise decimal place - and when you make the comparison {Rollup 1} = {Rollup 2}, Airtable is using those full float values in the comparison, rather than the rounded values you see in those fields.


In order to perform a rounding that actually applies to the values used in the comparison, you need to use the ROUND() function in your formula. This version will round to whole numbers and then compare:


IF(

   ROUND({Rollup 1}, 0) = ROUND({Rollup 2}, 0),

   "✅",

   "❌"

)

Supply your own rounding precision as needed:


Brilliant! Wish I had thought of that.


Thank you Jeremy, much appreciated.

Terms & Conditions