My apologies if this has been covered already.

I have a cabinetshop that builds things using metric measurements.

Every now and then we have to order glass for the cabinet doors but our glass vendor uses inches and fractions.

I would like to have a formula that divides millimeters (25.4mm per inch) into integers & fractions.

To be understandable to the glass guys, the fractions need to end in common units you could find on a tape rule.

Typical units of measurement would be sixteenths, eighths, quarter & half.

We don't need to get any closer than 1/16 inch.

Our metric starting point is millimeters. We don't use centimeters.

For example, we would call 4.6 centimeters as 460 millimeters.

Can anybody help me with this conversion?

Thanks.

Jarvis

Hi Jarvis. I would try converting the millimeter measure to 16ths of an inch rather than full inches. That is, rather than converting 460 millimeters to 18.11 inches, using the conversion factor you gave above, convert 460 millimeteres to 289.76 16ths of an inch, using the conversion factor (1.5875 millimeters per 16th inch). This allows you to remove the decimal portion of the number for easy conversion, and then you can just separate the 16ths out and make a standard-looking number. In short,

Int ( ( Int ( millimeters / 1.5875 ) ) / 16 ) & " " & Mod ( Int ( millimeters / 1.5875 ) , 16 ) & "/16"

If you enter 460 into millimeters, this field displays "18 1/16".

Does this help?

Jerry

Jerry,

I know I am going to have to stare at that calculation for a while but

I'm sure this is the path to take.

Thanks a lot!

Jarvis

My pleasure. Breaking it down:

Int ( ( Int ( millimeters / 1.5875 ) ) / 16 ) & " " & Mod ( Int ( millimeters / 1.5875 ) , 16 ) & "/16"

Int ( millimeters / 1.5875 )

Thanks alot Jerry!

Jarvis

Jerry,

I thought I would test your forbearance.

(Now that I can smell success I want more.)

Is there a way to have this resultant number smart enough to switch denominators as necessary?

What I would like the number result in is the lowest denominator possible.

For example:

2/16 default to 1/8

4/16 default to 1/4

6/16 default to 3/8

8/16 default to 1/2

10/16 default to 5/8

12/16 default to 3/4

14/16 default to 7/8

Is there a way to produce a result like these in a case statement?

Thanks again for all your help.

Jarvis

Here's a "dumb" way with Choose(), I'm sure something more clever could be done with fancier math:

Int( ( Int( mm / 1.5875 ) ) / 16 ) & " "

& Choose( Mod( Int( mm / 1.5875 ) , 16 ),

    "", "1/16", "1/8", "3/16", "1/4", "5/16", "3/8", "7/16", "1/2",

    "9/16", "5/8", "11/16", "3/4", "13/16", "7/8", "15/16" )

WOW! Much better than what i would have proposed, and the best use of the Choose() function i have seen. Nice, Shadow.

J

Jerry & Shadow,

I can't thank you guys enough!

I live in a world of millimeters and I'm surrounded by a universe of inches.

This is going to make a LOT of things easier.

Jarvis