LEN function

[Yara]

Hi,

Did anybody tried the following formula:

LEN(A1-INT(A1)) the output should be the number of decimal digits
of the number in cell A1.

Examples:
in cell A1 input the following formual: +LEN(A2-INT(A2))
in cell A2 input different numbers like the following:

Cell A2 Cell A1
2.2 3
5 1
10.25 4
9.2 17 What
100.1 18 What again !!!!

Can anyone figure what is happening

Yara

 

[Bob Phillips]

=MAX(LEN(A1)-LEN(INT(A1))-1,0)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Teethless mama]

Flaw designed...

You have to use round function

=LEN(ROUND(A2-INT(A2),2))

 

[Chip Pearson]

=MAX(LEN(A1)-LEN(INT(A1))-1,0)

That won't work for negative numbers. Instead, convert to positive with

=MAX(LEN(ABS(A1))-LEN(INT(ABS(A1)))-1,0)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Yara]

Hi Bob, Teethless mama

Thank you both for your answers, Teethless mama formual is simply.

But, why should go arround the problem where it should be simple by just
using LEN function only.

Anyhow thank you both for your help.

Yara

 

[Peo Sjoblom]

You posted the same question a while ago and you got several answers and a
link to an explanation then, please don't multipost

http://groups.google.es/group/micro...87bb3?hl=en&lnk=st&q=&rnum=4#65f8d801f7a87bb3



--

Regards,

Peo Sjoblom

 

[Sandy Mann]

This is a consequence of floating point arithmetic. With 9.2 in A1, and the
formula:

=LEN(A1-INT(A1)) in another cell, if I highlight just:

=A1-INT(A1)

and press F9 to calculate that part I get:

0.199999999999999

returned, Thus the 17

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[David Biddulph]

You had an answer from Peo Sjoblom when you asked the question two days ago
in another group. Asking the question in a different group two days later
won't change the truth.

If you don't like the answer, tell us the exact binary representation of 9.2

 

[Rick Rothstein \(MVP - VB\)]

Do you get your formula to work on all numbers? I have done some quick
testing and I am not seeing correct results.

Rick

 

[David Biddulph]

Anything with more than 2 decimal places will, of course, be rounded to only
2 places, so I'm not sure why 2 was the chosen number (apart from that being
highest number of decimal places in the OP's examples).

 

[Rick Rothstein \(MVP - VB\)]

I'm getting stranger results than that... for example, 123 (no decimal) is
returning 1, 12.3 is returning 3, 1.23 is returning 4, etc. Do you get these
results too?

Rick

 

[Chip Pearson]

You can get around any problems with INT and rounding and negative numbers
by treating A1 as a string rather than a number.

=IF(ISNUMBER(A1),
IF(ISERROR(FIND(".",A1)),0,LEN(MID(A1,FIND(".",A1)+1,99))),NA())


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[David Biddulph]

That's what I'd expect. A2-INT(A2) is going to return 0, 0.3, and 0.23,
respectively, so 1, 3, and 4 seem the right lengths for the strings. The
12.3 to 0.3 case is one where the binary representation approximation error
will return 17 as the string length if you don't do the rounding.

 

[Rick Rothstein \(MVP - VB\)]

The OP said in his first posting...

LEN(A1-INT(A1)).... the output should be the number of
decimal digits of the number in cell A1

so I presumed we were trying to find a formula that would return the number
of digits **after** the decimal point. Am I wrong in that assumption? If
not, then the answers I get back from Teethless mama's formula are not doing
that.

Rick

 

[Rick Rothstein \(MVP - VB\)]

This is the formula I posted over in the 2-day old thread in
worksheet.functions...

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

Notice the trick I used to eliminate having to do an ISERROR check on the
FIND function. I didn't look at your formula carefully (the wife is calling
me for dinner), but you might be able to use the trick in your solution too.

Rick

 

[Ron Rosenfeld]

Hi,

Did anybody tried the following formula:

LEN(A1-INT(A1)) the output should be the number of decimal digits
of the number in cell A1.

Examples:
in cell A1 input the following formual: +LEN(A2-INT(A2))
in cell A2 input different numbers like the following:

Cell A2 Cell A1
2.2 3
5 1
10.25 4
9.2 17 What
100.1 18 What again !!!!

Can anyone figure what is happening

Yara

100.1-int(100.1) does not equal .1 due to Excel's defined precision, well
explained in other posts.

If you want to count the number of digits to the right of the decimal point,
then try this:

=IF(ISERR(FIND(".",A1)),0,LEN(MID(A1,FIND(".",A1)+1,255)))


--ron

 

[Ron Rosenfeld]

100.1-int(100.1) does not equal .1 due to Excel's defined precision, well
explained in other posts.

If you want to count the number of digits to the right of the decimal point,
then try this:

=IF(ISERR(FIND(".",A1)),0,LEN(MID(A1,FIND(".",A1)+1,255)))


--ron

Never mind. Others have posted better solutions.
--ron