7 Replies Latest reply on Jul 18, 2017 4:32 AM by greatgrey

    Calculation Error

    johnw54

      I'm using the following calculation to add up invoice fields.

       

      zUnit Price Calc + zUnit Price 2 Calc + Unit Price 3 Calc + Unit Price 4 Calc +Unit Price 5 Calc + Unit Price 6 Calc + Unit Price 7 Calc + Unit Price 8 Calc + Unit Price 9 Calc + Unit Price 10 Calc + Unit Price 12 Calc + zUnit Price Custom Charges Calc + zUnit Price Metal Calc + zUnit Price Body Trim Calc + zUnit Price Nail Calc + zUnit Price Panel Calc + zUnit Price Stone Calc + zUnit Price Wood Calc + zUnit Price Base Calc

       

      It is totaling $1266.67.  Manually it add up to $1266.65 which is correct.  Any suggestions on how to make my total accurate?

       

      Thank you

        • 1. Re: Calculation Error
          Stephen Huston

          You may need to add the Round function to your fields  to stop accumulation of fractions.

          • 2. Re: Calculation Error
            FabriceNordmann

            There are some areas in which I tend to trust computers more than humans

            I'd be very surprised if FileMaker was not capable of adding numbers.

            What can happen though is that numbers have more decimals than displayed (because of layout formatting).

            • 3. Re: Calculation Error
              gofmp15

              Computers generate some long decimal numbers.

               

              Years ago I found that Excel suffered from extraneous numbers in the 12+ decimal places, for instance.

               

              Use the Round() or Truncate(?) functions to eliminate these extraneous decimals.

               

              For instance, to get the value of a particular item you might use a calc that divides or one that uses a longer decimal such as 1.56784.

               

              Example  zUnit Price Calc =  Round( (Length x Cost per foot x something) ; 2 )

               

              With the round the result might be 125.36 but without it 125.36225

               

              Those .00225's can add up and create those differences.

               

              You may not see these extra digits when you format your fields for decimal with 2 places.

              • 4. Re: Calculation Error
                schamblee

                The number field may only display 2 decimal places, but the number actually has more,

                so  if you have fieldA  with 1.043  and fieldB with 1.042, both field would appears as 1.04,  but your total would be 2.09.     You need to use Truncate (mynumberfield;2)  FileMaker Pro 16 Help

                1 of 1 people found this helpful
                • 5. Re: Calculation Error
                  schamblee

                  I believe I would modify my design with a related table and use a summary field to total. 

                  1 of 1 people found this helpful
                  • 6. Re: Calculation Error
                    johnw54

                    Thank you gofmp15  and all.  The Truncate function took care of my problem

                    • 7. Re: Calculation Error
                      greatgrey

                      Truncate can also bite you because it will make a number smaller that what is displayed when formatted to 2 digits. 0.029 displays as 0.03 while Truncate makes it 0.02 and most pricing is rounded. You just gave your customer 33% off.

                      1 of 1 people found this helpful