If you ever tried performing calculations with formatted numbers, you may have found inconsistencies or inaccuracies in your calculations.
This issue happens because formatting numbers renders numbers as strings within the specified decimal precision, while the underlying calculations continue to operate with full decimal precision.
Let’s take number 350.45 as an example.
If you apply the n0 format, the number will be rendered as 350. However, the actual calculations still use the original 350.45 value, leading to results that may appear inconsistent or incorrect.
The good news is, it is still possible to get the results you’re looking for.
This forum post describes the different approaches to ensure your calculations align with your formatted numbers. By implementing these methods, you can achieve consistent and accurate results as if you were performing calculations directly with the formatted values.
Rounding Numbers: Round, Round-number, Floor, and Ceiling XPath Functions
Let’s discuss the differences between the round(), round-number(), floor(), and ceiling() XPath functions when it comes to rounding numbers.
Round Function
The round() XPath function rounds a number to the nearest integer. In practical terms, this means that, for example, the value 350.45 will be rounded up to 350, while the value 350.98 will be rounded to 351.
Round-number Function
The round()-number XPath function rounds a number to the specified number of decimal places. In practical terms, this means that, for example, the value 350.4585 will be rounded up to 350.5 if the number of specified decimal places is 1 and 3.46 if the specified number of decimal places is 2.
Floor Function
The floor() XPath function rounds a number down to the nearest integer that does not exceed it. This means that both the values 350.45 and 350.98 will be rounded to 350.
Ceiling Function
On the other hand, the ceiling() XPath function rounds a number down to the nearest integer that exceeds it. This means that both the values 350.45 and 350.98 will be rounded to 351.
Now, you might be wondering which of these functions to use. The choice depends entirely on your specific use case.
For example, if you aim to achieve results similar to formatting numbers to n0, then the round() XPath function is recommended. If you aim to achieve results similar to formatting numbers to n2, then the round-number() XPath function is recommended.
Rounding Numbers: Format and Number Functions
Another option to round numbers is using the format() XPath function in combination with the number() XPath function.
If you recall the introduction of the forum post, you’ll remember that while formatting numbers using the “n” format allows you to display a number in the desired format, it also converts a number into a string, causing problems with calculations.
To tackle this issue, it’s essential to convert the formatted string back into a numeric value. You can achieve this by using a combination of the format() XPath function to specify the desired format, and the number() XPath function to convert the string representation back into a numeric data type.
number(format(@LineAmount, 'n2'))
By combining the format() and number() XPath functions, you can not only achieve the desired formatting for your data but also perform accurate calculations on individual data fields.
Format Function
The format() XPath function allows you to convert numeric or date-time values into strings displayed in a specified format, such as ‘n2.’ To explore various ways of formatting numbers using the format function, I recommend reading this forum post: Formatting of numbers.
If you’re interested in formatting dates using the format function, I recommend reviewing this forum post: How do you format a date?
Number Function
The number() XPath function converts its argument into a numerical value, making it ideal for converting our formatted string back to a number that we can use for accurate calculations.
Problems With Using Rounding Functions For Calculations
While the Rounding Numbers: Format and Number Functions and Rounding Numbers: Round, Round-number, Floor, and Ceiling Functions sections explain how to round individual numbers, using these functions in isolation when performing calculations on multiple lines at once won’t fully resolve the issues and will often return incorrect results.
The challenge arises because to get the expected results, you need to use these XPath functions on each line individually and perform calculations on the line level.
However, when used in combination with variables, these functions can deliver the desired outcomes. The process is explained in the next chapter.
Solution: Using The Rounding Functions with Variables
This solution follows a framework similar to calculating running totals.
We’ll explain the process using the round() XPath function and the combination of format() and number() functions.
This solution involves three critical steps, as shown below.
1. Creating The First Variable
Before the List tagging element, add a Var tagging element named ‘round’ and assign it a value of 0. The most convenient place for this variable is the header row of a table. This variable represents the starting point.
If you are using the format() and number() combination for rounding, you can name the variable based on the format you’re applying (e.g., ‘n2’). Regardless of the name, the value should be set to 0.
2. Creating the Second Variable
Within the List tagging element , place another Var tagging element after the Field tagging element representing @LineAmount. Name this variable the same as the first one (‘round’ or ‘n2’, depending on your chosen example).
Now, it’s time to assign a value to this variable, calculating the running total of the lines by progressively adding the rounded line amounts.
If You Are Using The round() XPath Function
var-element-value('round') + round(@LineAmount)
If You Are Using The Combination Of format() and number() XPath Functions
var-element-value('n2') + number(format(@LineAmount, 'n2'))
3. Rendering the Value
To display the value of the rounded running total, insert a field tagging element after the list tagging element and set its value using the var-element-value() XPath function. A new (summary) row after the list tagging element is the most suitable location for this field.
If You Are Using The round() XPath Function
var-element-value('round')
If You Are Using The Combination Of format() and number() XPath Functions
var-element-value('n2')
If you review your results now, you’ll notice that the calculations now align with your formatted numbers.
Closing Words
To summarize, formatting numbers using the format() XPath function or the ‘n format’ primarily affects the display of numbers while preserving their full decimal precision for calculations. The reason for the differences is that number formatting converts numerical data into strings, whereas calculations are still performed with decimal precision.
Furthermore, while using round(), round-number(), floor(), and ceiling() functions allow you to maintain numbers as numerical data types, correct results demand separate calculations for each line. Not doing so will still result in incorrect final calculations.
In contrast, using the mentioned numerical XPath functions or the combination of format() and number() XPath functions together with variables enables you to get the desired outcomes as they address both aspects.
For practical demonstrations of these examples, review our test template, which showcases the issues and the proposed solution.
Calculations with formatted numbers.docx