# 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?

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

Change MAX to MIN?

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

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.

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

@Fluff,

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

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

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

@Fluff,

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

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.