Count number of decimal places

  • 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

    ________________________________________


    Good Luck!


    Ray :nana:

  • 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


    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


    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.