A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

how to count the number of decimal places in a cell?



 
 
Thread Tools Display Modes
  #1  
Old August 27th 05, 02:43 PM
Warren Smith
external usenet poster
 
Posts: n/a
Default how to count the number of decimal places in a cell?

The worksheet I am doing needs to look at cell values, and in another return
the number of decimal places used in the cell.

for example
if A1 contained a number of 34.45,
I want B1 to tell me 2.
I need to know this because I want to enter the data in a table against
another set of figures which will have 1 more decimal place than the first
set,
and I can't set the decimal places up before because I don't know how many
there will be.

Thanks in advance for any help
Warren


Ads
  #2  
Old August 27th 05, 03:03 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default

=IF(ISNUMBER(FIND(".",A20)),LEN(A20)-FIND(".",A20),0)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Warren Smith" > wrote in message
...
> The worksheet I am doing needs to look at cell values, and in another

return
> the number of decimal places used in the cell.
>
> for example
> if A1 contained a number of 34.45,
> I want B1 to tell me 2.
> I need to know this because I want to enter the data in a table against
> another set of figures which will have 1 more decimal place than the first
> set,
> and I can't set the decimal places up before because I don't know how many
> there will be.
>
> Thanks in advance for any help
> Warren
>
>



  #3  
Old August 27th 05, 05:12 PM
KL
external usenet poster
 
Posts: n/a
Default

....just in case the file might be used in other language environments, the
following formula avoids using the decimal separator explicitly:

=IF(ISNUMBER(A1),LEN(MOD(ABS(A1),1))-1-(MOD(A1,1)>0),0)

It also uses IF(ISNUMBER(A1),...,0) construct to check if there is text in
the cell A1. If there can only be numbers or empty cells then a shorter
version can be used:

=LEN(MOD(ABS(A1),1))-1-(MOD(A1,1)>0)

Regards,
KL



"Bob Phillips" > wrote in message
...
> =IF(ISNUMBER(FIND(".",A20)),LEN(A20)-FIND(".",A20),0)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Warren Smith" > wrote in message
> ...
>> The worksheet I am doing needs to look at cell values, and in another

> return
>> the number of decimal places used in the cell.
>>
>> for example
>> if A1 contained a number of 34.45,
>> I want B1 to tell me 2.
>> I need to know this because I want to enter the data in a table against
>> another set of figures which will have 1 more decimal place than the
>> first
>> set,
>> and I can't set the decimal places up before because I don't know how
>> many
>> there will be.
>>
>> Thanks in advance for any help
>> Warren
>>
>>

>
>



  #4  
Old August 27th 05, 05:25 PM
Warren Smith
external usenet poster
 
Posts: n/a
Default

Thank you very much , that worked great!

"KL" > wrote in message
...
> ...just in case the file might be used in other language environments, the
> following formula avoids using the decimal separator explicitly:
>
> =IF(ISNUMBER(A1),LEN(MOD(ABS(A1),1))-1-(MOD(A1,1)>0),0)
>
> It also uses IF(ISNUMBER(A1),...,0) construct to check if there is text in
> the cell A1. If there can only be numbers or empty cells then a shorter
> version can be used:
>
> =LEN(MOD(ABS(A1),1))-1-(MOD(A1,1)>0)
>
> Regards,
> KL
>
>
>
> "Bob Phillips" > wrote in message
> ...
>> =IF(ISNUMBER(FIND(".",A20)),LEN(A20)-FIND(".",A20),0)
>>
>> --
>>
>> HTH
>>
>> RP
>> (remove nothere from the email address if mailing direct)
>>
>>
>> "Warren Smith" > wrote in message
>> ...
>>> The worksheet I am doing needs to look at cell values, and in another

>> return
>>> the number of decimal places used in the cell.
>>>
>>> for example
>>> if A1 contained a number of 34.45,
>>> I want B1 to tell me 2.
>>> I need to know this because I want to enter the data in a table against
>>> another set of figures which will have 1 more decimal place than the
>>> first
>>> set,
>>> and I can't set the decimal places up before because I don't know how
>>> many
>>> there will be.
>>>
>>> Thanks in advance for any help
>>> Warren
>>>
>>>

>>
>>

>
>



  #5  
Old August 27th 05, 05:54 PM
Bob Phillips
external usenet poster
 
Posts: n/a
Default

KL,

This doesn't work for me at all, it returns 15 for 34.45.

Looking at it MOD(ABS(A1),1) evaluated to .450000000000003, which I would
take is caused by lack of precision when using MOD.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"KL" > wrote in message
...
> ...just in case the file might be used in other language environments, the
> following formula avoids using the decimal separator explicitly:
>
> =IF(ISNUMBER(A1),LEN(MOD(ABS(A1),1))-1-(MOD(A1,1)>0),0)
>
> It also uses IF(ISNUMBER(A1),...,0) construct to check if there is text in
> the cell A1. If there can only be numbers or empty cells then a shorter
> version can be used:
>
> =LEN(MOD(ABS(A1),1))-1-(MOD(A1,1)>0)
>
> Regards,
> KL
>
>
>
> "Bob Phillips" > wrote in message
> ...
> > =IF(ISNUMBER(FIND(".",A20)),LEN(A20)-FIND(".",A20),0)
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Warren Smith" > wrote in message
> > ...
> >> The worksheet I am doing needs to look at cell values, and in another

> > return
> >> the number of decimal places used in the cell.
> >>
> >> for example
> >> if A1 contained a number of 34.45,
> >> I want B1 to tell me 2.
> >> I need to know this because I want to enter the data in a table against
> >> another set of figures which will have 1 more decimal place than the
> >> first
> >> set,
> >> and I can't set the decimal places up before because I don't know how
> >> many
> >> there will be.
> >>
> >> Thanks in advance for any help
> >> Warren
> >>
> >>

> >
> >

>
>



  #6  
Old August 28th 05, 08:22 AM
Harlan Grove
external usenet poster
 
Posts: n/a
Default

"Bob Phillips" > wrote...
>This doesn't work for me at all, it returns 15 for 34.45.
>
>Looking at it MOD(ABS(A1),1) evaluated to .450000000000003, which I
>would take is caused by lack of precision when using MOD.

....

The motivation may have been sound. The implementation wasn't. It should be
as simple as

=LEN(x)-LEN(INT(x))-1

though that'd work with values stored and used but not displayed.

What should the result be for, say, =32+1/3?


  #7  
Old August 28th 05, 01:45 PM
KL
external usenet poster
 
Posts: n/a
Default

Opps! You're right Bob. In my testing I hadn't run into this issue and I
didn't suspect any precision issue with MOD - it definetely returns
..450000000000003, which I believe has to do with the floating-point
limitations ( http://support.microsoft.com/kb/78113/en-us )

Regards,
KL


"Bob Phillips" > wrote in message
...
> KL,
>
> This doesn't work for me at all, it returns 15 for 34.45.
>
> Looking at it MOD(ABS(A1),1) evaluated to .450000000000003, which I would
> take is caused by lack of precision when using MOD.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "KL" > wrote in message
> ...
>> ...just in case the file might be used in other language environments,
>> the
>> following formula avoids using the decimal separator explicitly:
>>
>> =IF(ISNUMBER(A1),LEN(MOD(ABS(A1),1))-1-(MOD(A1,1)>0),0)
>>
>> It also uses IF(ISNUMBER(A1),...,0) construct to check if there is text
>> in
>> the cell A1. If there can only be numbers or empty cells then a shorter
>> version can be used:
>>
>> =LEN(MOD(ABS(A1),1))-1-(MOD(A1,1)>0)
>>
>> Regards,
>> KL
>>
>>
>>
>> "Bob Phillips" > wrote in message
>> ...
>> > =IF(ISNUMBER(FIND(".",A20)),LEN(A20)-FIND(".",A20),0)
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "Warren Smith" > wrote in message
>> > ...
>> >> The worksheet I am doing needs to look at cell values, and in another
>> > return
>> >> the number of decimal places used in the cell.
>> >>
>> >> for example
>> >> if A1 contained a number of 34.45,
>> >> I want B1 to tell me 2.
>> >> I need to know this because I want to enter the data in a table
>> >> against
>> >> another set of figures which will have 1 more decimal place than the
>> >> first
>> >> set,
>> >> and I can't set the decimal places up before because I don't know how
>> >> many
>> >> there will be.
>> >>
>> >> Thanks in advance for any help
>> >> Warren
>> >>
>> >>
>> >
>> >

>>
>>

>
>



  #8  
Old August 28th 05, 01:52 PM
KL
external usenet poster
 
Posts: n/a
Default

Harlan,

Yours is clearly a much better (and neater) implementation of the idea. As
to =32+1/3, I guess it is going to be the same issue for all possible
solutions given the IEEE 754 specification, so as long as one is aware of
that, =LEN(x)-LEN(INT(x))-1 is probably the best option.

Thanks and regards,
KL


"Harlan Grove" > wrote in message
...
> "Bob Phillips" > wrote...
>>This doesn't work for me at all, it returns 15 for 34.45.
>>
>>Looking at it MOD(ABS(A1),1) evaluated to .450000000000003, which I
>>would take is caused by lack of precision when using MOD.

> ...
>
> The motivation may have been sound. The implementation wasn't. It should
> be as simple as
>
> =LEN(x)-LEN(INT(x))-1
>
> though that'd work with values stored and used but not displayed.
>
> What should the result be for, say, =32+1/3?
>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Numbers won't right-align LurfysMa New Users to Excel 5 August 25th 05 09:46 PM
Excel adds phantom decimal places: why? Dave O Excel Discussion (Misc queries) 1 August 16th 05 06:25 PM
I Need to Count Number of Entries Based on Two Criteria Jones Excel Worksheet Functions 3 July 14th 05 10:34 PM
Max decimal places SusieQ Excel Discussion (Misc queries) 1 July 13th 05 07:57 PM
How do I insert 2 decimal places to a number in cell? Rebecca Martinez Excel Worksheet Functions 6 July 7th 05 07:12 PM


All times are GMT +1. The time now is 07:14 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.