FRACTIONS

HANDLING FRACTIONS IN COMPUTER SYSTEMS ... THE PROBLEM AND A SOLUTION

Ever since the 1980s, I have wished that computer systems would interpret fractions properly. Whereas a human will see a symbol like "½" (half) as a number and equivalent to 0.5, to a computer it is similar to a letter or punctuation mark, and it isn't possible to do arithmetic or any other processing with them. Trying to do so would give an error message: "No such variable", "Syntax error", or "Mistake".

In Microsoft Excel®, there is a system for handling fractions, but they appear as 1/2 instead of ½, and so 5¼ for example would appear as 5 1/4, which just looks ugly. If you type "5¼" into Excel, it would be treated as text, and so any calculation based on it would give an error.

I don't have a full solution to this, but I have produced a formula for Excel that will take a value in a (hidden) column and display it properly as a fraction if there is one available in the character set. So for example, if cell A1 contains 5.75 or 5 3/4, cell B1 will display it as 5¾, which looks much nicer (subject to font size and character availability). Column A can then be hidden. The only downside is that any calculations will still need to be done using the value in column A - the format "5¾" is for display purposes only.

My Excel formula is below - please feel free to use it by copying and pasting. You will just need to replace "A1" with whichever cell the input number is in. The formula works with halves, thirds, quarters, sixths and eighths:

=IF(OR(A1="",A1=0),A1,IF(ABS(A1*8-ROUND(A1*8,0))>0.05,IF(OR(A1="",A1=0),A1,IF(ABS(A1*6-ROUND(A1*6,0))>0.05,A1,CONCATENATE(IF(SIGN(A1)=-1,"-",""),IF(ABS(A1)<1,"",ABS(ROUNDDOWN(A1,0))),MID(" ⅙⅓½⅔⅚",ROUND(ABS(A1-ROUNDDOWN(A1,0))*6,0)+1,1)))),CONCATENATE(IF(SIGN(A1)=-1,"-",""),IF(ABS(A1)<1,"",ABS(ROUNDDOWN(A1,0))),MID(" ⅛¼⅜½⅝¾⅞",ROUND(ABS(A1-ROUNDDOWN(A1,0))*8+1,0),1))))

For best results, you might wish to use central justification using the button, as was used for the Excel image shown on the left.

There are also less complicated versions available:

=IF(OR(A1="",A1=0),A1,IF(ABS(A1*4-ROUND(A1*4,0))>0.05,A1,CONCATENATE(IF(SIGN(A1)=-1,"-",""),IF(ABS(A1)<1,"",ABS(ROUNDDOWN(A1,0))),MID(" ¼½¾",ROUND(ABS(A1-ROUNDDOWN(A1,0))*4+1,0),1))))
(halves, quarters - the option to use if only 8-bit ASCII is available)

=IF(OR(A1="",A1=0),A1,IF(ABS(A1*8-ROUND(A1*8,0))>0.05,A1,CONCATENATE(IF(SIGN(A1)=-1,"-",""),IF(ABS(A1)<1,"",ABS(ROUNDDOWN(A1,0))),MID(" ⅛¼⅜½⅝¾⅞",ROUND(ABS(A1-ROUNDDOWN(A1,0))*8+1,0),1))))
(halves, quarters, eighths)

=IF(OR(A1="",A1=0),A1,IF(ABS(A1*6-ROUND(A1*6,0))>0.05,A1,CONCATENATE(IF(SIGN(A1)=-1,"-",""),IF(ABS(A1)<1,"",ABS(ROUNDDOWN(A1,0))),MID(" ⅙⅓½⅔⅚",ROUND(ABS(A1-ROUNDDOWN(A1,0))*6,0)+1,1))))
(halves, thirds, sixths)

=IF(OR(A1="",A1=0),A1,IF(ABS(A1*5-ROUND(A1*5,0))>0.05,A1,CONCATENATE(IF(SIGN(A1)=-1,"-",""),IF(ABS(A1)<1,"",ABS(ROUNDDOWN(A1,0))),MID(" ⅕⅖⅗⅘",ROUND(ABS(A1-ROUNDDOWN(A1,0))*5+1,0),1))))
(fifths, in case anyone wants them)

Whole numbers and fractional parts that don't match anything in the formula (e.g. 0.85) are displayed as is.

I have put my formula to use in my "Clitheroe Weather" spreadsheet in the "Weather" page. In this spreadsheet, most values to the nearest half are shown as "½" instead of "1/2" which I think is a good improvement ... what about you?

© Matthew Eagles 2018