435,639 Members | 2,276 Online
Need help? Post your question and get tips & solutions from a community of 435,639 IT Pros & Developers. It's quick & easy.

# rounding error issues on invoices

 P: n/a I have a report/invoice that lists volume, price per unit, and extended price. My problem is being able to get the extended price by multiplying the [volume] x [price per unit]. With just 2 decimal places on both volume and [unit price] I've discovered there is not way to get the exact [extened price] on invoices with extended price values greater then 50k due to rounding errors. This customer wants only 2 decimal places on the invoice. Is there an industry standard for this? The [extended price] must match the companies internal accounting system so it can't be fiddled with. What do other companies do in this scenario? Nov 13 '05 #1
5 Replies

 P: n/a Have you got some examples? I'd be inclined to store the underlying numbers in more precise fields and limit the display output to 2 decimal places... that should help you get the precision you need when calculating... -- Message posted via http://www.accessmonster.com Nov 13 '05 #2

 P: n/a If you multiply 2 numbers that have 2 decimal places each, you can get 4 places in the result, e.g.: ? .01 * .01 0.0001 Try using an expression like this as the calculated field in your query: ExtendedPrice: CCur(Round(Nz([volume] * [price per unit],0),2)) That expression: - handles the nulls (because CCur() barfs on Null); - rounds the result to 2 places; - typecasts that result to Currency. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "astro" wrote in message news:_n*********************@twister.rdc-kc.rr.com... I have a report/invoice that lists volume, price per unit, and extended price. My problem is being able to get the extended price by multiplying the [volume] x [price per unit]. With just 2 decimal places on both volume and [unit price] I've discovered there is not way to get the exact [extened price] on invoices with extended price values greater then 50k due to rounding errors. This customer wants only 2 decimal places on the invoice. Is there an industry standard for this? The [extended price] must match the companies internal accounting system so it can't be fiddled with. What do other companies do in this scenario? Nov 13 '05 #3

 P: n/a Thanks folks - I'm not being clear here... my point is that there is no way to get the [exteneded sum] by using only 2 decimals for the inputs (volume & [unit price]) ...the rounding that a custom agent's calculator would do would make that impossible. Here is an example: volume: 108.88 kg unit price: 16.4569 ===>> round up to 16.46 invoice [extended price]: 1791.83 value #1 (16.46) yields = [extended price]: (108.88)(16.46) = - above true price value #2 (16.45) yields = [extended price]: (108.88)(16.45) = - below true price I cannot change the true [extended price]...it must match the accounting dept's value. My only choice seem to be extending the precision of [unit price] to 4 decimal places...this yields: (16.4569)(108.88) = 1791.83 Of course this will only work for results below a certain threshold (50k maybe) ...at which point I would need more decimals to get the exact [extended price]. What I want to know is this: is there an industry standard for handling this situation - one standard might be that on shipping invoices the [unit price] * [quantity] does not have to exactly match the [extended price]... which is of course not the line i'm getting from my client... Or maybe shipping invoices with large \$\$ on them display 6 decimal places to insure an exact match..... I don't know -- haven't been here before...wondering if anyone else has dealt with this before... Thanks "astro" wrote in message news:_n*********************@twister.rdc-kc.rr.com... I have a report/invoice that lists volume, price per unit, and extended price. My problem is being able to get the extended price by multiplying the [volume] x [price per unit]. With just 2 decimal places on both volume and [unit price] I've discovered there is not way to get the exact [extened price] on invoices with extended price values greater then 50k due to rounding errors. This customer wants only 2 decimal places on the invoice. Is there an industry standard for this? The [extended price] must match the companies internal accounting system so it can't be fiddled with. What do other companies do in this scenario? Nov 13 '05 #4

 P: n/a Um, I think you're really talking about business standards, rather than technical standards. I'd have thought that, legally, the visible math has to add up ie. 108.88 * \$16.46 = \$1792.1648 -> rounds to \$1792.16 Some industries have a convention of rounding up or down to the nearest unit of measure they're willing to accept eg. to the nearest dollar, but I'd have thought that woul dneed to be on their terms & conditions somewhere. If you can't change the extended price, then I think you'd have to display the unit price to its full number of decimal places. -- Message posted via http://www.accessmonster.com Nov 13 '05 #5

 P: n/a Yes - that would make sense to put it under the 'terms and conditions' - I will ask my client about this.. Thanks! "David Seeto via AccessMonster.com" wrote in message news:b1******************************@AccessMonste r.com... Um, I think you're really talking about business standards, rather than technical standards. I'd have thought that, legally, the visible math has to add up ie. 108.88 * \$16.46 = \$1792.1648 -> rounds to \$1792.16 Some industries have a convention of rounding up or down to the nearest unit of measure they're willing to accept eg. to the nearest dollar, but I'd have thought that woul dneed to be on their terms & conditions somewhere. If you can't change the extended price, then I think you'd have to display the unit price to its full number of decimal places. -- Message posted via http://www.accessmonster.com Nov 13 '05 #6

### This discussion thread is closed

Replies have been disabled for this discussion.