Format integers as fraction-CodePudding

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

CodePudding user response：

Yes, use a custom number format:

?/4

CodePudding user response：

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

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