Lowest decimal place value from a range of data
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Lowest decimal place value from a range of data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2014
    Location
    Mumbai, Maharashtra, INDIA
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Lowest decimal place value from a range of data

    I have this array formula which calculates the highest decimal place in a range.
    Code:
    {=MAX(LEN(SUBSTITUTE($A$1:$A$9,TRUNC($A$1:$A$9)&"","",1))-1)}
    How can this be modified to return the lowest decimal place in a range?

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,989
    Post Thanks / Like
    Mentioned
    460 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Lowest decimal place value from a range of data

    Change MAX to MIN?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,465
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Lowest decimal place value from a range of data

    Quote Originally Posted by Juggler_IN View Post
    I have this array formula which calculates the highest decimal place in a range.
    Code:
    {=MAX(LEN(SUBSTITUTE($A$1:$A$9,TRUNC($A$1:$A$9)&"","",1))-1)}
    How can this be modified to return the lowest decimal place in a range?
    Assuming all the cells do contain decimal places, you could also try
    Code:
    {=MAX(LEN(A1:A9)-FIND(".",A1:A9))}
    .. and with MIN
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    Board Regular
    Join Date
    Nov 2014
    Location
    Mumbai, Maharashtra, INDIA
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lowest decimal place value from a range of data

    @Peter,

    The formula does not work if a value doesn't have a decimal.

  5. #5
    Board Regular
    Join Date
    Nov 2014
    Location
    Mumbai, Maharashtra, INDIA
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lowest decimal place value from a range of data

    @Fluff,

    Change MAX to MIN doesn't work. I tried that.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,989
    Post Thanks / Like
    Mentioned
    460 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Lowest decimal place value from a range of data

    How about
    =MAX(LEN(SUBSTITUTE(SUBSTITUTE($A$1:$A$9,TRUNC($A$1:$A$9)&"","",1),".","")))
    & the same for Min
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,465
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Lowest decimal place value from a range of data

    Quote Originally Posted by Juggler_IN View Post
    @Peter,

    The formula does not work if a value doesn't have a decimal.
    I know that.
    Quote Originally Posted by Peter_SSs View Post
    Assuming all the cells do contain decimal places, ...
    .. but then neither does yours in some circumstances.

    Decimal

    ABC
    110 -1
    210
    310
    410
    510
    610
    710
    810
    910

    Spreadsheet Formulas
    CellFormula
    C1{=MAX(LEN(SUBSTITUTE($A$1:$A$9,TRUNC($A$1:$A$9)&"","",1))-1)}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    Board Regular
    Join Date
    Nov 2014
    Location
    Mumbai, Maharashtra, INDIA
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lowest decimal place value from a range of data

    @Fluff,

    Your formulae work!

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,465
    Post Thanks / Like
    Mentioned
    86 Post(s)
    Tagged
    19 Thread(s)

    Default Re: Lowest decimal place value from a range of data

    Try these

    Decimal

    ABC
    12.36 5
    23.33 0
    35.6325
    42.35698
    56.65984
    610
    70.3265
    80.33
    90.3265

    Spreadsheet Formulas
    CellFormula
    C1{=MAX(MAX(LEN(A1:A9)-FIND(".",A1:A9&".")),0)}
    C2{=MAX(MIN(LEN(A1:A9)-FIND(".",A1:A9&".")),0)}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    Board Regular
    Join Date
    Nov 2014
    Location
    Mumbai, Maharashtra, INDIA
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lowest decimal place value from a range of data

    @Peter,

    Ofcourse! ... I didn't realize that I was using it within a formula.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •