I am a woodworker who must work in Imperial measurements, including standard fractions. I understand how to format numbers to display in 32nds, but I also need the resulting fraction to display the LCD (least common denominator) version of the answer.

For example, by using `# ??/32`

for number formatting, then a value of `0.53125`

displays correctly as `17/32`

.

However, the value of `0.125`

displays as `4/32`

where I need it to display the LCD version of `1/8`

.

While using `# ??/??`

would display `.125`

as the desired `1/8`

, it would also display `0.3`

as `3/10`

which is unusable for me. I need `0.3`

to display as `5/16`

(`3/10`

= `9.6/32`

which rounds to `10/32`

whose LCD is `5/16`

)

So, to be clear, I can only use answers that are `# ??/2`

, `# ??/4`

, `# ??/8`

, `# ??/16`

, or `# ??/32`

--whichever is the LCD version of the fraction.

I thought about rounding answers to the nearest 1/32 with `=ROUND(_____*32,0)/32`

, then using the typical `# ??/??`

formatting so that the answer would be given in the LCD version–but this can cause too much “drift” in the sums of measurements when many rounded answers are totaled together.

For example, 50 measurements of 0.3 inches should equal 15 inches (`50*0.3 = 15`

), but if I use the rounding method to display my answers in 32nds, it totals 15-5/8 inches (`50*ROUND(3/10*32,0)/32 = 15.625`

). Thus, I just want the *displayed* value to be rounded to the nearest 32nd–I don’t want to round the actual value of the cell.

For my Metric friends, here is a standard woodworking ruler-which leads to my quandary…