PDA

View Full Version : Solved: Problem counting decimal places



boston4415
04-13-2009, 02:32 PM
I'm using Microsoft 2003 and I have a problem I'm hoping someone can mimick and tell my why this happens or am I going nuts.

I'm trying to count the decimal places for each number in a long list of numbers and getting the wrong count in some cases.

For example

enter in (cell A1) 103,540.8467 below it enter (cell A2)103,540 and below it enter (cell A3) =+A1-A2

In (Cell A3) should give you 0.8467 and now below it enter (cell A4)
=LEN(A3)-2 Which should be the length count to the right of the decimal. I minus 2 to count for the 0 and decimal place.

I get 15 and not 4? If I expand the decimal places in cell A3 it goes out 15 places - not sure why this happens.

lenze
04-13-2009, 02:59 PM
First, are you sure there aren't extra digits that do not show because of formatting? If not, then you can use


=IFLEN(ISNUMBER(FIND(".",A1)),RIGHT(A1,LEN(A1)-FIND(".",A1)),0)) or for the count

=LEN(IF(ISNUMBER(FIND(".",A1)),RIGHT(A1,LEN(A1)-FIND(".",A1)),""))

lenze

Paul_Hossler
04-13-2009, 03:42 PM
There's the display, and there is the internal representation of numbers

What looks like 0.8467 in A3, is really 0.846699999994598

Click on A3, hit F2 to go into the formula bar, and then hit F9 to see the results of the formula.

What are you trying to do? There might be a better way.

Paul

boston4415
04-14-2009, 05:12 AM
Paul - You were able to recreate the same thing.

I see that excel is reporting the decimal number out 15 places - Why does it do that?

I hand type only 4 decimal number and subtract a whole number and I end up with a number with 15 decimals - I'm not sure why excel does that.

What I am trying to determine from a list of 5,000 numbers which line items have more than 4 decimals.

Norie
04-14-2009, 05:29 AM
Paul - You were able to recreate the same thing.

I see that excel is reporting the decimal number out 15 places - Why does it do that?

I hand type only 4 decimal number and subtract a whole number and I end up with a number with 15 decimals - I'm not sure why excel does that.

What I am trying to determine from a list of 5,000 numbers which line items have more than 4 decimals.
It isn't only Excel that does this sort of thing with decimals, it's intrinsic to the 'floating point' method that computers use to store such numbers.

boston4415
04-14-2009, 05:44 AM
Thanks for the info - I need to better understand computing 'floating point'

I was trying to get to the decimal count in steps but Lenze's formulas work if I use it on the original number field.

Thanks for the help