Formatting the spreadsheet problem generator
Posted by Greten on 17 Mar 2012 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, we encounter different formats of numerical data that can appear side-by-side each other. 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 (using LibreOffice/OpenOffice Calc or MS Excel), we will definitely encounter the needs to work on these kinds of problem.
In my previous article, I discussed how to create a spreadsheet problem generator that will allow you to create several unique versions of the same kind of problem by tweaking the numerical values involved, 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. In this post, I will discuss how to format the cells and encode your word problem so that other possible numerical values are formatted accordingly.
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 placed after a number. However, few of the units are difficult to encode either because they are not in keyboard or because of limitations of the Spreadsheet software.
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. You can encode it by clicking Insert » Special Character and select it from a gallery of possible characters. Alternatively, you can use the CHAR(197) function.
- The prefix symbol for micro in SI is the Greek letter nu (µ). You cannot use the "Symbol" font for this purpose. The reason is that while you can separately format a single character in a cell that contains only text, you cannot do it in a cell that contains combined text string (enclosed by double quotes) and cell names separated by ampersand (&), just as how the word problems were encoded. The way to go around this is to select the separate µ character in the aforementioned Special Character gallery or use the function CHAR(181).
- For units that contain 2 or 3 (e.g., m/s2 and cm3); again, you cannot format the 2 and 3 as superscripts separate from the rest of the character string. Thus, nothing will happen if you highlight 2 or 3 and formatted it as superscript. However, you can use the the character functions CHAR(178) for 2 and CHAR(179) for 3.
Click here to download samples of unit formatting: [LibreOffice/OpenOffice Calc]. You can also open the file in MS Excel 2010.
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 will automatically be formatted into a percent. You may also format a cell by right-clicking on it, selecting "format cell", 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.
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, if 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 are similar to the normal decimal format except that it always include a currency sign (either before or after the number depending on the country of origin) and includes 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 decimal value and the currency is automatically stripped of the currency sign and trailing zeroes on the right side of decimal point. For example, you placed 12% in cell B3 and ₱ 1,000,000.00 (formatted as Currency » Php ₱ Hilagaynon in LibreOffice/OpenOffice Calc). 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 replaced the cell name themselves while cell name is also one of its arguments. Similar to cell name, it must have AND operator (&) before and after it except if it's the very first or the very last part of the string in which it only needs & after it or before it respectively.
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 (but you will memorize them if you're working on spreadsheet problem generator and TEXT function often). 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 at the lower part of the Format cell window and copy it.
- In the TEXT function, delete everything in the double quotes on the right of cell reference and paste the Format code there.
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: [Open Document Spreadsheet (ODS)]. You can open this in either LibreOffice Calc or OpenOffice Calc. MS Excel 2010 also supports ODS.
For those who might ask how I managed to put the peso sign, I simply searched Google for "Philippine peso", saw the sign on the first result (Wikipedia), highlighted the character, copied and the pasted it. I was initially planning to include this together with how to type other currency symbols and special characters in Part I: Formatting Units but I figured out it will be useful for other purposes, not just in creating a spreadsheet problem generator so perhaps, I will just make a separate article on how to type special characters.
III. Notes on formatting thousands separator and decimal mark
In formatting numbers, I explained how you cannot just change the Format code to obtain the thousands separator or decimal mark that you want. You need to configure the language of that cell to the language or culture that uses the thousands separator and decimal mark that you want e.g., set the language to Georgian if you want to use space as thousands separator while keeping the period as decimal mark or decimal point.
However, in using the & sign and TEXT function to join text strings with formatted cell references, there's no way to assign language to it. Suppose you wrote 1000000 in cell C3. Then, you press CTRL + 1 to 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 ... part denotes that there are more part of the problem but I will no longer complete it because I'm only trying to explain how the thousand separators and decimal mark work. The merged cell will display:
"There are 1,000,000.0 people in the...
What if you intend to use character space as thousands separator instead. As explained in formatting numbers article, you can do this by setting the C3 cell to Georgian language. With the thousands separator checked and decimal places set to 1, your Format code will become # ##0.0.
However, you cannot use # ##0.0 in the TEXT function because it will split only the hundred and thousand digits. It will look like this:
"There are 1000 000.0 people in the...
Note that in the merged cell, the format of number is affected only TEXT function and not by the format of the reference cell. The reason why the 1000 000.0 id displayed this way is that that there's no way to tell the TEXT function that the language should be in Georgian. Thus, the merged cell will use the default language (English - Philippines in my case).
The solution here is to make the merged cell Georgian as well (or whatever language uses the thousands separator and decimal mark that you want). It will then look like this:
"There are 1 000 000.0 people in the...
No need to worry about Calc checking the text based on the grammar of the language that you set, it does not seem to work that way. Try pressing F7 and see if Calc tried suggesting replacing words with those from the language that you use; in my testing it did not. The only limitation of this solution is that all numerical quantities must have the same thousands separator and decimal mark.
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 comes closest to these conventions as possible.
I was initially planning to make only one post for formatting the numbers in spreadsheet problem generator. However, while studying how to go with this post, I realized that date and time are a totally different kind of number. Please visit by next post to know more about using and formatting date and time in spreadsheet problem generator.
References:
- ANSI character set (accessed feb 29)
- Weight of pollen grain (accessed march 17)
- Tunisian milim (accessed march 17)
- Kuwaiti fil (accessed march 17)
Last updated on 20 Apr 2013.
Share your thoughts
* Required. Your email will never be displayed in public.