Excel format numbers with only the specificity needed: 1/2, 3/8, 7/16, etc

Question

I have a column of numbers in excel that I am using to help me with a carpentry project. Since I'm in the good 'ol USA I expect measurements like this:

• 4 5/8
• 4 1/2
• 4 3/4

I'm able to get the numbers to display as fractions, but I want them to be only as precise as the fraction requires. I don't need common denominators; in fact they make the reading of the file more cumbersome. Here's what I'm getting:

• 4 5/8
• 4 4/8
• 4 6/8

How can I get all the cells in that column to be formatted the most simply (e.g. with 1/2 instead of 4/8)? Thanks in advance for your help.

Answer 1

Round to 1/8s your result then apply fraction format "up to one digit":

=ROUND(<formula>*8,0)/8

Where <formula> is your original formula.

I don't know how you calculate in carpentry, you may want to use ROUNDUP to avoid calculating less then necessary.

Also, you may want to replace 8 to 16, 32 in the formula and display up to two digits.

Answer 2

I have numbers that are not integers, and I want to round the fractional part to the nearest multiple of ⅛ and then reduce it to lowest terms.

If

• The above is an accurate description of your requirement,
• You’re willing to have one column to contain the actual number and another column to display the display value, and
• You’re willing to dedicate a few cells elsewhere in the workbook to a lookup table,

then this answer should work for you.

Create an array mapping all possible fractions from their numeric forms to their textual (display) forms:

        
      Note that the non-blank text values (R2, R3, ...) all begin with a space.

Then, if cell C1 contains a number, such as 4.62, set D1 to

=INT(C1+Q$2/2) & VLOOKUP(MOD(C1+Q$2/2,1), Q$1:R$8, 2)

Q2 contains the smallest representable fraction; in the above example, it is ⅛ (0.125).  So we want to add half of that to the C1 value to allow for rounding; we want anything between 4.94 and 5.06 to be displayed as “5”.  Then MOD(C1+Q$2/2,1) extracts the fractional part of the augmented number, and the VLOOKUP finds the textual form that you want to display.

This approach has the feature that you aren’t restricted to a single sequence of fractions e.g., the multiples of 1/8); if you also want, say, multiples of 1/5 or 1/3, you can add them to the table.