Spreadsheet problem generator part II: formatting numbers and magnitudes
Posted by Greten on 06 Jul 2019 under Tools
Numbers can be written in several different formats. The format they are written usually depends on the traditions or standards of the society or the industries that are using them. While 20% and 0.2 are equal, you would not hear any department store announcing "0.2 discount today"; it's always something like "20% discount today".
In working with science and mathematics problem, there are different formats of numerical data. Some quantities are expressed in terms of fraction or percent. Some problems will give specific times as expressed by clocks instead of giving the number of hours or minutes. In constructing a spreadsheet problem generator, you will encounter the need to format numerical data in more than one point
A previous discussed how to create a spreadsheet problem generator that will allow you to create several unique versions of the same kind of word problem: tweak the numerical values involved while retaining the words, and the spreadsheet will automatically generate the problem and the answer for you. However, this spreadsheet problem generator can accept only numerical values that are in decimal format. This post will explain how to format the cells and enter your word problem so that other possible numerical values are formatted accordingly.
This post will provide examples for LibreOffice Calc and MS Office Excel, in particular LbreOffice 6.2.2.2 and Office 2016. Google Sheet works in a different manner, so I will cover it in another post.
I. Formatting units
This is possibly the easiest formatting because we are not concerned with any of the numerical data that we must tweak to come up with a new problem. Most units are very easy to encode because they are simply letters or symbols placed after a number. However, some units are difficult to enter either because they are not on the keyboard or because of limitations of the spreadsheet application.
Some of the units that you might find difficult are as follows:
- The unit Angstrom (Å), unit of length equal to 1/10,000,000,000 or 1.0 × 10-10 of a meter.
- The prefix symbol for micro in SI is the Greek letter mu (µ).
- Units that contain superscripts such as 2 or 3 (e.g., m/s2 and cm3)
In Excel and Calc, you can insert special characters for units such as Angstrom (Å) and the SI prefix micro (µ) using Insert » Symbol and Insert » Special Character respectively. You can also insert mu and other Greek letters by typing a letter, highlighting it, and then changing the font to Symbol. You can change the format a selected text within a cell while keeping the original format for the rest.
You can format a character within a cell to superscript or subscript; highlight the character, right-click on it, and select Format Cells for Excel or Character for Calc. Both options will open a window that provides you with several character formatting options. The name of the option in Excel can be confusing; it is called Format Cells even if it affects only one or more characters within the cell.
Download here examples of Calc and Excel spreadsheets that use special characters and superscripts as units.
II. Percentage and Currency
If you encoded a number in a spreadsheet cell that is not yet formatted and typed the percent sign after that number, that cell would automatically be formatted into a percent. You may also format a cell by right-clicking on it, selecting Format Cells, clicking the Number tab, and selecting Percent from the choices. After formatting this cell, any number that you encoded in this cell will automatically be formatted into percent.
You can also format the numbers using the Format on the top horizontal menu of Calc and the Number group under the Home ribbon menu in Excel.
Percent and hundredths decimal are equal. A 100% means 1, a 50% means 0.5 or 1/2, a 25% means 0.25 or 1/4, and so on. Thus, say you encoded 40% at A1, encoded 20 at B1, and encoded "=A1*B1" at C1, C1 will display 8.
Currency is a number format that is similar to the normal decimal format except that it always includes a currency sign and two decimal places to correspond to currency subunits such as centavo; these decimal places are set as zero and not removed if the amount of money is a whole number. Note that some currencies such as Tunisian dinar's milim and Kuwaiti dinar's fil are divided into 1000 instead of 100 subunits, and thus such currency format should include three decimals or zeroes.
If you encoded the cell name of the cell containing a percentage or a currency in the word problem, the percentage value is automatically converted to a decimal value, and the currency is automatically stripped of the currency sign and trailing zeroes on the right side of the decimal point. For example, you placed 12% in cell B3 and ₱ 1,000,000.00. Then, you encoded the following in a merged cell (or any cell containing the problem):
="A real estate broker closed a "&D3&" worth of land at "&B3&" commission. How much did he earn?"
You will get results similar to what is shown in the screenshot below:
To format the numbers in the word problem itself, you need to use the TEXT function, which allows you to obtain data from other cells and at the same time, format them. The formatting of the TEXT function is separate from the format done in the source cell and thus can be formatted separately.
You need to encode the word problem into something like this:
="A real estate broker closed a "&TEXT(D3,"₱ #,##0.00")&" worth of land at "&TEXT(B3,"0.00%")&" commission. how much did he earn?"
Using the text function, it works as TEXT([cell name],[format]) and can readily replace the cell name themselves, which you can then put inside the TEXT function. Similar to cell name, it must have AND operator (&) before and after it to separate it from strings and other functions.
You will get results similar to what is shown in the screenshot below:
There are too many possible combinations of hashes, zeroes, and symbols that you can use to format the numbers using the TEXT function, and it would be difficult to memorize them. The easiest way to do it is to:
- Format the cell containing the given data (the same cell that the TEXT function calls) in the way you want: adjust the number of decimal places, edit the thousand separators, whatever suits you.
- Look for the Format code. In Calc, you can find it at the lower part of the Format cell window.
In Excel, you need to go to the Custom format. It will automatically highlight the current format in the list and display the format code under Type field. - Copy the Format code or Type by highlighting it in the field.
- Encode the TEXT function as follows: TEXT ([cell name], [format code]). Use the format code you obtained from the Format code field of Calc or Type field of Excel. For example:
TEXT(D3,"₱ #,##0.00")
To know more about formatting numbers and percentage, like the meaning of those zeroes and hash signs in the TEXT function and Format code, please read my posts about formatting numbers, percentage, and currency.
Click here to download samples of percentage and currency formatting in LibreOffice ODS and MS Excel XLSX formats.
III. Notes on formatting thousands separator and decimal mark
In formatting numbers, you could not just change the Format code to obtain the thousands separator or decimal mark that you want. Suppose you wrote 1000000 in cell C3. Then, you open the Format Cells option window and checked the "thousands separator" checkbox and set decimal places to 1. You will notice that the Format code is now #,##0.0 and cell C3 will now display 1,000,000.0.
Then, to contain the actual problem itself, you merged some cells and encoded what is shown below. You copied the Format code in the Format Cells option window and used it in the TEXT function.
"There are "&TEXT(C3,"#,##0.0")&" people in the...
The merged cell will display:
"There are 1,000,000.0 people in the...
This is possibly a bug, but if you replaced the comma in the format code inside the TEXT function, it applies only to the first thousand separator. For example, using space:
There are "&TEXT(C3,"# ##0.0")&" people in the...
It displays:
"There are 1000,000.0 people in the...
This applies to both Calc and Excel. I do not know what's causing this. A solution is to anticipate the highest number of digits you think you will encode in the cell referenced by the text function. For example:
There are "&TEXT(C3,"### ### ##0.0")&" people in the...
It will display:
"There are 1 000 000.0 people in the...
If the number in C3 is just 1000, It will display:
"There are 1 000.0 people in the...
If the number in C3 has more than nine digits, that's more than the zeroes and hash. For example, 1 billion. It will display:
"There are 1000 000 000.0 people in the...
Notice that space disappeared again? Hence, anticipate the number of digits you need for the reference cell and enter the Format code accordingly.
Conclusion
There are different numbers, different formats, and they can be used for different purposes. Spreadsheets are excellent software, but technology is limited, and we have to use it the best way we can. While spreadsheet cannot capture all the conventions used in science and mathematics, we can make word problem generators that come closest to these conventions as possible.
I was initially planning to make only one post for formatting the numbers in the spreadsheet problem generator. However, while studying how to go with this post, I realized that date and time are a different kind of number. Please visit the next post to know more about using and formatting date and time in the spreadsheet problem generator.
Last updated on 01 Sep 2019.
Learn from others
Greten says: Hello Jayna, thanks for dropping by.
You are referring to the superscripts that exist for some numbers as separate characters right? That’s what I would like to introduce about formatting in Google Sheets, but I consider this as last resort option because not all numbers have equivalent superscript as separate characters.
Share your thoughts
* Required. Your email will never be displayed in public.
One extremely useful trick I have not seen described elsewhere is inserting the units of a number within the cell containing the number. LibreOffice Help tells how to do this (” text” in the number format), but not how to get superscripts (so one can have “6 cm³”, rather than “6 cm3” or “6 cm^3”, etc.
The trick is to copy the superscript from the special characters menu. It took me awhile to think of that!