ABC Analyzer supports all standard formulas known from other spreadsheet tools, eg. Excel. Use the formulas when you want to add extra columns to your data set.
Learn How to add columns to your data set
Often used formulas are marked with dark orange
|
Function name |
Description and example |
|
Round()
|
Rounds to nearest integer. Usage: Round(x). Example: Round(-1.6) = -2.
|
|
ColumnSum() |
Sum of column values. Usage: ColumnSum([MyColumn]) |
|
ColumnCount() |
Number of values in a column. Usage: ColumnCount([MyColumn]). |
|
ColumnAverage() |
Average of column values. Usage: ColumnAverage([MyColumn]). |
|
ColumnMaximum() |
Maximum of column values. Usage: ColumnMaximum([MyColumn]). |
|
ColumnMinimum() |
Minimum of column values. Usage: ColumnMinimum([MyColumn]). |
|
AND() |
Logical AND. Usage: AND(x, y). Examples: AND(1=1, 1>2) = 0 (false), AND(1=1, 1<2) = 1 (true). |
|
OR() |
Logical OR. Usage: OR(x, y). Examples: OR(1=4, 1>2) = 0 (false), OR(1=4, 1 <2) = 1 (true). |
|
ColumnCountIf() |
Number of values in column satisfying condition. Usage: ColumnCountIf([Mycolumn], condition). Example: ColumnCountIf([MyColumn], ">0") = value. |
|
ColumnSumIf() |
Sum of values in column satisfying condition. Usage: ColumnSumIf([ Mycolumn], condition). Example: ColumnSumIf([MyColumn], ">2") = value. |
|
CorCoef() |
Correlation coefficient. Usage: CorCoef([Mycolumn1], [Mycolumn2])). Example: CorCoef([MyColumn1], [MyColumn2]) = value. |
|
ColumnStdDev() |
Column standard deviation. Usage: ColumnStdDev([MyColumn]). Example: ColumnStdDev([MyColumn]) = value. |
|
Maximum() |
Maximum of values. Usage: Maximum(value1, value2, ...). Example: Maximum(1, 3, 7, -5, 2) = 7. |
|
Minimum() |
Minimum of values. Usage: Minimum(value1, value2, ...). Example: Minimum(1, 3, 7, -5, 2) = -5.
|
|
Average() |
Average of values. Usage: Average(value1, value2, ...). Example: Average(1, 3, 7, -5, 2) = 1.6. |
|
CountIf()
|
Conditional count. Usage: CountIf(value1, value2, ..., condition). Example: CountIf(1, 2, 3, 4, 5, ">3") = 2. |
|
SumIf() |
Conditional sum. Usage: SumIf(value1, value2, ..., condition). Example: SumIf(1, 2, 3, 4, 5, "<4") = 6. |
|
StdDev() |
Standard deviation. Usage: StdDev(value1, value2, ...). Example: tDev(1, 3, 7, -5, 2) = 4.34. |
|
Days() |
Days since 1900-01-01 to a given date + 1. Usage: Days(x). Example: Days("1900-01-02") = 2. |
|
Now() |
Days since 1900-01-01 till today + 1. Usage: Now(). Example: Now()= 41031 |
|
ImportDate() |
Days between 1900-01-01 + 1 and last import date. Example: ImportDate() = 40210 |
|
FileDate() |
Days between 1900-01-01 + 1 and last file modification. Example: FileDate() = 40209 |
|
Sqrt() |
Square root. Usage: Sqrt(x). Example: Sqrt(2) = 1.4142. |
|
Abs() |
Absolute value. Usage: Abs(x). Example: Abs(-6.4) = 6.4. |
|
Ceil() |
Ceiling function. Usage: Ceil(x). Examples: Ceil(3.1) = 4, Ceil(-3.1) = 3. |
|
Floor() |
Floor function. Usage: Floor(x). Examples: Floor(3.1) = 3, Floor(-3.1) = 4. |
|
If() |
If structure. Usage: If(condition, value _if_true, value_if_false). Example: If(2<1, [Order lines], [Revenue]) = [Revenue]. |
|
Exp() |
Exponential function. Usage: Exp(x). Example: Exp(2.4) = 11.0232. |
|
Ln() |
Natural logarithm. Usage: Ln(x). Example: Ln(11) = 2.3979. |
|
Log() |
Base-10 logarithm. Usage: Log(x). Example: Log(1000) = 3.0. |
|
LogN() |
Base-N logarithm. Usage: LogN(N, x) where N is the base. Example: LogN(2, 32) = 5.0. |
|
Mod() |
Modulo operator. Usage: Mod(a, N) where N is the modulus. Example: Mod(17, 5) = 2. |
|
Pow() |
Power function. Usage: Pow(b, p) where b is the base, p the power. Example: Pow (1.5, 3) = 3.375. |
|
Rnd() |
Random number between 0 and 1 Usage: Rnd(x). |
|
Sign() |
Sign function. Usage: Sign(x). Examples: Sign(3.2) = 1, Sign(-3.2) = -1, Sign(0) = 0. |
|
Sqr() |
Square function. Usage: Sqr(x). Example: Sqr(-1.5) = 2.25. |
|
Trunc() |
Discards the fractional part of a number. Usage: Trunc(x). Example: Trunc(-6.4) = -6, Trunc(6.4) = 6. |