Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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?

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.

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

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)

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 again

I'm sure!!!

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

Related Topics:

- Difference between vba.round and application.round
- Is there a difference between vba.round and application.round
- adds =round(cell references,5) to allocate the calculation to 5 places
- Strange rounding behavior
- Percentage Rounding VBA
- Rounding on the tens
- 2003 - Moving to a formula address
- 2003 - Setfocus on userform
- Excel 2003 - Looking at jpgs on the web
- Select Duplex Printing-Excel 2003
- Convert code from excel 2000 vba to excel 2003 vba
- Excel 2003 compatibility pack
- Forcing Excel 2003 to open file when Excel 2007 also installed
- 2003 - Trigonometry puzzle
- jswdp in windows server 2003
- Access 7 to Access 2003 using Excel VBA
- VBE Missing References in Excel 2003
- XL 2003 - Precision printing
- 2003 - TEXT and VALUE functions
- 2003 - drawing shapes
- 2003 - ActiveSheet.Calculate Problem
- 2003 - Userform combobox behaviour
- Conditional Formatting in Access 2003
- 2003 - Problem with dates
- 2003 Match() Problem