Calculating with Numeric CSV Fields
Arithmetic Calculations with Numeric Fields
Reading numeric field values
The CSVfox software will extract the decimal numeric value of a CSV field when the reference to this field is surrounded by round brackets. This will look likeSee Reading and Formatting Numeric Fields for more information how to extract the numeric field values with using parentheses.
Using Arithmetics
Identifying an arithmetic formula
Any formula expression will, like a single numeric field, be surrounded by round brackets as a whole. There is no need to use parentheses for each CSV field inside a formula. However, using parentheses around a field may make it better readable when you also need to specify its number format.A formula may consist solely of data fields, decimal numbers, operators and brackets.
Decimal numbers must be written in local format (decimal separator, optionally thousands separator). This might require e.g. a decimal comma in USA, but a decimal dot in Germany. Any negative sign must be placed immediately before the number.
- ([MyField]*3)
- Multiplies the numeric content of [MyField] with 3.
- ([MyField].,4*3) or (([MyField].,4)*3)
- Multiplies the numeric content of [MyField] with 3.
In this case, [MyField] contains the value in a foreign format with dot as thousands and comma as decimal separator. And after read, the value must be rounded to four decimal places before using it for calculation. - ([NetPrice]*[TaxFactor])
- Multiplies the numeric content of [NetPrice] with the numeric content of [TaxFactor].
The following rules apply for each arithmetic formula:
- In general, the formula will be evaluated from left to right
- Bracket calculation from inner to outer
- Basic algebraic rules (brackets before exponent/root before multiplication/division before addition/substraction).
- Numbers are calculated up to max. 25-27 significant places, see Software Limitations.
Supported Arithmetic Operators
There are three types of arithmetical operators:O: Two-sided operators between two operands, like [a]+[b] ,
P: Prefix before the operand, like #[a] ,
S: Suffix past the operand, like [b]% .
The following arithmetic operators are understood:
| Symbol | Type | Description | Example |
|---|---|---|---|
| + | O | Addition of two field values and/or numeric values | |
| - | O | Subtraction of two field values and/or numeric values | |
| P | Negative Value, i.e. the result of 0 minus Value | ||
| * | O | Multiplication of two field values and/or numeric values | |
| / | O | Float Division (mathematical division) | |
| : | O | Integral Division (division with remainder); has a whole number result and will discard the remainder |
|
| % | O | Modulo (remainder of integral division) | |
| S | Percent Sign | ||
| ^ | O | nth Exponent | |
| S | Exponent 2 | ||
| r | O | nth Root | |
| P | Square Root | ||
| # | P | Absolute value (positive values: value; negative values: 0-value) | |
| ! | S | Factorial value | |
| @ | O | Round to the given resolution; allows "Rappenrundung" |
- |
| S | Round to whole number | - |
|
| > | O | Max (Greatest of two or more values) | |
| < | O | Min (Lowest of two or more values) |
Remarks regarding some operators
@ Round to given resolutionThis is suitable for rounding to certain thresholds.
If you like to round to the next 10-cent amount, you can use
In some countries, monetary amounts are rounded toward some thresholds. In Switzerland, "Rappenrundung" (centime rounding to the nearest 5 centimes) is common. You can achieve it with a formula like this:
If you use the rounding operator as a suffix, like in
% as a Percent Sign
This allows the direct use of a percentage value in a formula. It has the following special meanings:
In all other cases, a percentage value
<, > as Bigger and Lesser
These are not exactly arithmetic operators.
They find the maximum or minimum, i.e. the greatest resp. the lowest of two or more values.
So,
You can also do things like this:
! as Factorial Value
Factorial computation is only defined for whole numbers. As numbers in this context are generally decimal, they will automatically be rounded to the next whole number before factorization.
So, the value expression of 4.15! will silently be computed as 4!, which is calculated as 1*2*3*4.
Limitations
The number handling is appropriate for commercial or financial calculations that require large numbers of significant and decimal places, and and tolerate no round-off errors. However, when using very large numbers in exponential or factorial calculations, limits may be reached and an overflow error occurs. See here: Software Limitations.
some more examples...
Printing Formatted Values
Like single numeric fields, the numeric result of a formula calculation can also be formatted for output (into a CSV field).If no explicit formatting is defined, number formatting automatically takes place according to the current %locale defaults.
Otherwise, please see Reading and Formatting Numeric Fields on how to define explicit formatting.