find minimum increment

Excel777

Well-known Member
Joined
Jul 3, 2009
Messages
886
hi i need a formula to solve this problem
0.005 and numbers like that results should be 23/100 = 0.001
0.09 and numbers like that results should be 23/100 = 0.01
1.230 and numbers like that results should be 23/100 = 0.01
1.10 and numbers like that results should be 1/10 = 0.1
123 and numbers like that results should be 1
1027 and numbers like that results should be 10
any help
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,008
Office Version
  1. 365
Platform
  1. Windows
Or maybe
+Fluff v2.xlsm
AB
10.0050.001
20.090.01
31.230.01
41.10.1
51231
6102710
Main
Cell Formulas
RangeFormula
B1:B6B1=10^-IFERROR(LEN(A1)-FIND(".",A1),-LEN(A1)+3)
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
51,008
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Sulprobil

Board Regular
Joined
May 12, 2020
Messages
139
Office Version
  1. 2016
Platform
  1. Windows
is this the formula it gives me different results
=10^(--RIGHT(TEXT(A1,"#,###############E+000"),4)+7-LEN(TEXT(A1,"#,###############E+000")))
123
result
0.1
Try "#.###...". XL2BB does not translate "," into "." within strings.
And what do you expect for an input of
10
100
1000
?
My formula will return the same (because the 0s are not significant).
 

Watch MrExcel Video

Forum statistics

Threads
1,119,026
Messages
5,575,652
Members
412,682
Latest member
salmanahmad84
Top