Running total problem

[Tpaairman]

This is a funky issue that doesn't seem to be limited to FMP, but it's also showing up in Excel.

This is on a check register file.  I have a transactions amount field and a running total, which is the balance.  I've been able to verify the very transaction is entered and accounted for, but I'm off form what the bank shows.  I exported all of the transactions to Excel and had it do it's own running total, which matches what was exported from FMP.  Here's where it gets odd.  In Excel, I had it do a calculation in each row that subtracts the imported running total from it's running total.  Every single one should be zero, but I have some that it says for example -1.22076E-13, which tells me there's 13 more decimal places, but I can't get it to show that.

Where it really gets bizarre is in Excel, if I change the transaction amount by anything, suddenly that funky number goes away, and now it shows a true difference based on what I now have entered in the transaction.  In other words, if I change the transaction amount from 25 to 24.99, it now shows a difference of .01,  but when I change it back to 25, I get the funky number.  I tried deleting the cell, and creating a new one, and it does exactly the same thing.  I tried changing the cell that shows the running balance that was imported over, and same thing happens.

[comment]

I can only guess, since you did not provide a way to reproduce the problem. 

The value of -1.22076E-13 is a VERY small number - almost zero, but not quite. If you see that where you expect to see zero, then it's likely a problem of rounding. Sometimes (in Filemaker, very rarely) the problem is caused by translation from floating-point representation to decimal. More often it is a result of neglecting to round a value where rounding is warranted.

You could probably get away with just formatting the summary field to display as decimal/currency with 2 decimals. But that's as far as I am willing to go on pure hunch.

 

Edited April 4, 2023 by comment

[Tpaairman]

All of the fields already are set as currency with 2 decimal places.

[comment]

5 minutes ago, Tpaairman said:

All of the fields already are set as currency with 2 decimal places.

Then I obviously misunderstand something in your description. A field formatted as currency with 2 decimal places will never display a value of -1.22076E-13 (unless you click into it).

 

Edited April 4, 2023 by comment

[Tpaairman]

In FMP, it's displaying correctly as two decimal places, but my running total has started to drift away from what it actually should be.  So I exported everything to Excel so that I could more easily see what's going on.  It's in Excel that it's showing the funky numbers.  But I'm assuming that somewhere in the background, it's doing the same in FMP, and that's what's causing the drift.

But, when I click on the field, it still shows only two decimal places, even if I change the format to a general instead of a currency.

Something else I forgot to mention, I had a check register file already, but have been created a new one for a few reasons.  But the check register portion that I'm working on here, works the same way, and has the same drift.  So, it's not just this file.

Edited April 4, 2023 by Tpaairman

[comment]

1 minute ago, Tpaairman said:

It's in Excel that it's showing the funky numbers.  But I'm assuming that somewhere in the background, it's doing the same in FMP, and that's what's causing the drift.

I believe your assumption is wrong and the whole experiment in Excel is a red herring distracting from the real issue - of which you told us nothing.

 

[Tpaairman]

Actually I did tell you something and you just quoted it.  I have a running total summary field that over time, is drifting away from what it should be.  When all of my transactions are accounted for in relation to the bank, the balance of my last transaction should be the same as my current balance according to the bank, but it's off by just over $10.  But, I've been able to verify that all transactions are entered correctly.  That can only mean that somewhere along the way, the running total field in FMP is deviating from what it should be.

Edited April 4, 2023 by Tpaairman

[comment]

I don't know how to solve a problem I cannot reproduce.

If you want, post a file showing the problem (and preferably, only the problem).

BTW, a discrepancy of over $10 is huge relative to -1.22076E-13. There is no way these two are connected.

 

Edited April 4, 2023 by comment

[Tpaairman]

$10 is large compared to one record being off, but maybe not several.  I have a couple that are showing -8.xxxxE-xx.  Some are positive numbers.  But it looks like a total of 10 that this happens with a total of 5,230 records.

Here's the Excel sheet where I did the group of 10 transactions, and has the error twice.  I can't really go posting my FMP file on this one.  But, I still think it might be the same type of issue, or at least point in the right direction.  I know that you can turn on and off levels of precision in Excel, but the question is why is it doing this at all with what seems to be specific numbers.

 

Error Sample.xlsx

Edited April 4, 2023 by Tpaairman

[comment]

1 hour ago, Tpaairman said:

$10 is large compared to one record being off, but maybe not several. 

Uhm ... I don't think you grasp the proportions here.  If your account had 1 billion transactions every day, and each of those transactions had an error of -1.22076E-13 (in the same direction), it would take more than 224 years for the errors to accumulate to $10. I suggest you look for a more plausible cause of the error (e.g. a typo in data entry).

 

1 hour ago, Tpaairman said:

the question is why is it doing this at all with what seems to be specific numbers.

That's how it is with floating-point arithmetic rounding errors. You can read more about it here:
https://learn.microsoft.com/en-US/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result

Anyway, this has nothing to do with the original problem in Filemaker.

 

Edited April 4, 2023 by comment
corrected a typo: billion instead of million

[Tpaairman]

It's not possible for it to be a mistyped entry.  When you reconcile a bank account, you first look at what the bank balance was the last time you reconciled.  Let's say that was $1,000.  Then you look at the current balance of the last thing that has cleared the bank (so you're not counting pending items.)  Let's say now my balance is $700.  So I'm looking for a total of $300 in transactions.  I might have spent $300, then deposited $100, then went and spent the $100, but those three transactions will equal out to $300.  If I mistyped the deposit as $101, then I wouldn't match the balances according to the bank. I would show that I have $301 in transactions when the bank cleared $300.  So reconciling does not look at the running balance according to your check register.

I understand that Excel and FMP are different apps, but they both came up with the same drifted running total.  And, I changed my transaction field in FMP to round to two decimal places, even though everything was already set to two decimal place currency, and it didn't change the end result.

[comment]

49 minutes ago, Tpaairman said:

I understand that Excel and FMP are different apps, but they both came up with the same drifted running total. 

Don't you think that should tell you something? Do you really imagine that after all those years you'd be the first one to discover such a major flaw in such a basic function in Filemaker - and then also the first one to discover the same flaw in Excel?? Have you ever heard of Occam's razor?

Anyway, this is not going anywhere unless you provide a way to reproduce the problem and I am not going to spend any more time on this unless you do.

 

[Tpaairman]

I have known about it for years, but I never could find the problem, and since it's a small amount and in my favor, and I was able to spot the difference to know it wasn't a runaway issue, it ended up on the back burner.  Now that I'm starting over from scratch, I'm trying to squash that bug.  As far as finding it in Excel, that was as of last night.  I had never exported over to that before.  I'm not saying it's a flaw in the software, but I'm not sure where else to look for the problem given that I've verified the entries eight ways to Sunday.  Since it's showing up in both, then that leads me back to an entry problem that I can't find.  But I was hoping that what came up in Excel might be a clue as to where to look in FMP.

If you don't want to continue with it, then don't.  I appreciate you looking at it, but nobody said you have to be the one and only person to answer questions.

[Tpaairman]

Upon further research, I've found the following.  I created a new FMP file so I could play with the data in FMP and not Excel.  I found that some of the transactions actually do have the extra digits in them.  That never showed up in the original FMP file or Excel, even when I changed the number format to general.  When I changed it to currency in my new file, then back to number, the extra digits were gone, but the balance was still drifted, so they have to be there.  The reason this didn't cause my reconcile to be off is because I might be reconciling 20-30 transactions at the most, and only one or two might be in that group.  Since they were off by fractions of the penny, it didn't skew the reconcile.   So to that end, I apologize, and you were right that the error had to be in the transaction side.

But, here's where it gets stranger.  I have an amount field, then AMT mask, which has a set field calculation with a Case statement in it, that basically if the transaction is listed as a Deposit, then the AMT mask just equals AMT, but for anything else it's "-" & AMT, so it's automatically making a negative number for money out.  What's goofy is I found that sometimes the AMT field has extra decimal places, but not the Mask, and sometimes it's the other way around.  Sometimes both.

[comment]

I don't know what you mean by "the extra digits". I am quite certain that a calculation of -AMT returns a result with exactly the same precision as AMT. Once again, these discussions could be eliminated if you simply posted a file showing the problem. 

 

 

[Tpaairman]

I'm sorry.  I've discussed this with quite a few people and lost track of where I have mentioned that part.  In the AMT field and in the AMT Mask field, some of the entries show as say 10.22, but what is actually there is something like 10.220000000000090098474585, even though it was typed in as 10.22, and retyping that doesn't change it.

That's why I was was getting the odd values in the running balance.  But there's no rhyme or reason to it. 

Sorry, but I'm not posting my full check register here.  But, I'm not sure what there would be to look at in a simple number field.

Edited April 6, 2023 by Tpaairman

[comment]

For starters, all you would need to do is post a file with one record and one field that shows a result of 10.220000000000090098474585 when you type in 10.22. Although I don't know how to interpret what you say here:

9 minutes ago, Tpaairman said:

some of the entries show as say 10.22, but what is actually there is something like 10.220000000000090098474585

If they show as 10.22 then how do you know that "what is actually there" is something different? I suspect you are chasing a ghost. Digits that aren't displayed when you click into the field (or - same thing - when the field is formatted to display "as entered") do not exist.

For your original problem, you would need to post a file that calculates a running total on any number of records (with any data you choose) and produces an incorrect result. And you would need some way of proving that the result is incorrect. If I am not mistaken, you claimed a discrepancy of more than $10 with just 5k records. That's a huge error and shouldn't be difficult to spot - even by reconstructing the process manually.

 

[Tpaairman]

This is a second file that I created to try to work with this in FMP instead of Excel.   Go take a look at the transaction for .07.  It looks as normal as the rest.  But click on the field and see what you get.

What's frustrating me further is, I had all of my transactions in Excel, and imported to this file.  I've since deleted the transactions just as part of my testing.  Now,it won't import the numbers with the errors, however, I have one file with just these 10 transactions as a sample, and it will import it.  But in the original file, even when formatting back from currency to just a number, the extra digits don't show up, and they don't show in Excel, but sometimes I can get them to show up here, on an untouched file.  At one point I had a running balance field here as well, and when I started manually correcting those entries to get rid of the extra numbers, the balance started to shift to where it should be.  I did maybe 100 and it changed by just over $1.

Reg Test.fmp12

Edited April 6, 2023 by Tpaairman

[comment]

I get the amount of .070000000000000007. But when I type in the amount of .07 it stays .07. At no point it turns back into .070000000000000007 or any other value. So the only conclusion I can draw from this that the original value entered into the field was .070000000000000007.

I am not sure what to make of all this. FYI, I actually went back to your original post to check if it wasn't dated April 1.

 

Reg Test.fmp12

[Tpaairman]

I wish it was an April fools joke.  It's not.

As I said, I can manually reenter the numbers on this file.  In my original file, when you click on the field, it only shows two decimal places, even when formatted back to number from currency.  But I know that the extra decimal places have to be there because the balance is off.  The test file showed the same error amount until I started correcting them manually and it change.

And no, I did not enter transactions with a bunch of extra numbers, and not sure how I could have since the field was formatted as a currency, so it should not be possible anyway.

Edited April 6, 2023 by Tpaairman

[comment]

Sorry, I missed the part you added while I was replying. Are you saying the source of your data is Excel? That could explain the extra digits - esp. if the source cell is a calculation.

However - as I already pointed out several times - there is simply no way these digits could accumulate to a discrepancy of over $10 with a mere 5k records. 

 

Edited April 6, 2023 by comment

[Tpaairman]

The data was exported from Filemaker to Excel, then Excel to the other Filemaker file.

[comment]

1 minute ago, Tpaairman said:

The data was exported from Filemaker to Excel, then Excel to the other Filemaker file.

It's unlikely that alone would cause a change, but I don't know if it can be ruled out. But again, that's a grain of sand that cannot account for the Sahara desert.