Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Created:
Jul 30, 2016 9:08 PM
| Last Modified: Oct 18, 2016 8:32 PM
(11947 views)

I have this script which does a simple calculation and displays result in a window:

subtotal = 100.05;

discountPercent = 0.1;

discountAmount = subtotal * discountPercent;

totalBeforeTax = subtotal - discountAmount;

formattedMessage = Concat(

"Subtotal:",Repeat(" ",5), Format(subtotal, "Currency"), "\!n",

"Discount Percent:", Repeat(" ",5), Format(discountPercent, "Percent"), "\!n",

"Discount Amount:", Repeat(" ",5), Format(discountAmount, "Currency"), "\!n",

"Total before tax:", Repeat(" ",5), Format(totalBeforeTax, "Currency"), "\!n"

);

formattedMessageDisplay = New Window( "Result",

Lineup Box( N Col( 1 ), Spacing( 0, 0 ), Border Box( Left( 20 ), Right( 20 ), top( 20 ), bottom( 20 ),

Text Box( formattedMessage, <<SetFontSize( 14 ) ) )

),

Show(subtotal, discountAmount, totalBeforeTax)

);

It produces the following, in the log and the output window :

So using Format(totalBeforeTax, "Currency"), results in totalBeforeTax being "rounded up". I can think of a variety of ways of handling this, but wondered what would be most recommended.

PDB

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

You might want to round/floor/ceiling the discountAmount calculation. Round has a second argument that you might want to set to 2 for digits after the decimal. Floor and ceiling will need something like floor(x*100)/100 to get a similar result.

subtotal = 100.05;

discountPercent = 0.1;

discountAmount = **round**(subtotal * discountPercent,**2)**;

totalBeforeTax = subtotal - discountAmount;

show(discountAmount, totalBeforeTax);

**discountAmount = 10.01;**

**totalBeforeTax = 90.04;**

The format function in your original code is on the knife-edge of rounding up or down with the 0.005 rounded to two places. The discrepancy in the presentation is triggered by the value having a minus in one case and a plus in the other...both values are rounded up, but one of them has negative discountAmount in the calculation.

Floating point numbers used in JMP represent numbers like 0.01 as a truncated repeating binary fraction. Just like 1/3 isn't exactly 0.333333333333, the internal representation isn't exact (except for binary fractions like 1/2, 1/4, 1/8, ...). if you add up 100 pennies, represented as 0.01, you'll get a number that is very close to 1.00, but different in the 16'th decimal place. This very tiny error has always made accounting packages choose another representation: java - Why not use Double or Float to represent currency? - Stack Overflow

total = 0;

For( i = 1, i <= 100, i++, total += .01 );

show( format(total,"fixed",20,16) );

**Format(total, "fixed", 20, 16) = "1.0000000000000007";**

Craige

6 REPLIES 6

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to handle rounding error

I don't do a lot of financial reporting, however, when I need to worry about rounding errors, I just use the functions Round(),Ceiling() and Floor().

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to handle rounding error

Hi Jim,

Thanks for you reply. So, Floor and Ceiling round to the nearest integer. Format (x, "Currency") basically appears to use Round(x,2). One would think the "Currency" format would employ a truncation method rather than rounding.

PDB

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

You might want to round/floor/ceiling the discountAmount calculation. Round has a second argument that you might want to set to 2 for digits after the decimal. Floor and ceiling will need something like floor(x*100)/100 to get a similar result.

subtotal = 100.05;

discountPercent = 0.1;

discountAmount = **round**(subtotal * discountPercent,**2)**;

totalBeforeTax = subtotal - discountAmount;

show(discountAmount, totalBeforeTax);

**discountAmount = 10.01;**

**totalBeforeTax = 90.04;**

The format function in your original code is on the knife-edge of rounding up or down with the 0.005 rounded to two places. The discrepancy in the presentation is triggered by the value having a minus in one case and a plus in the other...both values are rounded up, but one of them has negative discountAmount in the calculation.

Floating point numbers used in JMP represent numbers like 0.01 as a truncated repeating binary fraction. Just like 1/3 isn't exactly 0.333333333333, the internal representation isn't exact (except for binary fractions like 1/2, 1/4, 1/8, ...). if you add up 100 pennies, represented as 0.01, you'll get a number that is very close to 1.00, but different in the 16'th decimal place. This very tiny error has always made accounting packages choose another representation: java - Why not use Double or Float to represent currency? - Stack Overflow

total = 0;

For( i = 1, i <= 100, i++, total += .01 );

show( format(total,"fixed",20,16) );

**Format(total, "fixed", 20, 16) = "1.0000000000000007";**

Craige

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to handle rounding error

Thanks Craige, indeed I've been considering building an interface to allow JMP to access java's BigDecimal class. Are there any plans to include this as a type in the next version of JMP?

PDB

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to handle rounding error

Another thing I thought of was to convert to string and use Regex.

PDB

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: How to handle rounding error

No Big Number plans at this time. There were a lot of interesting points being made in the StackOverflow link. Different solutions might be appropriate depending on what you'll do with the numbers.

Craige

Craige

Article Labels

There are no labels assigned to this post.