Spreadsheet problem generator part III: concatenate cells with special number format
Posted by Greten on 11 Aug 2019 under Tools
The codes used and examples of problem generators in parts I and II covers only the situation where the cell format is a general number. When you encode a general number, it is easy to concatenate the cell references in the cell that contains the full text of the word problem because what you see in the cell source is what you will also see in the concatenated word problem text.
However, if you formatted the cells where you enter the given values using one of the specialized number formats, like percentage and currency, the cell that contains the concatenated text and cell references will not copy the format from the source cells.
In case of a percentage, the percentage value is automatically converted to a decimal value. In case of currency, it 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:
How can we properly display a word problem if the concatenated text does not reflect the format from the cell reference? This entry will cover how to format cells and how to display a format in a concatenated cell
There are many ways you can format the cells based on your specific need. In this post, we will use percentage and currency to demonstrate this.
Formatting cells as percentage
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. The right-click option is not available in Google Sheets.
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. In Sheets, you can format a cell as percentage by going to the top bar menu and selecting Format » Number » 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, say you encoded 40% at A1, encoded 20 at B1, and encoded "=A1*B1" at C1, C1 will display 8.
Formatting cells as currency
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 by 1000 instead of 100 subunits, and thus such currency format should include three decimals or zeroes.
Currency formats in Excel and Calc
In Excel and Calc, to format a cell as currency, right-click on it and select Format Cells. In the Format Cells window, select Currency under Category. You can then further customize your currency number by changing its symbol and the number of decimal digits.
Shown below are the Format Cells windows for Excel (left) and Calc (right).
Currency formats in Sheets
As mentioned earlier, Google Sheets does not show an option to format cells when you right-click on the cell. You can format a cell as currency by moving the mouse pointer in it, and selecting Format » Number » Currency from the top bar menu. However, this option defaults to the local settings (seen in File » Spreadsheet settings » Locale) and does not provide any option to change the symbol or number of decimal digits, e.g., if the Locale is set to the United States, then the currency default to US Dollar.
To have the option to change the number of decimal digits and the currency symbol, select Format » Number » More Formats » More currencies instead. You will see a window like this:
Unlike in Excel and Calc, you do not have much freedom in changing the number of decimal points. Instead, the possible formats you can select depends on how the currency you selected is used. For example, Philippine peso and US Dollars are written only either as whole numbers or with two decimal digits. Hence, these two are the only two options for including decimal places for these currencies. Meanwhile, Bahraini Dinar can be displayed with three decimal digits, two decimal digits, or as a whole number, and thus these options are available if the selected currency is the Bahraini Dinar.
Using the TEXT function to display the word problem with proper number format
As mentioned in the introduction, the concatenated text does not recognize the format from the cell reference. If you formatted a cell as percentage and entered 20%, when display it in the word problem, it will show the raw number format 0.2.
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 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 Format code at the lower part of the Format Cells window.
- In Excel, right click the cell and open Format Cells, and then select Custom format. It will automatically highlight the current format in the list and display the format code under Type field.
- In Sheets, go to top bar menu and select Format » Number » More Formats » Custom number formats. The code is in the text field on the right of Apply button.
- Copy the code by highlighting it in the field and pressing Control C (CTRL+C).
- Encode the TEXT function as follows: TEXT ([cell name], [format code]). Use the format code you copied in step 3. For example:
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.
Notes on formatting thousands separator and decimal mark in the TEXT function
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 cell containing the problem will display:
"There are 1,000,000.0 people in the...
However, 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...
"There are 1000 000.0 people in the...
I am not sure if it is a bug, but the fact that this is how it works in Calc, Excel and Sheets, makes me think it is not. 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.
Inserting symbols other than comma or space can have varying results:
- Using hair space yields results similar to that use of space.
- Using a period, a common thousand separator in some countries, increases the number of digits as if the spreadsheet reads it as the decimal point despite having another decimal point to the right. I am not sure if the local settings need to be reconfigured to make the spreadsheet acknowledge period as thousand separator (and I haven't experimented yet).
- Other characters have different results that in some cases vary with the spreadsheets. Sometimes, a character is ignored by a spreadsheet (displaying no thousand separator) while in another spreadsheet, it will cause an error message.
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.
This post is the third of the four-part post series. Here is the complete list of all the posts under this series.
- Spreadsheet problem generator part I: basic word problem generator
- Spreadsheet problem generator part II: units with special characters
- Spreadsheet problem generator part III: concatenate cells with special number format
- Spreadsheet problem generator part IV: formatting date and time
This post was updated just three months ago, July 6. I am updating it again to include Google Sheets. I opted to update this post instead of writing a separate post just for Google Sheets to make it similar with parts I and and the former III (now part IV) that covered Excel, Calc, and Sheets. However, as this post expands, I realized that it would be better to split the special characters from special number formatting and TEXT function. Hence, this post about special number formatting is the new part III, while the post about date and time became the part IV. I also set its published date to August 11 so it will conveniently fall between part II from where this content split from, and the already published part IV, but it was really published on October 6.
Last updated on 06 Oct 2019.
Share your thoughts
* Required. Your email will never be displayed in public.