Search:

# 2003 - Rounding

Asked By: Molly    Date: Mar 25    Category: MS Office    Views: 2306

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 reason
for mentioning that particular value... And convert it to inches.

Now 3.572mm = 9/64ths Inches... And that can be displayed using
Format/Fractions.

What I want to do is... If I type in 10 for mm, which is 0.0.393700787401575
Inches... Which according to Excel is 37/94 ths, Round that to the nearest
number of 64ths because that's what's on all the rulers!

Any ideas guys?

Share:

### 6 Answers Found

Answer #1    Answered By: Milton Robinson     Answered On: Mar 25

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, numer
If (Target.Value <> "") Then
inch = Target.Value / 25.4
denom = 64
numer = Int(inch * denom)
If (numer Mod 2 = 0) Then
denom = 32
numer = Int(inch * denom)
If (numer Mod 2 = 0) Then
denom = 16
numer = Int(inch * denom)
If (numer Mod 2 = 0) Then
denom = 8
numer = Int(inch * denom)
If (numer Mod 2 = 0) Then
denom = 4
numer = Int(inch * denom)
If (numer Mod 2 = 0) Then
denom = 2
numer = Int(inch * denom)
If (numer Mod 2 = 0) Then
denom = 1
numer = Int(inch * denom)
End If
End If
End If
End If
End If
End If
End If
mm2in = numer & "/" & denom
End Function

then 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.

Answer #2    Answered By: Vinit Online     Answered On: Mar 25

Out of interest... Anyone have a none vba solution?

Answer #3    Answered By: Jake Williams     Answered On: Mar 25

I think that this'll work

For mm to the nearest 64th, disregarding whole inches (ie, answer
always <=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)

Answer #4    Answered By: Muriel Dunn     Answered On: Mar 25

Did you write that "ad hoc" or do you use it yourself for anything?

Answer #5    Answered By: Trae Thompson     Answered On: Mar 25

Na... I just whipped it up when you asked.
I've got jobs running, and I get bored!

Answer #6    Answered By: Rochelle Elliott     Answered On: Mar 25

I really appreciate it.

I think you must have a maths background... You will be called upon again
I'm sure!!!

Didn't find what you were looking for? Find more on 2003 - Rounding Or get search suggestion and latest updates.