Help

SUM formula returning negative currency values - why?

Topic Labels: Formulas
Solved
Jump to Solution
699 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Lauren_Prete
6 - Interface Innovator
6 - Interface Innovator

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?
1 Solution

Accepted Solutions

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

See Solution in Thread

6 Replies 6

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

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

Screenshot 2023-03-21 at 3.45.30 PM.png

@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

Thanks @TheTimeSavingCo! Worked a treat!