Now that Calcapp has comprehensive support for formatting numbers for 79 languages and 147 language variants, it’s high time that we enable you to format numbers directly from formulas. Luckily, that’s exactly what our latest release does.
You have been able to access the formatted value of a number field
from a formula for a long time now: just add .FormattedValue after
the field name. However, what if you want to format arbitrary values
you calculate using a formula? You can move that part of the
calculation to a hidden
field, change the way its value is formatted in the inspector and
access the formatted value from the original formula, but that’s
cumbersome in comparison to just being able to format the number
directly with a function.
The new FORMATNUMBER function does what its name implies. It accepts a number of parameters, but if you just supply the number to format, it is formatted with sensible defaults for the language your app has been configured to use. The second and third parameters control the number of decimal places, the fourth parameter determines if thousands separators are used and the fifth parameter is used to supply the minimum number of integer digits to use, padding the returned number with leading zeroes if necessary. The final two parameters supply the leading and trailing units, if any. All parameters but the first one are optional.
To format 3500.5 as “003,500.50 kg”, use this formula:
This formula formats the number with two decimal places at all times, with thousands separators, with at least six integer digits and with the trailing unit “kg.” The leading unit is left out by supplying a blank value through the BLANK function.
FORMATPERCENTAGE is similar to FORMATNUMBER but exclusively formats
percentages. FORMATPERCENTAGE(0.25)
returns “25%”. There is also FORMATSCIENTIFIC for formatting numbers
using scientific notation (which formats 10000 as “1.00E+04”) and
FORMATFRACTION for formatting numbers as an integer followed by a
fraction (formatting 3.75 as “3 3/4”). Read full documentation for
the four new functions using the reference sidebar.
FORMATNUMBER, FORMATPERCENTAGE, FORMATSCIENTIFIC and FORMATFRACTION all turn a number into a text string. To convert a text string holding a number into a true number instead (which you can use in calculations), use the four new functions PARSENUMBER, PARSEPERCENTAGE, PARSESCIENTIFIC and PARSEFRACTION. (Turning a text string into a number is known as parsing the text string.)
The formula PARSENUMBER("0023.500"),
for instance, returns 23.5 if the language of the app is set to US
English. (A French app would not recognize the decimal period as a
decimal separator, it would expect a comma.) Again, refer to the
reference
sidebar for details.
Differences between the new functions and older functions
In January, we introduced the
functions TONUMBER, TOLOGICAL and TOTEXT. The new functions appear
superficially similar. After all, to turn the number 23.5 into the
text string “23.5”, you could easily use TOTEXT(23.5) and to
turn the text string “23.5” into the number 23.5, you could use
TONUMBER("23.5").
The difference is that the new functions take the language of the app into account. TOTEXT always uses a period for a decimal separator, whereas FORMATNUMBER uses a comma if the app language is French, a period if the app language is English and “٫” if the langauge is set to Arabic. Also, FORMATNUMBER, FORMATPERCENTAGE, FORMATSCIENTIFIC and FORMATFRACTION support a large number of additional options for customizing the result.
Similarly, PARSENUMBER, PARSEPERCENTAGE, PARSESCIENTIFIC and PARSEFRACTION differ from TONUMBER in that they take the app language into account. Also, they are far more forgiving of potentially problematic values (“($001,234.5)”, “-1,234.50 lbs” and “?-001234.500” are all successfully turned into the number -1234.5).
Differences between FORMATNUMBER and FIXED
Prior to this release, Calcapp actually had a function which returned a number as a formatted text string: FIXED. This function, which we added almost two years ago to improve compatibility with spreadsheets, takes parameters specifying the number of decimal places to use and whether thousands separators should be used.
To format 3500.5 as “3,500.50”, use this formula:
Behind the scenes, FIXED has been changed to invoke the new FORMATNUMBER function, meaning that it has the same great language support as the newer function. You’re welcome to use it to make it easier to transfer formulas between Calcapp and your favorite spreadsheet.
FIXED does offer one feature not provided by FORMATNUMBER: you can set the second parameter, which determines the number of decimal places to use, to a negative value to round the given value to the left of the decimal point.
For instance, FIXED(123.456, 1)
returns “123.5”, FIXED(123.456, 0)
returns “123”, FIXED(123.456, -1)
returns “120” and FIXED(123.456, -2)
returns “100”.
What about formatting and parsing dates and times?
While the four new formatting functions and the four new parsing functions cover a lot of ground, there are no new functions for formatting and parsing dates and times. We’d love to add the functions FORMATDATE and PARSEDATE in the future, but doing so would require many engineering hours and we’d rather spend our time doing work we think you’ll value more.
Do you have a pressing need for formatting and parsing dates and times today from formulas? Let us know.
Meanwhile, you can use the technique we alluded to before to format a
date from a formula: move the part of your formula that calculates
the date to a field you make hidden after making it
a date and time field and customizing the formatting using the
inspector. Then, simply reference the formatted value from your
original formula using the FormattedValue property. If your
hidden field is named DateField, you can simply write
DateField.FormattedValue
in your original formula to make use of the formatted value.
Excel’s number formats and its TEXT function
Microsoft Excel and most other spreadsheets support number formats for formatting numbers, dates and times. A number format is a text string which uses a very particular format determining how a number should be formatted according to the formatting rules of a particular language and language variant.
For instance, “#.##” is used to format a number with zero, one or two decimal places and uses as few decimal places as possible. “#.00” is used to always format a number with exactly two decimal places. Number formats also support padding numbers with leading zeroes, can use scientific notation and supports formatting dates, times and fractions.
Spreadsheets often support formatting numbers using an easy-to-use graphical interface (not unlike the inspector in Calcapp Creator), but also typically enable users to set the number format directly. To format a number from a formula, spreadsheets provide the TEXT function.
To format 3500.5 as “003,500.50” in a spreadsheet, use this formula:
The zeroes to the left of the decimal point signify that leading zeroes should be used to pad the number to six digits. The comma signifies that thousands separators should be used. The two zeroes to the right of the decimal point signify that exactly two decimal places should be used.
Early on, we made the call not to support number formats. They are complex to support and our feeling is that few users understand them well. Further, they support features which aren’t relevant in Calcapp, such as aligning numbers so they line up in a column, or duplicate features we already support using other means.
For an example of the latter, consider assigning colors to values. To make a value green if it is less than or equal to 50 and red otherwise, this number format may be used:
This syntax is special to number formats — you can’t invoke functions, do any other calculations or reference other cells. Also, only eight named colors are supported, with support for an additional 57 colors opaquely named Color1 through Color57.
In contrast, Calcapp uses formulas to assign colors to a field. To get the same effect in Calcapp as with the number format above, associate a formula with the Color property of a field named Field1:
As this is a regular formula, you have the full power of Calcapp’s formulas at your disposal, meaning that you can reference other fields and perform calculations. Also, you’re not limited to assigning a color to the actual number. To change the background color of the field instead, associate the formula with the BackgroundColor property instead. Finally, Calcapp’s many color functions make it easy to do things like making a field progressively redder the closer the value gets to a certain value.
As such, we don’t think that Excel’s number formats are a good fit for Calcapp. While there are a few things number formats can do that Calcapp can’t match (at least not without using a custom formula), we think that the vast majority of users are better served by our four new functions FORMATNUMBER, FORMATPERCENTAGE, FORMATSCIENTIFIC and FORMATFRACTION.
We invite you to try them out and look forward to your feedback.