Why does this not return a date value?

[Lauren_Prete]

Hi Airtable Wizards, 

I have the following formula which is formatted in currency and occasionally returns a -$0.00 result, where to my untrained eye, I cannot see why. 

{PM: TOTAL BANK ALL EFTPOS}-{PM: TOTAL EFTPOS SALES}

"PM: TOTAL BANK ALL EFTPOS" and "PM: TOTAL EFTPOS SALES" are computed fields that simply tally up totals from other columns. 

When both of the above columns match, it should return $0.00, which for some outputs it does, and others it computes as a negative. 

Just wondering if anyone can figure out why?

[TheTimeSavingCo]

It could be because that's not really a 0.  Try adding a `ROUND()` to it and it might go away.  If it does, you can find some discussions on it here and here

[Lauren_Prete]

Ah I see, thanks @TheTimeSavingCo! Unfortunately ROUND() rounds my output too much and I need to be aware of any discrepancy, no matter how large or small (e.g. 0.04 is rounded to 0.00). After having a look at those threads, this looks to be an endemic issues with no fix, so I'll just deal with it for now. 

Thank you

[TheTimeSavingCo]

Roger that.  You probably already know this, but round lets you set the precisions. Not sure if that helps

 

[Lauren_Prete]

@TheTimeSavingCo I certainly did not know that! Just tried the following and it's not liking my input/direction as my result is formulated in currency.

ROUND({PM: TOTAL BANK ALL EFTPOS}-{PM: TOTAL EFTPOS SALES}, 0.00)

Any ideas on a workaround here? 

[TheTimeSavingCo · Accepted solution]

I assume you're trying to get it to round two decimal places?

If so, try:

ROUND({PM: TOTAL BANK ALL EFTPOS}-{PM: TOTAL EFTPOS SALES}, 2)

 You can also try out `ROUNDUP()` if that's what you want instead

[Lauren_Prete]

Thanks @TheTimeSavingCo! Worked a treat!