Home |
Search |
Today's Posts |
#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 |
#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 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
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Numbers won't right-align | New Users to Excel | |||
Excel adds phantom decimal places: why? | Excel Discussion (Misc queries) | |||
I Need to Count Number of Entries Based on Two Criteria | Excel Worksheet Functions | |||
Max decimal places | Excel Discussion (Misc queries) | |||
How do I insert 2 decimal places to a number in cell? | Excel Worksheet Functions |