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. 


Thread Tools  Display Modes 
#1




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




=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




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




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




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




"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




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 floatingpoint limitations ( http://support.microsoft.com/kb/78113/enus ) 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




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  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Numbers won't rightalign  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 