Skip to main content

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?

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

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


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

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


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

 

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

 


@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 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? 

 

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

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


Thanks @TheTimeSavingCo! Worked a treat!

Reply


Terms & Conditions