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

caffeine_demon

Board Regular
Joined
Apr 19, 2007
Messages
64
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.
 

Some videos you may like

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
Joined
Jan 27, 2010
Messages
628
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 12, 2010
Messages
13,458
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@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
Joined
Jan 27, 2010
Messages
628
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 12, 2010
Messages
13,458
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
Joined
Nov 12, 2010
Messages
13,458
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

1581536914154.png


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
Joined
Apr 19, 2007
Messages
64
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
Joined
Nov 12, 2010
Messages
13,458
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
Joined
Apr 18, 2011
Messages
36,438
Office Version
  1. 2010
Platform
  1. Windows
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&"."))
 

Watch MrExcel Video

Forum statistics

Threads
1,114,655
Messages
5,549,254
Members
410,905
Latest member
Extjel
Top