Hello,
I want to show (in a cell) how much decimals there are after a comma.
So, i hope you understand me...
Thanks!
Hello,
I want to show (in a cell) how much decimals there are after a comma.
So, i hope you understand me...
Thanks!
Re: Count after comma...
Oeste, welcome to Ozgrid.
Using this formula for a value in A1 you must format the cell to text or general and NOT number - A1 that is!
=IF(ISERROR(FIND(",",A1)),0,LEN(A1)-FIND(",",A1))
If the data is in number format then I don't know the answer
Re: Count after comma...
Thanks!
My cell was allready a "General"
Sorry for my bad English :d
Re: Count after comma...
This should work for numbers too
(I have used decimal point as opposed to European "comma" ok so replace if necessary)
sample data A1:A5
15.9845
17.256
15.11
15.2
15
fomula in B1 copied down
=IF(A1=INT(A1),0,LEN(MID(A1-INT(A1),FIND(".",A1,1),LEN(A1)-FIND(".",A1,1))))
result
4
3
2
1
0
Any good
Re: Count number of decimal places
Thanks!
This is better :d
But Now, I want to tally
I have a Number, 4 for instance.
Then I want in cell N7: llll (4xL)
When a number is 9
I want in cell N7: lllll llll
When the number is 15
I want in a N7: lllll lllll lllll
Thanks in advance!
You've helped me already a lot
Re: Count number of decimal places
OK
Let's say A1 is 15.9454
B1 is result of previous formula giving value of 4
In C1 type =REPT("l",B1)
result would be llll
This what you meant ?
Re: Count number of decimal places
Yes, this is what i want.
But now i want to group the "l" per 5.
Something like this:
lllll lllll lllll l
Thanks in advance!
Re: Count number of decimal places
Try this one:
=REPT("IIIII ",INT(B1/5))&REPT("I",MOD(B1,5))
HTH
TJ
Re: Count number of decimal places
Nice TJ
Re: Count number of decimal places
hello all (my first post here)
alternative way (works with number formatted cells and with all decimal separator) up to 5 decimal places of the input number. you can change the round to parameter for more.
=MAX(0;LEN(ROUND(A1-INT(A1);5))-2)
sample data:
4.13228
4.1389
4.476
4.14
3.78
0.58
-0.22
result:
5.00
4.00
3.00
2.00
2.00
2.00
2.00
sample:
4.55228
4.5589
4.896
4.56
4.2
1
0.2
result:
5.00
4.00
3.00
2.00
1.00
0.00
1.00
Re: Count number of decimal places
Hi everybody, is my first post
How could excel show or find in a singular cell, the maximum value of decimal places for a range of numbers?
Re: Count number of decimal places
Hello and welcome to Ozgrid.
Your question would be clearer if you provided examples of what you mean.
The MAX function returns the largest value.
The LEN function returns the length of a string - includes the decimal if the string is not a whole number.
Here is my guess:
Say the range A1:A5 contain decimal numbers
We could use:
[f]
{=MAX(LEN(A1:A5))-2}
[/f]
Note: the above is an array formula and must be committed using the key combination of Ctrl + Shift + Enter
To determine the number of characters to the right of the decimal.
Does that work for you?
Re: Count after comma...
This should work for numbers too
(I have used decimal point as opposed to European "comma" ok so replace if necessary)
sample data A1:A5
15.9845
17.256
15.11
15.2
15
fomula in B1 copied down
=IF(A1=INT(A1),0,LEN(MID(A1-INT(A1),FIND(".",A1,1),LEN(A1)-FIND(".",A1,1))))
result
4
3
2
1
0
Hi
I needed something similar to this formula above you posted for another user, however I need it to be able to count the trailing zeros. I am working in a machine shop
example : tolerance for
12. 0 = +/- .030
12.00 = +/- .015
12.000 = +/- .005
The current one was doing great until I introduced a trailing zero 12.01 was 2 places and 12.10 was 1 place need it to count 12.10 as 2. I am setting up nested if statements based on the number of places . I understand normal if statements, I'm just not getting the jest of your formula yet.
If you can show me a way to do it for now I would appreciate it . In the mean time Im going to look up len and mid in the help menu.
Thanks Bob
Re: Count number of decimal places
Welcome to Ozgrid, Bob.
Please don't post questions in threads started by other members. Start your own thread, give it a clear and concise title and explain your issue fully.
If you think this thread can help explain or clarify your issue, you can include a link by copying the URL from the address bar of your browser and pasting into your message.
This thread has been closed.