finding how much decimal places numbers have

[dikken20]

Hey all,

in short, my purpose is to find the maximum decimal places among 6 prices.
meaning,
if I have the next prices (I'll list only 3 prices in the example, don't need more):
stock A: 100
stock B: 100.1
stock C: 100.11
then I would like to get the answer 2 as stock C has 2 decimal places which are more than 1 or 0 of stocks B and A.

so I wrote a function that does that, and for some reason when I play with the prices to check if it works it has some issues (in particular if all prices has no decimal and I add one decimal to a price it's ok, but as soon as I add another decimal to another stock [two stocks with one decimal each] then suddenly I get the answer 2 instead of 1)

can someone give me a hand on this please ? thanks

Function DecimalPlaces(ByVal bp1!, ByVal bp2!, ByVal bp3!, ByVal ap1!, ByVal ap2!, ByVal ap3!) As Byte 'bp = Bid Price
Dim nodp As Byte 'nodp = number of decimal places
nodp = 0
If (bp1 * 10 - CInt(bp1 * 10) <> 0) Or (bp2 * 10 - CInt(bp2 * 10) <> 0) Or (bp3 * 10 - CInt(bp3 * 10) <> 0) Or _
(ap1 * 10 - CInt(ap1 * 10) <> 0) Or (ap2 * 10 - CInt(ap2 * 10) <> 0) Or (ap3 * 10 - CInt(ap3 * 10) <> 0) Then
nodp = 2
ElseIf (bp1 - CInt(bp1) <> 0) Or (bp2 - CInt(bp2) <> 0) Or (bp3 - CInt(bp3) <> 0) Or _
(ap1 - CInt(ap1) <> 0) Or (ap2 - CInt(ap2) <> 0) Or (ap3 - CInt(ap3) <> 0) Then
nodp = 1
Else
nodp = 0

End If

DecimalPlaces = nodp
End Function

 

[VBA Geek]

okay, good I did not remember that the ! could be used instead of as single

That is incorrect. If you print TypeName(bp1) in dikken20's original implementation, you will see it is type Single.

That is because dikken20 wrote bp1! in the original implementation. Note the exclamation point; it causes the variable to be type Single.

(I prefer to write As Single explicitly.)

 

[joeu2004]

I would be greatful if you could tell me what is wrong with my original code as I need to learn from my mistake

The problem is: the expressions (bp1 * 10 - CInt(bp1 * 10) <> 0) and (bp1 - CInt(bp1) <> 0) are almost always true for most decimal fractions. So nodp = 2 almost always.

The reason is: type Single (and type Double) represent numbers using binary floating-point. So most decimal fractions cannot be represented exactly. For example, the exact internal representations are (type Double):

100.1:
100.099999999999,994315658113919198513031005859375

100.11:
100.109999999999,9994315658113919198513031005859375

I use a comma to demarcate 15 significant digits, the most that Excel and VBA format for type Double.

as far as I know ! refers to single, not as variant, correct me if I'm wrong..

You are correct. But using type Single compounds the problem. The exact internal representations are (type Single):

100.1!:
100.0999,9847412109375

100.11!:
100.1100,006103515625

Here, the comma demarcates 7 significant digits, the most that VBA formats for type Single.

Further compounding the problem is the fact that on Intel-compatible CPUs, VBA tries to rely on the internal 80-bit binary floating-point representation for intermediate results. Note the difference in the following code (press ctrl+G to see results in the Immediate Window).

Private Sub testit()
Dim s As Single, sx As Single, sy As Single
s = 100.1!: sx = s * 10: sy = CInt(s * 10)
Debug.Print s, sx, sy, (sx - sy <> 0), (s * 10 - CInt(s * 10) <> 0)

Dim d As Double, dx As Double, dy As Double
d = 100.1: dx = d * 10: dy = CInt(d * 10)
Debug.Print d, dx, dy, (dx - dy <> 0), (d * 10 - CInt(d * 10) <> 0)
End Sub

The additional precision of the 80-bit binary causes the subtraction to be non-zero. That is not the case when each subexpression is stored into a 64-bit binary first.

On the other hand, your use of CInt instead of Int is debatable, especially for this purpose. CInt rounds to an integer, whereas Int truncates. I believe the latter is your intent.

Using Int, the conditional expressions consistently produce an unintended True for both 80-bit and 64-bit representations. Again, the reason is: 100.1 cannot be represented exactly in binary.

why did you choose to use the array as Variant ? isn't that a waste of memory and might be problematic (in the meaning of miss interpertation of VBA to recognize the right type of variable) ?

As "VBA Geek" explained, the ParamArray qualifier requires a variable of type Variant. And contrary to any "misinterpretation", VBA correctly interprets the internal type of values passed by Excel as type Double.

But in general, I think you are unduly concerned about memory space. It does not make much difference for individual variables; and even for most array variables.

IMHO, usually you should use type Long for integers and type Double for non-integers. You are less likely to encounter implementation problems.

Almost never use type Single. It adversely affects the precision of arithmetic.

Notable exception: I use type Single for Timer results to ensure that the result of time arithmetic is a multiple of the system clock "tick" (15.625 msec). But I convert time values to type Double when printing and formatting to ensure that I see the full microsecond accuracy. Otherwise, again, VBA arbitrarily formats only up to 7 significant digits for type Single.

 

[joeu2004]

Addenda and errata....

why did you choose to use the array as Variant ? isn't that a waste of memory and might be problematic (in the meaning of miss interpertation of VBA to recognize the right type of variable) ?

As "VBA Geek" explained, the ParamArray qualifier requires a variable of type Variant. And contrary to any "misinterpretation", VBA correctly interprets the internal type of values passed by Excel as type Double.

I forgot to mention.... The issue is not mis-interpretation, but re-interpretation of the underlying date type in a type Variant variable.

The primary issue with type Variant is performance. Generally, it is more efficient to use type Double than type Variant that contains type Double.

However, that does not mean we should avoid type Variant altogether. ParamArray is one legitimate use of type Variant. Statements of the form "v = range" is another. Whether it is worthwhile to convert type Variant to a numeric type depends on the algorithm.

Private Sub testit()
Dim s As Single, sx As Single, sy As Single
s = 100.1!: sx = s * 10: sy = CInt(s * 10)
Debug.Print s, sx, sy, (sx - sy <> 0), (s * 10 - CInt(s * 10) <> 0)

Dim d As Double, dx As Double, dy As Double
d = 100.1: dx = d * 10: dy = CInt(d * 10)
Debug.Print d, dx, dy, (dx - dy <> 0), (d * 10 - CInt(d * 10) <> 0)
End Sub

The additional precision of the 80-bit binary causes the subtraction to be non-zero. That is not the case when each subexpression is stored into a 64-bit binary first.

I should have written: "stored into a 32-bit or 64-bit binary variable first". 32-bit for type Single; 64-bit for type Double.

I use type Single for Timer results to ensure that the result of time arithmetic is a multiple of the system clock "tick" (15.625 msec).

I should have written: "elapsed-time arithmetic". For other time arithmetic, I convert type Single elapsed time to type Double; for example, dividing elapsed time to determine time per iteration.

 

[dikken20]

Thank you for the detailed answer. really made me undetstand things I did not know.

one further question:
yo mentioned that the precision for either Single and Double will cause a detailed decimal digits.
meaning,
100.1:
100.099999999999,994315658113919198513031005859375

100.11:
100.109999999999,9994315658113919198513031005859375

my question is:
Is there any way to just have the "simple" number, meaning, 100.1 will keep be 100.1 and not 100.099999.... ?

 

[joeu2004]

I'm sorry: I did not see this question until today, 12 days later. This forum's GUI and features leave much to be desired.

one further question: yo mentioned that the precision for either Single and Double will cause a detailed decimal digits. meaning,
100.1:
100.099999999999,994315658113919198513031005859375

100.11:
100.109999999999,9994315658113919198513031005859375

my question is:
Is there any way to just have the "simple" number, meaning, 100.1 will keep be 100.1 and not 100.099999.... ?

The short answer is "no".

But I think you misunderstand: Excel is not changing 100.1 to 100.09999..., for example.

Instead, that is simply the most accurate representation of 100.1 within the limitations of 64-bit binary floating-point, which is how Excel stores type Double numbers.

Type Double numbers are represented by the sum of 53 consecutive powers of 2 ("bits") times an exponential factor.

(Type Single numbers are represented by the sum of 24 consecutive powers of 2 times an exponential factor.)

In this case, 7 bits are used to represent the integer part (100). That leaves 46 bits to represent the fractional part (0.1).

But 0.1 cannot be represented exactly as the sum of powers of 2, no matter how many bits we can use. So there is an infinitesimal difference above or below 0.1.

The degree of the difference ("error") depends on the number of bits we can use for the fractional part.

Try it yourself on paper [1]: 0/2 + 0/4 + 0/8 + 1/16 + 1/32 + 0/64 + 0/128 + 1/256 + 1/512 + 0/1024 + etc.


-----
[1] If you try to use Excel to do the sum, the results might be misleading at some point for two reasons.

First, Excel formats only as many as 15 significant digits, rounding any remaining digits. That is why 100.1 looks like 100.100000000000 in the first place.

Second, Excel has an arbitrary heuristic that forces truly different values to be treated as equal if they are "close enough". That might adversely impact your algorithm.

The work-around: instead of E1+C2<=0.1, use E1+C2-0.1<=0. Of course, there is no difference mathematically. But it makes a difference in Excel because of the inconsistent implementation of the dubious heuristic.