9 Replies Latest reply on Mar 31, 2015 1:46 PM by philmodjunk

    Calculation Errors - Returning totals that don't match by .01

    JennyArntzen_1

      Title

      Calculation Errors - Returning totals that don't match by .01

      Post

      Hi Everyone, I have set up a simple calculating table to keep a running record of client account balances. It is comprised of four fields: Amount_Previous, Amount_Invoice, Amount_Paid, and Account_Balance.

      I cut and paste the Amount_Previous from the previous record in Account_Balance to carry the balance forward. Amount_Invoice is a lookup field that brings in the Invoice total (a calculating field) related by pk_Invoice. Amount_Paid is entered manually whenever a payment is made. Account_Balance is a calculating field that starts with Amount_Previous, subtracts Amount_Invoice and adds Amount_Paid to give a result. 

      For some reason, the calculations are returning values that do not match by a penny.

       

      Can anyone explain how this can be happening and what I can do about it?

       

      Screen_Shot_2015-03-30_at_8.31.32_AM.png

        • 1. Re: Calculation Errors - Returning totals that don't match by .01
          philmodjunk

          I'd check for round off errors. You may have a dollar amount that displays with a two decimal place precision but which is actually a value with more decimal places. FileMaker will compute values based on the actual values stored, not the rounded, displayed values you can get with currency formatting on the Inspector's data tab. Try clicking into these fields and see if any show more than two decimal places.

          And rather than copy/paste balance forward amounts, you might consider the methods used here: https://dl.dropboxusercontent.com/u/78737945/AccountingLedger.fmp12

          • 2. Re: Calculation Errors - Returning totals that don't match by .01
            JennyArntzen_1

            I think you are right about the round off errors - how do I get my calculating fields to limit the actual calculations to 2 decimals? I can see how to get them to present as two decimals but I can't see how to limit the calculations so they don't run to more decimal places.

            Thanks!

            • 3. Re: Calculation Errors - Returning totals that don't match by .01
              JennyArntzen_1

              I set up your running total solution and double checked against my numbers - yours returns the same value by the end, so I guess the rounding off resolved *sigh of relief*. However, your solution is much better and I am going to use it from now on. I hadn't used running total before. Live and Learn!

              • 4. Re: Calculation Errors - Returning totals that don't match by .01
                philmodjunk

                A lot depends on why/how you had such a value before. If you are just recording dollar amounts of transactions, the value should not need any rounding, but a data entry error might do so. I had to add some validation rules (or did I use an auto-enter calc?) to catch those in a system I used to manage in my previous job.

                But if you are calculating a value-such as by applying a percent sales tax, you need to round your result. You can enclose your calculations in such cases inside a call to the Round function to round your values to the correct precision:

                Round ( InvoiceTotal & taxRate ; 2 )

                is an example that rounds the total from an invoice to a precision of two decimal places after calculating the correct sales tax.

                But knowing when to round and not to round values is crucial to getting correct results--especially when you are combining many different values to get your result.

                • 5. Re: Calculation Errors - Returning totals that don't match by .01
                  JennyArntzen_1

                  You got it right. I am calculating labor hours - which is end time - start time = labor hours - these are then multiplied by the invoice rate to tally up labor hour charges, then GST is calculated on that labor hour charge.

                  In my invoice I have to calculate two different taxes on 5 different subtotals before generating the invoice total. Then, that total gets divided by 10% and withheld in a holdback account. So I have multiple decimal returns on all those totals. 

                  If I read your post correctly, I need to add that Round function in every calculation where I am calculating those taxes ie. subtotal for category 1, 2, 3, 4, and 5. Do I put the Round function in the tax calculation

                  for example Round ( Labor_Hours Invoice Summary * .05 ; 2)

                   or in the total that combines the sub total and the tax amount? 

                  for example Round ( Labor_Hours Invoice Summary + Labor_HoursInvoiceSummaryGST ; 2)

                   

                  Thanks for your help!

                  • 6. Re: Calculation Errors - Returning totals that don't match by .01
                    philmodjunk

                    You'll need to apply that function very carefully. You can get round off error because you rounded off and shouldn't as well as getting round off error because you didn't round off and should have. Technically speaking "round off error" is the difference between a calculated value that was rounded and it's (often theoretical) unrounded value. such as the difference between 0.33333 and 1/3.

                    • 7. Re: Calculation Errors - Returning totals that don't match by .01
                      JennyArntzen_1

                      Okay. Gosh, thanks for your advice on this! What I am going to do is set each field that is returning a percentage calculation to round to 2 decimals. That is what I want it to do. Then, when it calculates with dollar amounts there won't be any discrepancies. I generate client invoices weekly, so I just need this thing to work reliably. I'll see if I can get it set up properly today.

                      • 8. Re: Calculation Errors - Returning totals that don't match by .01
                        JennyArntzen_1

                        Thank you!!! I implemented the Round function on all my percentage calculating fields and now the numbers are consistent in the account summary. Well done!

                        • 9. Re: Calculation Errors - Returning totals that don't match by .01
                          philmodjunk

                          Here's an example where using the round function introduces error rather than eliminates it:

                          Say you have an invoice with line items where some of the items are taxable and other are not. There are two ways to compute the sales tax amount and, if properly implemented, both will produce exactly the same result, but rounding in the wrong place could easily produce an error too.

                          Method 1: This may seem like the obvious method but method 2 also works and in some systems can be simpler to implement. You set up your system to compute the line item total for just the taxable items. You then multiply that subtotal by your tax rate and round the resulting value to the nearest penny.

                          Method 2: For each line item record, you set up a calculation field that returns zero if the item is not taxable, but multiply it by the tax rate if it is taxable. Your system then computes the total of this calculation field to compute the tax amount for this invoice.

                          But in Method 2, should you round the results of this calculation field? Answer: No. If you use the round function to round the results of each individual line item calculation and then sum the rounded values to get a total for your invoice, some invoices will show a slightly different amount via Method 2 than method 1. By rounding, you introduce a small amount of round off error that may add up to enough of a difference to produce a value a penny or two different when you compare the invoice total to that produced by method 1. But if you don't round the values in the individual calculation fields, but add up all the unrounded values first, before rounding to the nearest penny, the total produced by Method 2 should be identical to the total produced by Method 1.