Display vulgar fractions with a colon for aspect ratios

Question

How can I display floating point numbers in cells as aspect ratios with a colon : or ratio symbol ∶ U+2236 instead of a slash / in spreadsheet applications?
One can format numeric cell contents to display as a vulgar fraction with a slash between numerator and denominator. My copy of MS Excel has these predefined by default:

# ?/? single-digt
# ??/?? double-digit
# ???/??? triple-digit
# ?/2 halves
# ?/4 fourths or quarters
# ?/8 eights
# ?/16 sixteenths
# ?/10 tenths
# ?/100 hundredths

They all include an optional initial integer #. This can be suppressed, of course, e.g. by using ?/?,  to force values above 1 to have a numerator larger than the denominator.
One can also introduce different fixed denominators, e.g. ?/9 (as is popular in image, video and screen aspect ratios). This does not support floats for numerators as in “19.5∶9” (or even “19½∶9”), because Excel does not allow formatting codes like ?,0/? to be used.
However, I cannot find a way to alter the character used for the fraction slash /.

sancho.s ReinstateMonicaCellio · Answer

There is no way to have a cell containing a number, displayed as you mean to do.
You would have to settle for having text in that cell, referring to another cell holding the corresponding number.
The formula linking the two cells is
=TRIM(SUBSTITUTE(TEXT(D1,"?/????"),"/",":"))

where D1 holds the numeric value. This supports fractions with the denominator up to four digits, and with no integer part.
It seems you can extend this maximum number of digits, but I guess this is good enough.
The image shows in column F how it works.
The last two rows show how the formula discerns (1+4/999) from (1+4/1001).

Gary's Student · Answer

For a value like .125, pick a cell and enter:
 =SUBSTITUTE(TEXT(0.125,"# ?/8 "),"/",":")

NOTE:
The formula can be modified to reference a separate cell like:
=SUBSTITUTE(TEXT(A1,"# ?/8 "),"/",":")

This is for display purposes only.  The value given by the formula is Text rather than Numeric.

Display vulgar fractions with a colon for aspect ratios

2 Answers

Add your own answers!

Ask a Question