Formula calculating to -$0.00

[Ashley_Jackson]

I can't for the life of me figure out what's happening with this one transaction in the personal finances base I've been building.  Would you guys take a look?

These are two of the same type of transaction for comparison.  The top transaction calculates exactly as expected.  The bottom transaction calculates out to -$0.00?!?!  I have traced and re-traced the formulas and can't find the issue, haha!  Here's the flow:

The Amount field calculates by choosing the non-empty entry amount field from the following options:  an Enter Amount field that allows me to type the amount indirectly, and the Import Debit and Import Credit fields you see in this screenshot which come from my bank import files.

Transactions are designated into one or more categories.

The amounts from those designation records are rolled up into the Total Split field.

The Left to Split formula is:

IF(Type="Withdrawl",

Amount-{Total Split},

IF(Type="Deposit", Amount-{Total Split}))

Does anyone have any explanation for the -$0.00 formula calculation??? TIA!

[Ben_Young1]

Hey @Ashley_Jackson! 

I set up a quick replication of your formula and the applicable fields, but I unexpectedly wasn't able to replicate the returned "-$0.00" value that you are seeing.

IF(
    {Type} = "Withdrawl",
    {Amount} - {Total Split},
    IF(
        {Type} = "Deposit",
        {Amount} - {Total Split}
    )
)

Now, the only obvious variance in what I have against what you posted is that you're referencing computed fields. So, I buckled in and built a similar implementation to what you're working with. Interestingly, I still wasn't able to reproduce your results.

Out of my own curiosity, does the "-$0.00" value have any effect on your workflow? Or is it a cosmetic issue?

[Ashley_Jackson]

Hey Ben, Wow, this is fascinating!  I suppose it's not actually affecting MOST of my base.  The category totals and balances still calculate correctly.  The only place it messes up is in the transactions table.  I have a grid with several filters set up to catch common errors.  Ideally, the grid would return no responses, meaning all transaction records are error-free.  The -0 record always shows up bc my filter is "does not equal 0."  I could, of course, change the filter but that could cause other problems down the line maybe?  I don't know.

Thanks for your help as always!  At least we've tracked the error down to the roll-up field or its calculated components!

[Yeshi_Wang · Accepted solution]

Try using ROUND formula for your Rollup field as below:

ROUND(SUM(values),2)

[Ashley_Jackson]

OMG that fixed it!  But why???  Can you explain?