1

Using Excel for Mac. I want the formula to round decimal numbers to fractions. The smallest increment I would want is 1/16 (which I can easily do) but I want automatically to reduce sixteenths to 1/8, 1/4, and 1/2 when appropriate (not rounding here).

  1. Only rounding to the nearest sixteenth. (I have all the fractions rounding to the nearest 1/16)
  2. But THEN reduce the fractions, eg:

14/16 - this should become 7/8
12/16 - this should become 3/4
8/16 - this should become 1/2

So in the end, I will have a large Excel sheet of fractions, some as small as 1/16, and some larger.

How would I do this?

pnuts
  • 58,317
  • 11
  • 87
  • 139
AgileMan
  • 35
  • 1
  • 1
  • 9
  • It's excel 2011. I can set it to format to two digits, but then it will remain as two digits. I want to convert 1/16 digits to 1/8, 1/4, and 1/2 when applicable. So far I can only see how to set 1 convention for a set of values ... but not multiple. – AgileMan Feb 07 '14 at 18:25

3 Answers3

0

I gather you do have some functionality for formatting fractions. Excel 2007 for example has:

SO21634379 example

I was contemplating 'doing the math' to achieve numbers (decimals) in multiples of 0.0625 and then applying formatting just for appearances.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    Thanks ... originally this didn't work because I forgot to transform the numbers first by rounding them (mround) to a number divisible by 0.0625. Once I did that than this option worked. – AgileMan Feb 07 '14 at 19:42
0

This will work for values between 0 and 1 . Select the cells and run this small macro:

Sub FixFormat()
Dim r As Range, v As Variant, s As String
Dim numerator As Long
Selection.NumberFormat = "General"
For Each r In Selection
    v = r.Value
    If v >= 0 And v <= 1 Then
        r.NumberFormat = "??/16"
        numerator = CLng(Split(r.Text, "/")(0))
        Select Case numerator
            Case 0, 1, 3, 5, 7, 9, 11, 13, 15
            Case 2, 6, 10, 14
                r.NumberFormat = "# ?/8"
            Case 4, 12
                r.NumberFormat = "?/4"
            Case 8
                r.NumberFormat = "?/2"
            End Select
    End If
Next r
End Sub

The macro will detect the value and apply the proper formatting.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
0

Rounding to 1/16ths

ROUND(YourNumber*16,0)/16

Then set the cell to two digit fractions. They will automatically reduce to 1/2, 1/4, 1/8, 1/16. Rounding drops off the "extra" portion. This could be used with round up and round down as well.

Kraig
  • 1