Display Rounded Imperial Feet with Fractional Inches and Approximation Indicator

I think others will find this helpful.

I wrote these formulas to convert imperial inches (with decimal) to a nice display of feet with fractional inches (rounded to 8ths, 16ths or whatever).
You can also optionally add an approximation indicator if the display calculates to a rounded approximation rather than the true measurement display.
I chose to use the single-tilde when the display is slightly less than the source number and the double-tilde for displays that are greater than the source number.

The formulas assume that A1 contains the denominator of the fractional inches you want to round to; e.g. 16 would be rounding to the 1/16th inch.
It also assumes that B1 contains the original source value (in inches with a decimal) to convert and display, B1 can be any positive or negative number (with decimal value for the fractional inches).

Format 1a: does not display zero feet or zero inches. e.g. 0", or 1/2", or 2', or 5' 3/8"

Please Login or Register  to view this content.

Format 1b: add the tilde/double-tilde approximation to 1a.

Please Login or Register  to view this content.

Format 2a: Displays zero feet, but does not display a zero between feet and inches. e.g. 0'-0", or 0' 1/2", or 2'-0", or 5' 3/8"

Please Login or Register  to view this content.

Format 2b: adds the tilde/double-tilde approximation to 2a.

Please Login or Register  to view this content.

Format 3a: Display zero feet and also zero inches. e.g. 0'-0", or 0'-0 1/2", or 2'-0", or 5'-0 3/8"

Please Login or Register  to view this content.

Format 3b: adds the tilde/double-tilde approximation to 3a.

Please Login or Register  to view this content.

Notes:

This formula is a key building block to the more complex formulas above: MROUND(B1,1/$A$1*SIGN(B1))

MROUND will round the value of B1 to the nearest 8th/16th or whatever you have entered into cell A1.
Notice the multiplication of the SIGN(B1), this is required for MROUND to work with negative numbers.

Cell B1 can also be hardcoded into the formulas if you don't need to change the rounding in the future. E.g. 8th or 16th of an inch is pretty typical.