Have you faced a situation in Excel where you want it to automatically show integers as integers and fractions as proper fractions (e.g. 8/3) and not mixed fractions (e.g. 2 2/3) or decimals (e.g. 2.6667)?
Well, I faced that situation today. Excel does not have a direct solution to this.
Yes, you can go to ‘Format Cells’ -> ‘Number’ and choose the format you want.
I will take two numbers to show an example of my problem: 8 and 8/3. I want them to be shown just as I typed them here.
Problem if I use default Fractional format (“# ???/???”): I see those numbers as 8 and 2 2/3.
Problem if I use a custom Fractional format (“???/???”): I see those numbers as 8/1 and 8/3.
Solution:
=IF(MOD(A1,A2)=0,TEXT(A1/A2,”#”),TEXT(A1/A2,”???/???”))
NOTE: A2 could be 1.
Here I have used A1 and A2 as example cells. For my application, I needed the formula,
=IF(MOD(B$4,(2*$A5))=0,TEXT(B$4/(2*$A5),”#”),TEXT(B$4/(2*$A5),”???/???”))
