5 Replies Latest reply on Nov 6, 2012 4:39 PM by keywords

    Round Function error?

    AjEGfmTech

      I have found what appears to be an error, at least by my way of thinking.

       

      I am using the following simple calculation in a calculation field (Royalty) for a daily sales entry where 'DaySalesNet' is the daily net sales and 'Stores:RateRoyalty' is the royalty rate for that store.

      Royalty = Round ( DaySalesNet * Stores::RateRoyalty ; 2 )

       

      I have created a summary field (TotalRoyalty) to get the total of 'Royalty' when sorted by 'StoreID' with a running total where the summary is restarted for each sorted group.

       

      A sub-summary part is used in a report that shows the weekly totals for sales and royalties.

       

      Following are the results I'm getting which make me think there is an error in how the 'Round' function is working.

       

      RateRoyalty = 0.06

      Total of DaySalesNet = 6,487.73

      Total of Royalty = 389.27

       

      The calculated amount for 6,487.73 * 0.06 is 389.2638. This SHOULD round to 389.26, but instead it rounds to 389.27. This rounding error only occurs if I have the 'Royalty' Round function precision set to '2'. If I set the function precision to 4 or just remove the Round function altogether, the expected 389.26 occurrs.

       

      Is this an error of the function or is this just the accumulated error when getting the Total of seven days of Royalty amounts?

       

      I think I may have just answered my own question, but I am interested in other thoughts. Thanks.

        • 1. Re: Round Function error?
          comment

          ajegfmtech wrote:

           

          is this just the accumulated error when getting the Total of seven days of Royalty amounts?

           

          Very likely so. It would be easier to answer if we could see the daily amounts.

          • 2. Re: Round Function error?
            keywords

            I am making the assumption that at some point, or points, you want the result to be negotiable dollars and cents, hence only two decimal places.  In cases like these my experience is that it is best to apply rounding only at the point where it is required to become a negotiable sum and thereafter work only with that figure for any subsequent calculations; otherwise you do end up with occasional quirk such as the one you have discovered. In your example, I make two brief observations:

             

            1.     If it is necessary for the royalty on each store's sales for the day ( Royalty = Round ( DaySalesNet * Stores::RateRoyalty ; 2 ) ) then you may well have a slight variation if you then seek to further round the TotalRoyalty amount (although I suspect this is merely a check you are doing here).

             

            2.     If the royalty only becomes a negotiable amount (i.e. is actually paid to someone) as a final total, then don't bother to round it along the way, just at the point where it becomes a payment. This may involve a further calculation to round the summary total.

             

            So you are not quite right in your comment that it is an "accumulated error"; it is not an ERROR but rather the accumulated EFFECT of rounding as compared to a final rounding, which can deliver slightly different results.

            • 3. Re: Round Function error?
              AjEGfmTech

              Thanks for the help guys.

               

              @Michael: sorry I left those out. I think the assumption is correct that the accumulated effect is causing the rounding issue.

               

              @pixi: thanks for working up that solution.

               

              @keywords: Yes, you are correct, I should have said an accumulated 'effect', not error. Your assumptions are also correct in that the amount is not 'negotiable' until the final summary. Even in that case, the on-screen/print format of the field to 2 decimal places takes care of displaying the amount correctly. I removed the 'Round' functions and am just relying on the field data formatting. Thanks.

              • 4. Re: Round Function error?
                comment

                keywords wrote:

                 

                So you are not quite right in your comment that it is an "accumulated error"; it is not an ERROR but rather the accumulated EFFECT of rounding

                 

                Well, the technical term for this "EFFECT of rounding" is round-off error, or rounding error:

                http://en.wikipedia.org/wiki/Round-off_error

                • 5. Re: Round Function error?
                  keywords

                  touché!  When I check my dictionary (book, not online) I find the term round-off error there too. So, error it is.