Formatting cell data in Calc part III: percentage, fraction and scientific notation
Posted by Greten on 13 Apr 2013 under Tools
This post assumes that you already selected the cell(s) that you want to format and already opened the Format Cells option window. If not, please visit this prerequisite post on cell formatting.
Formatting percentage, fraction and scientific notation is very much similar to formatting numbers because the format code is also made of hashes (#) and zeroes (0). However, they have extra symbols that control the other properties of their formats.
Formatting percentage
The second option that you can select from category of number formats is the percentage. Percentage works very much like a number because it is just another way of expressing number. The rules on Format code and computations are the same as number. It's just that it is expressed differently.
The Format code for percentage can be any possible Format code for numbers followed by % sign. It could be #0.00%, or #,##0.0% or #%. Note that there's no space before the percent sign. The default configuration of percentage is that it always has two decimal places but you can alter it by changing the decimal places attribute or by editing the Format code.
In computations, percentage is simply the number besides the percent sign divided by 100. Thus 25% also means 0.25, 3% means 0.03 and 0.5% means 0.005. If your A1 has 38, your B1 has 50%, and you encoded =A1*B1 in C1, it will display 19.
Formatting fraction
Fraction has the Format code # ?/? and has no decimal part because fractions can display only whole numbers in three locations, the whole number part on the left, the numerator and the denominator. The fraction part itself replaces the decimal part.
If you encoded something like 4 3/5, Calc will automatically recognize it as such and show it as 4 3/5. However it's actual value as shown in the input line will be the decimal 4.6. You cannot edit the data as seen in the input line. It shows how Calc actually stored the numerical data. You can however, wipe-out what is written in the input line and encode the fraction the way you want or encode a new fraction in its place.
For example, you want to edit the data 4 3/5 to 4 2/7. You cannot edit the 4 3/5 (unless you are fast at converting decimals to fractions) 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 it's decimal equivalent 4.28571428571429.
The thousand separator is grayed-out and cannot be checked if you formatted a number as a fraction. However, you can force the whole number part to display comma thousand separator is you change the # part to #,###. The separator will also appear on the left of 6th, 9th, 12th and so on digits, but it only allows comma as thousand separator. Using a character space or a point in Format code will result to not having any thousand separator. Not even setting the language to German or Turkmen will work.
In practice, we rarely see mixed fractions in which the whole number part reachers 1000, since if someone is willing to use that much digit, they might as well use decimals instead of fractions. However, in teaching mathematics, you might want to include rare combination of numbers that are theoretically correct even if rarely used in practice.
WARNING: Special character fractions
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 whole cell become formatted as Text and the fraction is replaced by the equivalent special character.
The Format Cells option window indicates that the format is General under the Number category, but we can tell that it is a Text by the fact that it is aligned to the left instead of to the right for numbers and it's not possible to perform mathematical computations using a cell that contains a special character fraction. You can also tell if a fraction contains a special character based on the character's size. In a real fraction, the whole number part, the numerator and the denominator should be of the same size. However, in a fraction containing special characters, the numerator and the denominator looks smaller than the whole number part. You can see these indicators in the screenshot below.
Only these three are problematic so your best solution is to write the number in decimal form, and then format it as 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.
Formatting scientific
This is the formatting for scientific notation. However, it is not useful in constructing spreadsheet problem generator for science and mathematics because it is not presented in a way similar to how scientific notations must be written e.g., 3.14 × 108 but in the shortcut method that is usually used in calculators e.g., 3.14E+008. Calculator manufacturers did this because technology was limited at that time and they cannot insert exponents. New calculators, however, display scientific notations properly and so I have no idea why Calc (and other spreadsheet software like MS Excel, 2010 is the last one I checked) insists on this format.
My suggestion is that you just use the General number format or any format you can produce by combinations of hashes(#), zeroes(0) and decimal point. Then, manually convert it to scientific notation in your quizzes and lesson plans.
I would also like to point out that hashes behave in a different manner in scientific format. They behave like zeroes. It's default format is 0.00E+000 meaning, it will always have trailing zero if the decimal part has less than two digit, and round off if it has more than two digits. If you change this to hash, like 0.##E+000, the format will still be the same; trailing zero for one-digit decimal. Thus, there's no difference between hash and zero in scientific format.
Like fraction, the true value of a number formatted in scientific is actually in decimal notation. If you have say 1.230E-004 in a cell, if you access that cell and check the input line, you will see 0.000123 instead. You cannot edit the base and exponents separately, just like in fractions wherein you cannot edit the numerator and denominator separately. However, it is much easier this time because editing the base is simply editing the number as seen in the input line, while editing the exponent is just a matter of adding or removing zeroes in the number.
Last updated on 29 Apr 2013.
Learn from others
Greten says: Hi Ron
Are you sure you are referring to the topic of this post, or is it about LibreOffice Math as embedded in ODT file?
David says: Trying to set up LibreOffice database, but the two decimal points always revert to zero. Why? For example if I write in 120.87 the systems writes in 120.00 And if I try to change the .00 to say, 50, it still reverts to .00 What am I doing wrong?
Greten says: Hi David, are you sure you’re talking about LibreOffice Calc and not LibreOffice Base? That’s the application for database.
Share your thoughts
* Required. Your email will never be displayed in public.
I can type a fraction with a horizontal fraction bar and it appears OK ini Libreoffice. However, after I save, close the document and reload it, the fraction has moved upward on the screen and seems to appear as an exponent. I can double-click the fraction and it moves back down where it is supposed to be. However, If I save again, close and reload it is back up. Any suggestions.