Lowest decimal place value from a range of data

[Juggler_IN]

I have this array formula which calculates the highest decimal place in a range.

{=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?

 

[Fluff]

Change MAX to MIN?

 

[Peter_SSs]

I have this array formula which calculates the highest decimal place in a range.

{=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

{=MAX(LEN(A1:A9)-FIND(".",A1:A9))}

.. and with MIN

 

[Juggler_IN]

@Peter,

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

 

[Juggler_IN]

@Fluff,

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

 

[Fluff]

How about
=MAX(LEN(SUBSTITUTE(SUBSTITUTE($A$1:$A$9,TRUNC($A$1:$A$9)&"","",1),".","")))
& the same for Min

 

[Peter_SSs]

@Peter,

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

I know that.

Assuming all the cells do contain decimal places, ...

.. but then neither does yours in some circumstances.

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

 

[Juggler_IN]

@Fluff,

Your formulae work!

 

[Peter_SSs]

Try these

Excel Workbook
	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
Decimal

 

[Juggler_IN]

@Peter,

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