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)

"Yara" wrote in message
...
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

Flaw designed...

You have to use round function

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


"Yara" wrote:

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)

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)


"Bob Phillips" wrote in message
...
=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)

"Yara" wrote in message
...
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

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
"Yara" wrote:

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

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/micros...d801f7 a87bb3



--

Regards,

Peo Sjoblom




"Yara" wrote in message
...

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
"Yara" wrote:

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

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


Replace @mailinator.com with @tiscali.co.uk


"Yara" wrote in message
...
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

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
--
David Biddulph

"Yara" wrote in message
...
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

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

Rick


"Teethless mama" wrote in message
...
Flaw designed...

You have to use round function

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


"Yara" wrote:

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

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).
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
Do you get your formula to work on all numbers? I have done some quick
testing and I am not seeing correct results.

Rick

"Teethless mama" wrote in
message ...
Flaw designed...

You have to use round function

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

"Yara" wrote:

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

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


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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).
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
Do you get your formula to work on all numbers? I have done some quick
testing and I am not seeing correct results.

Rick

"Teethless mama" wrote in
message ...
Flaw designed...

You have to use round function

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

"Yara" wrote:

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

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)

"Yara" wrote in message
...
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

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.
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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).
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
Do you get your formula to work on all numbers? I have done some quick
testing and I am not seeing correct results.

Rick

"Teethless mama" wrote in
message ...
Flaw designed...

You have to use round function

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

"Yara" wrote:

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

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


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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.
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
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).
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
Do you get your formula to work on all numbers? I have done some quick
testing and I am not seeing correct results.

Rick

"Teethless mama" wrote in
message ...
Flaw designed...

You have to use round function

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

"Yara" wrote:

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

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


"Chip Pearson" wrote in message
...
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)

"Yara" wrote in message
...
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

On Sun, 7 Oct 2007 11:33:02 -0700, Yara wrote:

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

On Sun, 07 Oct 2007 19:39:44 -0400, Ron Rosenfeld
wrote:

On Sun, 7 Oct 2007 11:33:02 -0700, Yara wrote:

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

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