Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2016
    Posts
    2

    Unanswered: Crystal Reports v8.5 Rounding up to nearest 1000

    I need to create a formula to round a numeric value up to nearest 1000th.

    The default behavoir is to round to the nearest, and not round up.

    Step 1: remove any possible decimal point Round({PROC_CONFIRM.MAX_INSURED_TRIP_VALUE})
    Step 2: truncate to the 1000’s spot truncate(Round({PROC_CONFIRM.MAX_INSURED_TRIP_VALU E}),-3)
    Step 3: add 1000+ back to force the round up truncate(Round({PROC_CONFIRM.MAX_INSURED_TRIP_VALU E}),-3) +1000

    I’ve tried creating this formula, and all it pulls is the +1000 (whether I change it to +2000 or +3000, that’s what it pulls.. either the 1000, 2000, or 3000.. depending on which one I put…)…

  2. #2
    Join Date
    Jan 2016
    Location
    San Jose
    Posts
    4
    Quote Originally Posted by steph_io View Post
    I need to create a formula to round a numeric value up to nearest 1000

    truncate(Round({PROC_CONFIRM.MAX_INSURED_TRIP_VALU E}),-3) +1000

    I’ve tried creating this formula, and all it pulls is the +1000 (whether I change it to +2000 or +3000, that’s what it pulls.. either the 1000, 2000, or 3000.. depending on which one I put…)…
    It sounds like your formula is yielding 0. If the values you're working with are low enough, that could be it. Check the values without applying any formula and see what they look like.

    Even if it works, your formula won't round up correctly, as it'll round up exact values (1000.00 becomes 2000). Assuming you're working with currency values (2 decimal places), the following should do what you want.

    truncate(({PROC_CONFIRM.MAX_INSURED_TRIP_VALUE}+99 9.99), -3)

    Will round up to 1000 any value from 0.01 through 999.99, but will not round up 1000.00 nor 0.


    That's an old Lotus 1-2-3 trick, BTW.

  3. #3
    Join Date
    Jan 2016
    Posts
    2

    Crystal reports round formula

    Quote Originally Posted by avargas View Post
    It sounds like your formula is yielding 0. If the values you're working with are low enough, that could be it. Check the values without applying any formula and see what they look like.

    Even if it works, your formula won't round up correctly, as it'll round up exact values (1000.00 becomes 2000). Assuming you're working with currency values (2 decimal places), the following should do what you want.

    truncate(({PROC_CONFIRM.MAX_INSURED_TRIP_VALUE}+99 9.99), -3)

    Will round up to 1000 any value from 0.01 through 999.99, but will not round up 1000.00 nor 0.


    That's an old Lotus 1-2-3 trick, BTW.

    Thank you for your feedback.
    This formula: truncate(({PROC_CONFIRM.MAX_INSURED_TRIP_VALUE}+99 9.99), -3) does not work.
    The formula is now always displaying a combination of 999.99. 3800 that should round to 4000 is now going to 3999.99.

  4. #4
    Join Date
    Jan 2016
    Location
    San Jose
    Posts
    4
    Quote Originally Posted by steph_io View Post
    Thank you for your feedback.
    This formula: truncate(({PROC_CONFIRM.MAX_INSURED_TRIP_VALUE}+99 9.99), -3) does not work.
    The formula is now always displaying a combination of 999.99. 3800 that should round to 4000 is now going to 3999.99.
    That would indicate that the truncate operation is being evaluated before the addition. Something like this: truncate({PROC_CONFIRM.MAX_INSURED_TRIP_VALUE},-3)+999.99 would do that.

    instead you want:

    truncate(({PROC_CONFIRM.MAX_INSURED_TRIP_VALUE}+99 9.99), -3)

    Just a matter of where the parens go.

    Actually, this might work just as well:

    truncate({PROC_CONFIRM.MAX_INSURED_TRIP_VALUE}+999 .99, -3)

    The idea is that you add the 999 to the variable, then truncate the result.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •