Find Smallest Decimal Place in Range

[Pistoleiro]

Hi All,

I've got a problem that I think may be solvable with an array function, however I'm struggling to think of how to put it together and thought y'all would be able to help. My general task is that I'm trying to determine the data type (string, integer, double, etc..) of certain fields in a large file however I'm having trouble determining the amount of precision required, specifically for doubles. In order to do this I need a formula that finds the smallest decimal place used in the range. I can do this in VBA however I was wondering if someone knows of a way using formulas in excel. Example of what I require below:

[TABLE="class: grid, width: 83"]
<tbody>[TR]
[TD="align: right"]0.0001
[/TD]
[/TR]
[TR]
[TD="align: right"]10
[/TD]
[/TR]
[TR]
[TD="align: right"]515
[/TD]
[/TR]
[TR]
[TD="align: right"]15.51588
[/TD]
[/TR]
[TR]
[TD="align: right"]0.181
[/TD]
[/TR]
[TR]
[TD="align: right"]158.156
[/TD]
[/TR]
</tbody>[/TABLE]


The formula would have to look through the above range and determine that row 4 has the most of decimal places as it goes to 100,000'ths so the formula would return 5.
I've got a lot of data to look through so I am looking for something that can work the entire range rather than row by row.

Thanks,

Nick

 

[Andrew Poulsom]

You would think this would work:

=MAX(INDEX(LEN(MOD(A1:A6,1)),))

but there is a floating point issue with the fourth item.

 

[Pistoleiro]

Hi Andrew,

Thanks for the response, never realised mod and len could be used in such a way. Shame mod turns 15.51588 to .5158799999999999999. Is there any way to get round this?

 

[billszysz]

Try this one (MOD is better but has a problem with fioating point).

=MAX(LEN(SUBSTITUTE(A1:A6,TRUNC(A1:A6)&"","",1))-1)

use CTRL+SHIFT+ENTER

 

[Andrew Poulsom]

Hi Andrew,

Thanks for the response, never realised mod and len could be used in such a way. Shame mod turns 15.51588 to .5158799999999999999. Is there any way to get round this?

Only by rounding.

 

[Pistoleiro]

Thanks Bill, Figures you'd have to get excel to treat a number as text in order to find decimal places haha.

Appreciate your help with this saved me from a great deal of trawling through data.

 

[billszysz]

You are welcome