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

1. ## 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?  Reply With Quote

2. ## Re: Lowest decimal place value from a range of data

Change MAX to MIN?  Reply With Quote

3. ## Re: Lowest decimal place value from a range of data Originally Posted by Juggler_IN 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  Reply With Quote

4. ## Re: Lowest decimal place value from a range of data

@Peter,

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

5. ## Re: Lowest decimal place value from a range of data

@Fluff,

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

6. ## Re: Lowest decimal place value from a range of data

=MAX(LEN(SUBSTITUTE(SUBSTITUTE(\$A\$1:\$A\$9,TRUNC(\$A\$1:\$A\$9)&"","",1),".","")))
& the same for Min  Reply With Quote

7. ## Re: Lowest decimal place value from a range of data Originally Posted by Juggler_IN @Peter,

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

 A B C 1 10 -1 2 10 3 10 4 10 5 10 6 10 7 10 8 10 9 10

 Cell Formula 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  Reply With Quote

8. ## Re: Lowest decimal place value from a range of data

@Fluff,  Reply With Quote

9. ## Re: Lowest decimal place value from a range of data

Try these

Decimal

 A B C 1 2.36 5 2 3.33 0 3 5.6325 4 2.35698 5 6.65984 6 10 7 0.3265 8 0.33 9 0.3265

 Cell Formula 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  Reply With Quote

10. ## Re: Lowest decimal place value from a range of data

@Peter,

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

## User Tag List

#### Tags for this Thread

decimal, lowest, modified, place, range #### Posting Permissions

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