# Lowest decimal place value from a range of data

#### Juggler_IN

##### Board Regular
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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

##### Board Regular
@Peter,

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

#### Juggler_IN

##### Board Regular
@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

##### Board Regular
@Peter,

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

1,102,679
Messages
5,488,226
Members
407,632
Latest member
varunwalla

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...