Skip to main content
Solved

Formula calculating to -$0.00

  • January 25, 2023
  • 4 replies
  • 40 views
Ashley_Jackson
Forum|alt.badge.img+17

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!

 

Best answer by Yeshi_Wang

Try using ROUND formula for your Rollup field as below:

ROUND(SUM(values),2)

4 replies

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • January 26, 2023

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
Forum|alt.badge.img+17
  • Ashley_Jackson
  • Author
  • Inspiring
  • January 26, 2023

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?


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
Forum|alt.badge.img+4
  • Yeshi_Wang
  • New Participant
  • Answer
  • January 28, 2023

Try using ROUND formula for your Rollup field as below:

ROUND(SUM(values),2)

Ashley_Jackson
Ashley_Jackson
Forum|alt.badge.img+17
  • Ashley_Jackson
  • Author
  • Inspiring
  • January 31, 2023

Try using ROUND formula for your Rollup field as below:

ROUND(SUM(values),2)


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

Terms & ConditionsAccessibility statement