We will review how to convert millimeters to inches (mm to in), and then round to any imperial fraction (i.e. 1/64, 1/32, 1/16, 1/8, 1/4, 1/2). This tutorial will convert a list of millimeter measurements and convert them into inches and then round to a 1/16”.

Functions covered:

- TEXT - Formats a number and converts it to text - Read more here
- MROUND - Returns a number rounded to the desired multiple - Read more here

Let’s start with the list of measurements that we have for our parts. We have these all in millimeters, but we need to see these sizes in inches as a reference.

To convert millimeters to inches we must divide the mm dimension by 25.4.

=D2/25.4

This gives us a large number that is hard to read. And is not a decimal based off an imperial fraction.

We want to be able to view this rounded to the appropriate decimal value that corresponds to the imperial fraction. See chart below:

A | B | |
---|---|---|

1 | Decimal | Fraction |

2 | 0.0625 | 1/16 |

3 | 0.1250 | 1/8 |

4 | 0.1875 | 3/16 |

5 | 0.2500 | 1/4 |

6 | 0.3125 | 5/16 |

7 | 0.3750 | 3/8 |

8 | 0.4375 | 7/16 |

9 | 0.5000 | 1/2 |

10 | 0.5625 | 9/16 |

11 | 0.6250 | 5/8 |

12 | 0.6875 | 11/16 |

13 | 0.7500 | 3/4 |

14 | 0.8125 | 13/16 |

15 | 0.8750 | 7/8 |

16 | 0.9375 | 15/16 |

To round this dimension to a number based on an imperial fraction we can use MROUND.

=MROUND(D2/25.4,1/16)

As you can see this will take the dimension to the nearest 1/16 of an inch.

We can also view this as a fraction. The TEXT command will format the decimal as a fraction. =TEXT(1.4375,”# #/##”) This will return a value of 1 7/16. The two hash symbols will take the fraction to two numbers on the denominator, in this instance, rounding the decimal to a 16^{th} of an inch. A single hash will round the number to a single number in the denominator.

=TEXT(1.4375,”# #/#”)

Here the value is rounded to 1 4/9.

Though useful, TEXT should only be used to format text, not as a rounding function. Use one of the ROUND functions and then format with TEXT to view the data as needed.

## Comments (0)

This thread has been closed from taking new comments.