Have I discovered a bug? - Counting number of decimal places

[caffeine_demon]

Hi,

So - I wanted to find a way of counting the number of decimal places in a number, so my idea was:
- Subtract the "int" part of a number from the number. (Ie a1-int(a1) ) - giving just the decimal value
- use "len" to calculate the length of that, and subtract 2 (for the 0, and the decimal point) - (so - Len(a1-int(a1)) - 2 )

I was getting strange results, if we start with :
A1 = 2.05
A2 = Int(a1)
A3 = A1-A2
a4 = len(a3) -2

a2 results in "2" - as expected, a3 shows "0.05" - as expected, but a4 is saying the length is 16. Going into the formula wizard, it tells me the value of A3 is "0.49999999999989" (there may be more 9s)

trying values from 2 up to 4 (in steps of 0.01) - it appears only 2, 3 and 4 give the correct result

Also - if I just enter the formula "Len(2.05-2) -2" , that also gives 16.

Is there something I've missed, or have I found a bug? is there a better way to calculate the number of decimal places?

(This is office pro 365 at work)..

Thanks.

 

[Joe4]

Nope, you haven't discovered anything new. Just understanding how Microsoft stores numbers and does calculations.
See: Floating-point arithmetic may give inaccurate result in Excel - Office

 

[6StringJazzer]

It is not a bug. There is an industry engineering standard for how real numbers are stored, and Excel conforms to it. It is a little complicated. Computers use binary words to store real numbers, and sometimes decimal real numbers cannot be expressed exactly as a binary number. This is easier to understand if you realize the the same thing is true for decimal numbers. The fraction 2/3 is an exact real number but you can't write it as a decimal number.

In complex numerical applications, software developers have to be aware of this. For example, it is not safe to perform two different calculations using real numbers and then compare the results for equality.

 

[MARK858]

@caffeine_demon, also if you want the decimal part of the number you can just use MOD...
It won't make any difference to your floating point issue though, as the article posted by Joe4 says you can get around it by wrapping it in a Round function.

Book1
	A	B	C
1	2.05	0.05	16
2	2.05	0.05	2
Sheet1

Cell Formulas
Range	Formula
B1	B1	=MOD(A1,1)
C1:C2	C1	=LEN(B1)-2
B2	B2	=ROUND(MOD(A2,1),2)

 

[6StringJazzer]

Using ROUND can mitigate many issues with using real numbers, but for this particular question it defeats the purpose of counting the number of digits after the decimal place. If you use ROUND you may lose significant digits.

 

[MARK858]

If you use ROUND you may lose significant digits.

Then the correct answer if the OP doesn't know the maximum amount of significant digits that is acceptable for their data precision is the 16 with the floating point issue.

 

[MARK858]

I suppose the OP could use a UDF to get the displayed Length but that is no more accurate than using round.
Cell A2 actually has the entered number 2.0555553555555 when formatted as General is displaying as

Function VisualLen(myRng As Range)
    VisualLen = Len(myRng(1).Text)
End Function

Book1
	A	B	C	D	E	F	G	H
1	2.05	4	4			0.05	4	18
2	2.055555356	11	15			0.055555	8	18
Sheet1

Cell Formulas
Range	Formula
B1,G1	B1	=VisualLen(A$1)
C1:C2,H1:H2	C1	=LEN(A1)
B2,G2	B2	=VisualLen(A2)
F1:F2	F1	=MOD(A1,1)

 

[caffeine_demon]

Thanks guys, rounding's perfectly ok for my data (nothing more than 5 dps), I have had similar happen in SAS now I think about it!

Anyone remember the pentium bug?

 

[MARK858]

Happy you found a solution that you can live with

Anyone remember the pentium bug?

Vaguely remember the fuss as there were arguments (mainly with their rivals) over how often the error could occur and how they handled the situation..

 

[Rick Rothstein]

I believe this formula will give you the number of decimal places either in the number shown in the Formula Bar if the entered number is a constant or the number decimal places that would be shown in the formula bar if the value from a formula value would be copied and have its value pasted into a cell...

=MAX(0,LEN(A1)-FIND(".",A1&"."))