# Lowest decimal place value from a range of data

#### Juggler_IN

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

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Fluff

##### MrExcel MVP, Moderator
Change MAX to MIN?

#### Peter_SSs

##### MrExcel MVP, Moderator
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

#### Juggler_IN

##### Active Member
@Peter,

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

#### Juggler_IN

##### Active Member
@Fluff,

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

#### Fluff

##### MrExcel MVP, Moderator
=MAX(LEN(SUBSTITUTE(SUBSTITUTE(\$A\$1:\$A\$9,TRUNC(\$A\$1:\$A\$9)&"","",1),".","")))
& the same for Min

#### Peter_SSs

##### MrExcel MVP, Moderator
@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
ABC
110-1
210
310
410
510
610
710
810
910
Decimal

@Fluff,

Try these

Excel Workbook
ABC
12.365
23.330
35.6325
42.35698
56.65984
610
70.3265
80.33
90.3265
Decimal

#### Juggler_IN

##### Active Member
@Peter,

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

Replies
1
Views
165
Replies
7
Views
102
Replies
4
Views
108
Replies
6
Views
150
Replies
15
Views
487

1,187,001
Messages
5,961,055
Members
438,516
Latest member
Fintrics

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back