Formatting cell data in spreadsheet part V: fractions
Posted by Greten on 22 Aug 2020 under Tools
You can express all rational decimal numbers as a fraction of integers. You can write numbers with terminating decimals like 0.25 as fraction 1/4, and numbers with repeating decimals like 0.333 as 1/3. You can even express whole numbers as fractions; 1, for example, can be expressed as any fraction where the numerator and the denominator are the same numbers. Indeed, the definition of a rational number lies in the fact that you can express it as a fraction of integers. Thus, numbers with non-repeating and non-terminating decimals, such as pi (3.14159...), fall under irrational numbers.
This entry is part V of the post series: Formatting cell data in spreadsheet software programs. The features and functions discussed in this entry are based on Calc 7.0.0, Excel 365, and the version of Google Sheets as of August 2020.
Encoding and editing fractions
In Calc and Excel, if you encoded something like 4 3/5, the spreadsheet will recognize it as a fraction and format it accordingly. However, the Input line or Formula bar will display it in decimal format, 4.6. You cannot edit the data as a fraction. If you double-click a cell containing a fraction, it will turn into decimal when its edit mode is active. You can, however, wipe-out the content of the cell and reenter the fraction the way you want or encode a new fraction in its place.
For example, you want to edit 4 3/5 to 4 2/7. You cannot change 4 3/5 directly since the input line shows 4.6. You can, however, delete the whole 4.6, type 4 2/7 in its place, and press Enter. You will see that the fraction is now 4 2/7. If you access that cell again, what you will see in the input line is not what you typed, but its decimal equivalent 4.285714....
Sheets cannot autodetect fractions. If you type 4 3/5 in a cell, Sheets interprets it as a text, and you notice that it is aligned to the left. You can enter a fraction in Sheets by encoding the decimal number equivalent of the fraction, and format the cell as a fraction.
Each spreadsheet application has its own quirks if you will rely on them to format the fractions for you.
Calc format common fractions to special characters
Be careful about the common fractions 1/2, 3/4, and 1/4. There are special characters made only to represent them. If you type any of these fractions, with or without a whole number part, the cell becomes formatted as Text, and the equivalent special character replaces the fraction.
The cells with special characters are formatted as text, and you will encounter errors if you use them as reference for a mathematical operation. Only these three are problematic, so your best solution is to write the number in decimal form and then format it as a fraction. The decimal equivalents of 1/2, 3/4 and 1/4 are 0.5, 0.75 and 0.25 respectively. Suppose you want to encode 5 1/2, just type 5.5 in a cell. Then, format that cell as fraction by opening the Format Cells option window and selecting Fraction.
There are other fractions that have special characters such as 1/8, 3/8, 5/8 and 7/8 but they do not trigger conversion to special characters when encoded. They are also listed separately in the special character map from 1/2, 3/4 and 1/4.
Excel and Calc formats two integers with slash in between them as dates
In Excel and Calc, the application can detect only an encoded mixed fraction, such as the aforementioned 4 3/5. You can encode a proper fraction and have the spreadsheet detect it if you enter zero in place of the whole number part; type 0 1/2 and the cell will show 1/2 with 0.5 in the Formula bar.
If you enter just two integers separated by a slash, the spreadsheet is going to interpret it as a date. For example, enter 1/2 and the Excel will turn it to 2-Jan (January 2). Calc, as mentioned already, formats 1/2 into a special character, but if you enter 1/5, it also formats the entry as a date 01/05/20; the current year is automatically appended. If the numbers are out of range of those used as dates, such as 33/50, it will turn into text.
Default format reduces the accuracy of decimal to fraction conversion
The default Format code for fractions is # ?/?. The # is the whole number part while ?/? is the fraction. There are several other default formats available such as # ??/?? or # ?/10 (the denominator is always 10). Calc, Excel, and Sheets differ on what default fraction formats they offer.
The default fraction format can be tricky because it may not display the actual fractional form of a decimal number. The Format code # ?/? displays only fractions in which the numerator and the denominator have single digits such as 1/2 and 1/3. For numbers like 2.25, the Format code of # ?/? will display it as 2 1/4.
However, there are decimal numbers that cannot be expressed with just a single digit in the numerator and the denominator. For example, the correct fractional form of 3.775 is 3 31/40. Reformat your cell to # ??/?? to accommodate the display of two digits. If the cell containing 3.775 has Format code of # ?/?, the spreadsheet will look for the nearest number that can be expressed as a fraction of two single-digit numbers; that nearest number is 3.777... (repeating decimal), which can be expressed as 3 7/9. Thus, 3.775 displays as 3 7/9.
Now, you may think, what if you enter the format code as # ???/??? or # ????/????; the idea is the more question marks in both the numerator and the denominator, the greater is the accuracy of the conversion from decimal to whole number.
Formatting with more question marks creates a whole new issue. If you have more question marks and the fraction requires fewer digits, extra spaces appear between the whole number and the fraction. The screen capture below shows a Google sheet where different decimal numbers are listed on the left, and on the right are the different versions of the decimal numbers' equivalent fractions using different Format codes.
Cells in red are the inaccurate fractions for the decimal numbers in column A. Cells in yellow are the fractions with unnecessarily larger spacing. Cells in green are those that contain both inaccurate fractions and unnecessarily larger spacing. The spreadsheet in the screen capture above is a Google Sheets file that you can find here. You can also download similar spreadsheets for Excel and Calc.
- Fraction spreadsheet for Google Sheets (link)
- Fraction spreadsheet for LibreOffice Calc (download)
- Fraction spreadsheet for Microsoft Excel (download)
Use hashes in fraction's format code code
When you checked the linked Google Sheets or downloaded the Excel or Calc files, you will see that columns F to I are similar to columns B to E except that they use a Format code with hashes(#) instead of question marks adjacent to the slash. By replacing the question marks with hashes in the Format code, you can have a fraction that restricts on the number of digits that you decide instead of 1, 2, or 3 digits that comes with the default fraction formats, and at the same time, does not create unnecessary character spaces.
The format codes are as follows:
- # #/# - one digit for the numerator and the denominator
- # ##/## - two digits for the numerator and the denominator
- # ###/### - three digits for the numerator and the denominator
- # ##/### - two digits for the numerator and three digits for the denominator
- # ####/#### - four digits for the numerator and the denominator
These codes are mere examples. You can have any other combination that you want. The hash on the left separated from others with a character space represents the whole number part of a mixed fraction and can be of any number of digits. The hashes adjacent to the slash are for the numerator and the denominator, and each hash represents a digit. In general, the more hashes on either side of the slash, the more accurate is the fraction that the spreadsheet cell shows. However, I cannot imagine anyone having any need to have five digits or more in their numerator or denominator; such quantity is better expressed as a decimal number.
These fraction Format codes work in Calc, Sheets, and Excel. To enter these codes:
- In Calc, open the Format Cells window by right-clicking on the cell or by pressing Ctrl+1. When the Format Cells window opens, select Fraction under Category, edit the code in the Format code field, and select OK.
- Calc has this quirk that it changes the rightmost hashes of both the numerator and the denominator into question marks and inserts a pair of quotes with a character space after the leftmost hash (the code for the whole number part). For example, it changes # ###/### into #" "##?/##?.
- This quirk does not change what the cell displays. A # ###/### and #" "##?/##? for example, both display up to three-digit numerator and denominator with a single space between the fraction and the whole number.
- This quirk happens only after you close the spreadsheet file and then reopen it again. If you close the Formal Cells window and then reopen it again to check the Format code, the codes are still hashes, space, and slash only.
- In Sheets, open the Custom number formats. On the top menu, select Format » Number » More Formats » Custom number format. Enter the format code on the top field and click Apply.
- In Excel, open the Format Cells window by right-clicking on the cell and selecting Format Cells. Under Category, select Custom. Then, type the Format code in the Type field, and click OK.
Enter improper fractions
The default setting of spreadsheet applications is to show fractions as a proper fraction if the number is between -1 and 1, and as a mixed fraction if the fraction has absolute value great than 1. A mixed fraction is a fraction that comes with a whole number; for example, 1 1/2. The Format codes in the previous section show only proper and mixed fractions.
An improper fraction is a fraction in which the numerator is greater than the denominator; it is merely an alternative way of writing numbers with absolute values greater than 1. An example of an improper fraction is 3/2, which is equal to 1 1/2 and 1.5.
To show an improper fraction, all you need to do is to remove the leftmost hash in the Format code that is separated from the others by a space. The Format codes that can present improper fractions are:
- #/# or ?/? - one digit for the numerator and the denominator
- ##/## or ??/?? - two digits for the numerator and the denominator
- ###/### or ???/??? - three digits for the numerator and the denominator
- ##/### or ??/??? - two digits for the numerator and three digits for the denominator
- ####/#### or ????/???? - four digits for the numerator and the denominator
The difference between using hashes and question marks is that hashes do not generate extra space between the fractional part and the whole number part of a mixed fraction. Since improper fractions do not have a whole number part, using hashes or question marks shows the same fraction format in spreadsheet cells. You will see this in the last eight columns of the spreadsheets provided above.
Notice that column J shows the same fraction format as that of column N. Same goes for columns, K and O, L and P, and M and Q.
Spreadsheet applications can detect fractions in some circumstances, but in most cases, you need to enter a decimal number and manually format it as fractions using number codes. Depending on the Format code, the cell may show the real fraction equivalent of a decimal number, or merely its closes approximation depending on how many digits its Format code allow it to display. By default, spreadsheets will show mixed fraction formats for numbers with an absolute value greater than one, but you can force them to show improper fractions by merely removing the first hash on the left.
Last updated on 22 Aug 2020.
Share your thoughts
* Required. Your email will never be displayed in public.