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

#### caffeine_demon

##### Board Regular
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.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### 6StringJazzer

##### Well-known Member
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

##### MrExcel MVP
@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
ABC
12.050.0516
22.050.052
Sheet1
Cell Formulas
RangeFormula
B1B1=MOD(A1,1)
C1:C2C1=LEN(B1)-2
B2B2=ROUND(MOD(A2,1),2)

#### 6StringJazzer

##### Well-known Member

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

##### MrExcel MVP
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

##### MrExcel MVP

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

VBA Code:
``````Function VisualLen(myRng As Range)
VisualLen = Len(myRng(1).Text)
End Function``````

Book1
ABCDEFGH
12.05440.05418
22.05555535611150.055555818
Sheet1
Cell Formulas
RangeFormula
B1,G1B1=VisualLen(A\$1)
C1:C2,H1:H2C1=LEN(A1)
B2,G2B2=VisualLen(A2)
F1:F2F1=MOD(A1,1)

#### caffeine_demon

##### Board Regular
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

##### MrExcel MVP
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

##### MrExcel MVP
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&"."))

Replies
1
Views
95
Replies
0
Views
76
Replies
14
Views
262
Replies
3
Views
102
Replies
4
Views
69