Google Sheets uses artificial intelligence to interpret your actions and support you to the best of its ability. But sometimes, that is not what you want.
One such example is how Sheets treat fractions. When you enter fractions in a cell, it changes them into dates. When you enter mixed fractions, it is interpreted as normal text instead rendering it useless if you want to use the data in mathematical calculations.
On the bright side, there are simple ways to get rid of this problem. We will take a look at a few ways to convert your data into the form you want.
Using Custom Number Formatting
With the Custom Number Formatting you can change how the data is shown to the user without actually changing the value stored in memory. For example, the decimal 0.13 would appear ⅛ in the cells while the value stays 0.13 in memory.
Keep this in mind when using dynamic calculation functions as it will take into account the decimal and not the fraction.
To use the feature, first select all the cells with decimals that you want to convert and click on the Formal menu.
From the options that appear, go to Number>More Formats>Custom number format.
In the dialog box that appears enter the format: # ?/? and click Apply.The decimal numbers in the cells should appear as fractions now.
Custom Number Formatting Symbols
You might be wondering what the # ?/? symbol meant. These symbols are used as syntax to format the decimals. The meanings of the symbols are as follows:
- #- it represents a digit in the fraction, does not show the insignificant zero, if any are present.
- A blank space is used to indicate the whole number part in a mixed fraction.
- /- used to differentiate between the numerator and denominator of the fraction.
- ?- it also represents a digit in the fraction and also does not show any insignificant zeros but unlike #, ? leaves a blank space in place of the zero.
You can mix and match these symbols to represent fractions however you want.
Using TEXT Function
TEXT Formula can also be used to convert decimals to fractions by specifying the format in which you want them shown. The conversion works in a similar way as mentioned above in the custom number formatting method except the result of the function is considered a text value and not numbers anymore.
The formula that will give the fraction(as text) is: =TEXT(A1,”# ?/?”), where the first argument(A2) is the reference address of the cell with the decimal to convert to fraction(in case of multiple cells you may also enter an address range), and the second argument separated with a comma(,) is the format in which you want the fraction to appear, it carries the same meaning as mentioned in the previous method.
A simple modification to the formula
We can preset a number to be the denominator or numerator by tweaking the formula slightly, such as # ??/10, for example. This will fix the number 10 as the denominator.
Sheets will ensure to keep the fixed denominator as it changes from decimals to fractions. So 6.3 for example would become 6 3/10 and 4.7 would become 4 7/10.
Please note that this does not work for numerators.
Hope this article helped you to learn something new today.