OK, just call me hasty the last week or so. I was incorrect in saying it
is necessary to convert (1) to currency before subtracting a currency from
it. VBA or the expression service automatically converts to Currency in
this case. Converting the constant to currency is only necessary if the
constant is a fraction.
On Tue, 16 Sep 2003 15:54:31 GMT, Steve Jorgensen <nospam@nospam.nospam>
wrote:
[color=blue]
>On Tue, 16 Sep 2003 12:33:26 GMT, "Allen Browne"
><abrowne1_SpamTrap@bigpond.net.au> wrote:
>[color=green]
>>What is your understanding of banker's rounding - that 5 is rounded towards
>>the EVEN number? $44.96 would seem to be the closest even number ot $44.955.
>>
>>There are a (very) few cases where floating point numbers do not round as
>>you would expect. You can deal with those by explicitly typecasting to
>>Currency, i.e.:
>> Round(CCur([Price] * (1 - [Discount])), 2)
>>
>>Since the Currency type cannot be Null, you may need Nz() inside CCur() as
>>well.[/color]
>
>I've found it takes more than this. Every part of the expression must be
>converted to currency so, for instance, assuming [Discount] is already
>currency, you need Round(CCur([Price] * (CCur(1) - [Discount])), 2). Also,
>if you ever divid, you have to wrap the division in a CCur(...) or that
>part of the result will be converted to Double. Note that, if you read all
>Microsoft's documentation, it would seem that CCur(<currency>/<currency>)
>would always produce a Double, then conver to Currency, but testing has
>shown that it works more intelligently, and actually does a Currency
>division in this case, never producing an intermediate Double (certain
>known cases of rounding error with Double division do not occur).
>
>Next, most companies I've worked with compute a discount amount, then
>subtract that from the price rather than computing Price*(1-Discount).
>Doing it this way, the discount on 49.95 is 4.995 which rounds to 5.00.
>Subtracting that from 49.95 gives 44.95. When the last digit of the price
>is odd, these 2 methods of calculating the discount always produce
>different results. It's a question of whether you're rounding the
>discounted price, or rounding the discount.[/color]
