2 decimal place accuracy

[JohnPapa]

As long as the user is not allowed to enter more than 2dp, then Currency is the way to go.

 

[The_Doc_Man]

The_Doc_Man: Stating the obvious, if not properly handled

76.07 - 67 = 9.06999999999999

This implies that 76.07 was stored in a SINGLE or DOUBLE since this won't happen in CURRENCY data types and won't happen in scaled LONG data types. The truth of the matter is that the "67" in that expression is window dressing, since that "76.07" was already stored with the seeds of its own inaccuracy already planted. The problem is that some fractions are inherently irrational (in the formal mathematical sense) when expressed in binary. Therefore, if you are worried about your representation, pick a method that doesn't run into the problem in the first place.

In decimal vs. binary for fractional numbers, we have to remember that 0.1 is irrational because it is formed by 1/(2*5) and thus 1/2*1/5. The 1/2 is a piece of cake in binary... but the 5 is not because 2 is not a factor of 5. EVERY ODD NUMBER in decimal has the potential to be irrational. Except for numbers like 0.5, 0.25, 0.125, etc., you cannot express decimal fractions evenly in binary.

The only problem I can see with a Currency field is for the 3rd or 4th digit to alter the first two digits.

If you do something in the code that would introduce a fraction, yes you would eventually accumulate something. Which is why you have to watch what you are doing with the number. When you are doing ONLY addition or subtraction operations, your concern of altering digits doesn't apply because there is no mathematical way to add digits that weren't already there. Multiplication by a fractional amount or any type of division WOULD introduce 3rd or 4th digits - but the simplest solution then becomes that you can head this off at the pass by using a function to truncate the result, such as VBA's TRUNC( number, places) after you have done something that could introduce a 3rd or 4th digit.

TRUNC function - Microsoft Support

Truncates a number to an integer by removing the fractional part of the number. TRUNC removes the fractional part of the number. INT and TRUNC are different only when using negative numbers: TRUNC(-4.3) returns -4, but INT(-4.3) returns -5 because -5 is the lower number.

support.microsoft.com

 

[Pat Hartman]

The only problem I can see with a Currency field is for the 3rd or 4th digit to alter the first two digits.

You don't get floating point errors but if you want only two decimal positions, you MUST round calculations that will potentially increase the decimal places.

As long as the user is not allowed to enter more than 2dp, then Currency is the way to go.

Calculations other than +- can cause extra decimal places. Currency will round to 4 BUT you need to specifically round to 2 if that is what you want. Addition and subtraction when using scaled integers such as decimal and currency do not generate extra decimal places. It is the rounding errors when converting from binary to decimal that cause them as Doc mentioned.

I ALWAYS use currency whenever I don't need extra decimal places. You can format it however you want so I also keep my percents as currency. Decimal takes more storage space and used to be flaky but those issues seem to have been fixed.

 

[JohnPapa]

You don't get floating point errors but if you want only two decimal positions, you MUST round calculations that will potentially increase the decimal places.


Calculations other than +- can cause extra decimal places. Currency will round to 4 BUT you need to specifically round to 2 if that is what you want. Addition and subtraction when using scaled integers such as decimal and currency do not generate extra decimal places. It is the rounding errors when converting from binary to decimal that cause them as Doc mentioned.

I ALWAYS use currency whenever I don't need extra decimal places. You can format it however you want so I also keep my percents as currency. Decimal takes more storage space and used to be flaky but those issues seem to have been fixed.

So to summarize, one way to go is to use the Currency data type and round to 2dp for multiplication and division operations, even though the user is not allowed to enter more than 2dp.

 

[gemma-the-husky]

If anyone is interested I did some tests as indicated below,
Double is not OK
Single, Currency are OK

Dim Dum1 As Double
Dim Dum2 As Double
Dim Dum3 As Double


Dum1 = 76.07
Dum2 = 67


Dum1 = Int((Dum1 + 0.005) * 100) / 100
Dum2 = Int((Dum2 + 0.005) * 100) / 100
Dum3 = Int(((Dum1 - Dum2) + 0.005) * 100) / 100
' Dum3 = 9.07




Dim Dum1c As Currency
Dim Dum2c As Currency
Dim Dum3c As Currency


Dum1c = 76.07
Dum2c = 67
Dum3c = Dum1c - Dum2c
' Dum3c = 9.07


Dim Dum1d As Double
Dim Dum2d As Double
Dim Dum3d As Double


Dum1d = 76.07
Dum2d = 67
Dum3d = Dum1d - Dum2d
' Dum3d = 9.699999999 etc


Dim Dum1s As Single
Dim Dum2s As Single
Dim Dum3s As Single


Dum1s = 76.07
Dum2s = 67
Dum3s = Dum1s - Dum2s
' Dum3s = 9.07

Single may work with that example, but will fail with others. You just have to appreciate the inherent problems of working with real numbers, and be careful about precision. Certainly be careful about testing equality with real numbers.

It's no different to evaluating percentages of a column of numbers (or rounded values) against the column total. Often the rounded total will not sum correctly, and there is no way to sort it automatically.

Eg. 3 equal items can't sum to 100% at any level of rounding, when expressed as a percentage of the total, since they will all round down. So you have to manually set one to 34%, and leave the others as 33%. At 2 dp's they will all be 33.33% and will sum to 99.99%

Calculating VAT on individual items will often give a different total to calculating the VAT on the total of the items.

With my accountancy experience, we often had to manually "float" a percentage, or a rounded value the "wrong" way to get a total to add correctly. Just a fact of life.

 

[JohnPapa]

Single may work with that example, but will fail with others. You just have to appreciate the inherent problems of working with real numbers, and be careful about precision. Certainly be careful about testing equality with real numbers.

It's no different to evaluating percentages of a column of numbers (or rounded values) against the column total. Often the rounded total will not sum correctly, and there is no way to sort it automatically.

Eg. 3 equal items can't sum to 100% at any level of rounding, when expressed as a percentage of the total, since they will all round down. So you have to manually set one to 34%, and leave the others as 33%. At 2 dp's they will all be 33.33% and will sum to 99.99%

Calculating VAT on individual items will often give a different total to calculating the VAT on the total of the items.

With my accountancy experience: we often had to manually "float" a percentage, or a rounded value the "wrong" way to get a total to add correctly. Just a fact of life.

The 33.3 (recuring) problem (and plenty others) needs manual intervention as you very rightly mention.