Format integers as fraction

Question

I have a list of fractions in Excel which I want to format as fractions, including integers. However, by default Excel formats integers as integers which is understandable.

Is there any way to force Excel to format, say, 4/4 as 4/4 instead of 1?

I need it to be stores as values and not as text, so '4/4 wont work. As I need to average a bunch of values from it afterwards.

Apparently I'm the first person ever to take issue with this, because google provides absolutely no help whatsoever :o

Answer 1

I don't believe what you are trying to accomplish is doable outright, as fractions are really division problems. However, with some formula trickery, you may be able to get something that will work for you.

If you place '7/8 in cell A1 and then use the following formula in cell B1

=DECIMAL(MID(A1,1,FIND("/",A1,1)-1),10)/DECIMAL(MID(A1,FIND("/",A1,1)+1,LEN(A1)),10)

The cell will display the decimal value of the "fraction", in this case 0.875 allowing you to change the denominator at will and still perform math functions.

This works because the formula slices up the "fraction" stored as text and converts it to a number and performs the math.

Answer 2

Yes, use a custom number format:

?/4