LEN function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
=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)
 
=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)
 
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
 
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
 
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
 
Do you get your formula to work on all numbers? I have done some quick
testing and I am not seeing correct results.

Rick
 
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).
 
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
 
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)
 
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.
 
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
Click to expand...

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
 
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
 
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
Click to expand...

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
 
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
Click to expand...

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