I hope this is a formula problem... :-)I'm building a sheet for converting mm to inches and the reverse.I want to type a number of mm... Say.. 3.572 ... And there's a good reasonfor mentioning that particular value... And convert it to inches.Now 3.572mm = 9/64ths Inches... And that can be displayed usingFormat/Fractions.What I want to do is... If I type in 10 for mm, which is 0.0.393700787401575Inches... Which according to Excel is 37/94 ths, Round that to the nearestnumber of 64ths because that's what's on all the rulers!Any ideas guys?
This isn't elegant, and I KNOW i can do it with a loop.but I figured it wouldn't be to cumbersome this way.I wrote this function:Function mm2in(Target As Range)Dim mm, inch, denom, numerIf (Target.Value <> "") Theninch = Target.Value / 25.4denom = 64numer = Int(inch * denom)If (numer Mod 2 = 0) Thendenom = 32numer = Int(inch * denom)If (numer Mod 2 = 0) Thendenom = 16numer = Int(inch * denom)If (numer Mod 2 = 0) Thendenom = 8numer = Int(inch * denom)If (numer Mod 2 = 0) Thendenom = 4numer = Int(inch * denom)If (numer Mod 2 = 0) Thendenom = 2numer = Int(inch * denom)If (numer Mod 2 = 0) Thendenom = 1numer = Int(inch * denom)End IfEnd IfEnd IfEnd IfEnd IfEnd IfEnd Ifmm2in = numer & "/" & denomEnd Functionthen put in cell B2:=mm2in(A2)in A2, i put the metric number I want converted.the INT function truncates the inch value.You may wish to round it instead.
Out of interest... Anyone have a none vba solution?
I think that this'll workFor mm to the nearest 64th, disregarding whole inches (ie, answeralways <=64)=ROUND((A1/25.45-INT(A1/25.45))*64,0)..to include entire length (ie, no limit)=ROUND((MOD(A1/25.45,64))*64,0)for in to mm=ROUND(A1*25.45,0)
Did you write that "ad hoc" or do you use it yourself for anything?
Na... I just whipped it up when you asked.I've got jobs running, and I get bored!
I really appreciate it.I think you must have a maths background... You will be called upon againI'm sure!!!