Rounding decimal issue when using Sum

6 posts, 1 answers
  1. Ron
    Ron avatar
    3 posts
    Member since:
    Aug 2015

    Posted 08 Aug Link to this post

    As shown in the image, the Amount totals are calculating correctly ( Price*NetUnits=Amount). However, when using the SUM function in the Subtotal, the calculation is off by a penny. The SUM function is adding the amounts then Rounding which is causing the issue. See the calculations below.

    Line 1 (7/25/2016) - Price $13.06 * Net Units 9.44 = Amount $123.286 (Rounded $123.29)

    Line 2 (7/29/2016) - Price $13.06 * Net Units 8.30 = Amount $108.398 (Rounded $108.40)

    Subtotal $123.286 + $108.398 = $231.684 (Rounded $231.68)

    Line 1 + Line 2 = $231.69

    Any help would be appreciated. Thanks!

     

  2. Katia
    Admin
    Katia avatar
    301 posts

    Posted 10 Aug Link to this post

    Hi Ron,

    The described behavior is expected when using the Sum() function.

    In case, you need to round the numbers with a custom approach you can use built-in Math Functions, for example Ceiling(), Floor(), Round() and Truncate().


    Regards,
    Katia
    Telerik by Progress
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  3. DevCraft banner
  4. Ron
    Ron avatar
    3 posts
    Member since:
    Aug 2015

    Posted 10 Aug in reply to Katia Link to this post

    Thanks for the response Katia. I've tried using the Math functions for rounding. However, I have not been able to round to a certain decimal, only the nearest integer. What is the correct format for rounding to a decimal in an expression?
  5. Answer
    Katia
    Admin
    Katia avatar
    301 posts

    Posted 12 Aug Link to this post

    Hi Ron,

    Let's say you want to calculate the subtotals using amount values rounded to two decimal places. For this, you can use Ceiling() function with the amount value multiplied by 100 and then divide the result by 100, for example:
    = Sum(Ceiling(Fields.Amount * 100) / 100)

    Another option could be to use Format() function to round the amount values, for example:
    = Sum(CDbl(Format("{0:N2}", Fields.Amount)))


    Regards,
    Katia
    Telerik by Progress
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
  6. Ron
    Ron avatar
    3 posts
    Member since:
    Aug 2015

    Posted 12 Aug in reply to Katia Link to this post

    Thank you Katia! You have helped me resolve the problem!
  7. Jennifer
    Jennifer avatar
    16 posts
    Member since:
    Aug 2012

    Posted 30 Sep in reply to Katia Link to this post

    I was having a similar problem and your solution worked for me as well!  Thanks!
Back to Top
DevCraft banner