CSVfox
Leverage Your Data.
 English

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 like ([FieldName]) or ([FieldName].,3).
See 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 -set[GrossPrice]=([NetPrice]+[Tax]) -set[TheAnswer]=(40+2)
- O Subtraction of two field values and/or numeric values -set[NetPrice]=([GrossPrice]-[Tax])
P Negative Value, i.e. the result of 0 minus Value -set[MinusPrice]=(-[GrossPrice])
* O Multiplication of two field values and/or numeric values -set[GrossPrice]=([NetPrice]*[TaxFactor]) -set[YearlyValue]=([MonthlyValue]*12)
/ O Float Division (mathematical division) -set[MonthlyValue]=([YearlyValue]/12)
: O Integral Division (division with remainder);
has a whole number result and will discard the remainder
-set[Theirs]="Each kid will get ([Apples]:[Kids]) apples"
% O Modulo (remainder of integral division) -set[Mine]="And I will keep ([Apples]%[Kids]) apples for myself"
S Percent Sign -set[NewPrice]=([OldPrice]+10%)
^ O nth Exponent -set[Expo3Field]=([BaseField]^3)
S Exponent 2 -set[ExpoField]=([BaseField]^)
r O nth Root -set[Root3Field]=(3r[BaseField])
P Square Root -set[SquareRootField]=(r[BaseField])
# P Absolute value (positive values: value; negative values: 0-value) -set[HeightDifference]=(#([AHeight]-[BHeight]))
! S Factorial value -set[Permutations]=([Cards]!)
@ O Round to the given resolution;
allows "Rappenrundung"
-set[Whole-Number-Value]=([OddValue]@1) -set[SwissPrice]=(RawPrice@0.05)
S Round to whole number -set[Whole-Number-Value]=([OddValue]@)
> O Max (Greatest of two or more values) -set[MaxPoints]=([MyPoints]>[YourPoints])
< O Min (Lowest of two or more values) -set[Cheapest]=([ApplesPrice]<[PearsPrice]<[BananasPrice])

Remarks regarding some operators

@ Round to given resolution
This is suitable for rounding to certain thresholds.
If you like to round to the next 10-cent amount, you can use ([Field]@0.10).
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: ([Amount]@0.05) .
If you use the rounding operator as a suffix, like in ([Amount]@) , this will round to the next whole number.

% as a Percent Sign
This allows the direct use of a percentage value in a formula. It has the following special meanings:
([BaseField]+3%) is equivalent to and returns the value of ([BaseField]*1.03).
([BaseField]-3%) is equivalent to and returns the value of ([BaseField]*0.97).
([BaseField]*3%) is equivalent to and returns the value of ([BaseField]*0.03).
In all other cases, a percentage value n% is identical to (n*0.01) .

<, > as Bigger and Lesser
These are not exactly arithmetic operators.
They find the greatest resp. the lowest of two or more values.
You can also do things like this: [a]<[b]>[c].  This will get the lower value of [a] and [b], and will compare the result to [c], returning the greater value of both (evaluation goes from left to right).

! 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.

 
Under construction, coming soon