Numero > Functions |
Numero is an Office add-in that brings calculations and chemistry to Word, PowerPoint and Outlook. This allows you to keep your train of thought and improve productivity when writing technical reports, emails and presentations. You no longer need to switch from Word, Outlook or PowerPoint to Excel for a simple calculation, or open a browser to access currency conversion rates. Use the Ctrl+3 shortcut to evaluate formulas quickly and the Formula Bar or Numero ribbon for powerful calculations, including statistics. Capture notes in a task pane using Plaintext. Access materials and other information using the Data Services feature.
The following mathematical constants are available:
Constant | Description | Value | Unit |
Pi() | The ratio of circle's circumference to it diameter | 3.14159265358979 | |
e() | Base of natural logarithms | 2.71828182845905 | |
RGAS() | Ideal gas constant | 8.3144598 | J/mol.K |
mu0() | Permeability of vacuum | 1.2566370614E-06 | N/A2 |
c() | Speed of light in vacuum | 2.99792458E+08 | m/s |
NA() | Avogadro constant | 6.0221410000E+23 | 1/mol |
e0() | Elementary charge | 1.6021765650E-19 | Coulomb |
kB() | Boltzmann constant | 1.3806488589E-23 | J/k |
eps0() | Permittivity of vacuum | 8.8541878170E-12 | A2.s4/kg.m3 |
DESCRIPTION
Round the number by the specified number of digits
SYNTAX
ROUND(number;digit)
EXAMPLE
Formula Description Result
=ROUND(3.141528;3) Round 3.141528 to 3 digits 3.142
DESCRIPTION
Round the number up by the specified number of digits
SYNTAX
ROUNDUP(number;digit)
EXAMPLE
Formula Description Result
=ROUNDUP(3.23412;3) Round up 3.23412 to 3 digits 3.235
DESCRIPTION
Round the number down by the specified number of digits
SYNTAX
ROUNDDOWN(number;digit)
EXAMPLE
Formula Description Result
=ROUNDDOWN(3.23412;3) Round down 3.23412 to 3 digits 3.234
DESCRIPTION
Rounds a number down to the nearest integer
SYNTAX
INT(number)
EXAMPLE
Formula Description Result
=INT(3.8) Rounds 3.8 down to the nearest integer 3
DESCRIPTION
Rounds the number with its sign swapped
SYNTAX
NEG(number)
EXAMPLE
Formula Description Result
=NEG(-3.8) Changes the sign of -3.8 3.8
DESCRIPTION
Returns the absolute value from the input
SYNTAX
ABS(number)
EXAMPLE
Formula Description Result
=ABS(-3.8) Absolute value of -3.8 3.8
DESCRIPTION
Truncates the input by the specified number of digits
SYNTAX
TRUNC(number;digits)
EXAMPLE
Formula Description Result
=TRUNC(3.84367;3) Truncate 3.84367 to 3 digits 3.843
DESCRIPTION
Calculates the exponent of a number
SYNTAX
EXP(number)
EXAMPLE
Formula Description Result
=EXP(2.5) Exponent of 2.5 12.182494
Calculates the logarithm with base 10 for a number
SYNTAX
LOG(number)
EXAMPLE
Formula Description Result
=LOG(100) Logarithm to the base 10 of 100 2
Calculates the natural logarithm of a number
SYNTAX
LN(number)
EXAMPLE
Formula Description Result
=LN(7.389056) Natural logarithm of 7.389056 2
Calculates the logarithm with base 2 for a number
SYNTAX
LOG2(number)
EXAMPLE
Formula Description Result
=LOG2(16) Natural logarithm of 16 4
Calculates the logarithm of a specified base for a number
SYNTAX
LOGB(number;base)
EXAMPLE
Formula Description Result
=LOGB(81;3) Logarithm to the base of 3 for 81 4
Calculates the square root of a number
SYNTAX
SQRT(number)
EXAMPLE
Formula Description Result
=SQRT(81) Square root of 81 9
Calculates the cube root of a number
SYNTAX
CBRT(number)
EXAMPLE
Formula Description Result
=CBRT(125) Cube root of 125 5
Calculates the square root of a number×Pi
SYNTAX
SQRTPI(number)
EXAMPLE
Formula Description Result
=SQRTPI(2) Square root of 2×Pi 2.506628
Calculates the nth root of a number
SYNTAX
ROOT(number;root)
EXAMPLE
Formula Description Result
=ROOT(7776;5) 5th root of 7776 6
Calculates the product of all numbers in the selected data set
SYNTAX
PRODUCT(array)
EXAMPLE
Formula Description Result
=PRODUCT(a) Product of the array of numbers 30
Array (a) |
3 |
5 |
2 |
Returns the factorial of the number
SYNTAX
FACT(number)
EXAMPLE
Formula Description Result
=FACT(5) Factorial of 5 (5!) 30
Returns randomized number between 0 and 1
SYNTAX
RAND()
EXAMPLE
Formula Description Result
=RAND() Returns a random number between 0 and 1 0.71804
Returns randomized number between two defined numbers
SYNTAX
RANDB(low;high)
EXAMPLE
Formula Description Result
=RANDB(1;100) Returns a random number between 1 and 100 56
Returns the sine of a number in radians
SYNTAX
SIN(number)
EXAMPLE
Formula Description Result
=SIN(10) Sine of 10 -0.544021
Returns the sine of a number in degrees
SYNTAX
SIND(number)
EXAMPLE
Formula Description Result
=SIND(10) Sine of 10 0.173648
Returns the cosine of a number in radians
SYNTAX
COS(number)
EXAMPLE
Formula Description Result
=COS(20) Cosine of 20 0.408082
Returns the cosine of a number in radians
SYNTAX
COSD(number)
EXAMPLE
Formula Description Result
=COSD(20) Cosine of 20 0.939693
Returns the tangent of a number in radians
SYNTAX
TAN(number)
EXAMPLE
Formula Description Result
=TAN(15) Tangent of 15 -0.855993
Returns the tangent of a number in degrees
SYNTAX
TAND(number)
EXAMPLE
Formula Description Result
=TAND(15) Tangent of 15 0.267949
Returns the cosecant of a number in radians
SYNTAX
COSEC(number)
EXAMPLE
Formula Description Result
=COSEC(10) Cosecant of 10 -1.838164
Returns the cosecant of a number in degrees
SYNTAX
COSEC(number)
EXAMPLE
Formula Description Result
=COSECD(10) Cosecant of 10 5.75877
Returns the secant of a number in radians
SYNTAX
SEC(number)
EXAMPLE
Formula Description Result
=SEC(15) Secant of 15 -1.31633
Returns the secant of a number in degrees
SYNTAX
SEC(number)
EXAMPLE
Formula Description Result
=SEC(15) Secant of 15 1.035276
Returns the cotangent of a number in radians
SYNTAX
COTAN(number)
EXAMPLE
Formula Description Result
=COTAN(10) Cotangent of 10 1.542351
Returns the cotangent of a number in degrees
SYNTAX
COTAND(number)
EXAMPLE
Formula Description Result
=COTAND(10) Cotangent of 10 5.671282
Returns the inverse sine of a number where the number is between -1 and+1
SYNTAX
ARCSIN(number)
EXAMPLE
Formula Description Result
=ARCSIN(0.98) Arcsin of 0.98 1.370461
Returns the inverse sine of a number where the number is between -1 and+1
SYNTAX
ARCSIND(number)
EXAMPLE
Formula Description Result
=ARCSIND(0.98) Arcsin of 0.98 78.521659
Returns the inverse cosine of a number where the number is between -1 and+1
SYNTAX
ARCCOS(number)
EXAMPLE
Formula Description Result
=ARCCOS(0.98) Arccos of 0.98 0.200335
Returns the inverse cosine of a number where the number is between -1 and+1
SYNTAX
ARCCOSD(number)
EXAMPLE
Formula Description Result
=ARCCOSD(0.98) Arccosd of 0.98 11.478341
Returns the inverse tangent of a number
SYNTAX
ARCTAN(number)
EXAMPLE
Formula Description Result
=ARCTAN(1.3) Arctan of 1.3 0.915101
Returns the inverse tangent of a number
SYNTAX
ARCTAND(number)
EXAMPLE
Formula Description Result
=ARCTAND(1.3) Arctand of 1.3 52.431408
Converts the input from radians into degrees
SYNTAX
DEGREES(number)
EXAMPLE
Formula Description Result
=DEGREES(1.5708) Convert 1.5708 radians into degrees 90
Converts the input from radians into degrees
SYNTAX
RADIANS(number)
EXAMPLE
Formula Description Result
=RADIANS(45) Convert 45 degrees into radians 0.785398
If the condition is met, then return the value specified in the true, if it is false, it returns the value specified in the false section.
SYNTAX
IF(condition;true;false)
EXAMPLE
Formula Description Result
=IF(4>0;Y;N) If 4>0 is true, then give Y, otherwise give N Y
If the condition is met, count the number of cells that it is met.
SYNTAX
COUNTIF(a;condition)
EXAMPLE
Formula Description Result
=COUNTIF(a;a>2) Count the number of cells in the array that has a value more than 2 4
1 | 3 | 5 |
2 | 4 | 6 |
If the condition is met, sum the numbers inside the cell where the condition is met.
SYNTAX
SUMIF(a;condition)
EXAMPLE
Formula Description Result
=SUMIF(a;a>2) Sum the numbers that have values more than 2 18
1 | 3 | 5 |
2 | 4 | 6 |
Returns the sum of all the numbers in the dataset
SYNTAX
SUM(array)
EXAMPLE
Formula Description Result
=SUM(a) Sum of all the numbers 15
3 |
5 |
7 |
Counts all the numbers in the dataset
SYNTAX
COUNT(array)
EXAMPLE
Formula Description Result
=COUNT(a) Count all numbers in the array 3
2 |
4 |
6 |
Counts all the numbers in the dataset
SYNTAX
AVERAGE(array)
EXAMPLE
Formula Description Result
=COUNT(a) Count all numbers in the array 3
2 |
4 |
6 |
Returns the minimum of the dataset
SYNTAX
MINIMUM(array)
EXAMPLE
Formula Description Result
=MIN(a) Returns the minimum value in the array 3
4 |
3 |
7 |
Returns the minimum of the dataset
SYNTAX
MAX(array)
EXAMPLE
Formula Description Result
=MAX(a) Returns the minimum value in the array 7
4 |
3 |
7 |
Calculates each value as a cumulative sum to date
SYNTAX
SUML(array)
EXAMPLE
Formula Description
=SUML(a) Returns the cumulative sum to date
Values | Result |
4 | 4 |
3 | 7 |
7 | 14 |
Calculates each value as a cumulative count to date
SYNTAX
COUNTL(array)
EXAMPLE
Formula Description
=COUNTL(a) Returns the cumulative sum to date
Values | Result |
4 | 1 |
3 | 2 |
7 | 3 |
Calculates each value as a cumulative average value to date
SYNTAX
AVGL(array)
EXAMPLE
Formula Description
=AVGL(a) Returns the cumulative average to date
Values | Result |
4 | 4 |
3 | 3.5 |
7 | 4.667 |
Calculates each value as a cumulative minimum value to date
SYNTAX
MINL(array)
EXAMPLE
Formula Description
=MINL(a) Returns the cumulative minimum value to date
Values | Result |
4 | 4 |
3 | 3 |
7 | 3 |
Calculates each value as a cumulative maximum value to date
SYNTAX
MAXL(array)
EXAMPLE
Formula Description
=MAXL(a) Returns the cumulative minimum value to date
Values | Result |
4 | 4 |
3 | 4 |
7 | 7 |
Calculates each values as the difference to the previous value in the series
SYNTAX
DIFF(array)
EXAMPLE
Formula Description
=DIFF(a) Returns the difference to the previous value in the series
Values | Result |
4 | 4 |
3 | -1 |
7 | 4 |
Calculates each values as the difference to the next value in the series
SYNTAX
DIFFN(array)
EXAMPLE
Formula Description
=DIFFN(a) Returns the difference to the next value in the series
Values | Result |
4 | 1 |
3 | -4 |
7 | -2 |
9 | -2 |
11 | 11 |
Calculates each values as the difference to the first value in the series
SYNTAX
DIFFF(array)
EXAMPLE
Formula Description
=DIFFF(a) Returns the difference to the first value in the series
Values | Result |
4 | 0 |
3 | -1 |
7 | 3 |
9 | 5 |
11 | 7 |
Calculates each values as the difference to the last value in the series
SYNTAX
DIFFL(array)
EXAMPLE
Formula Description
=DIFFL(a) Returns the difference to the last value in the series
Values | Result |
4 | -7 |
3 | -8 |
7 | -4 |
9 | -2 |
11 | 0 |
Calculates each value as the difference to the total
SYNTAX
SUMD(array)
NOTE:
EXAMPLE
Formula Description
=SUMD(a) Returns each value as a difference of the total
Values | Result |
4 | 10 |
3 | 11 |
7 | 7 |
Calculates each value as the difference to the average
SYNTAX
AVGD(array)
NOTE:
EXAMPLE
Formula Description
=AVGD(a) Returns each value as a difference of the average
Values | Result |
4 | -0.6667 |
3 | -1.6667 |
7 | 2.3333 |
Calculates each value as the difference to the minimum value
SYNTAX
MIND(array)
NOTE:
EXAMPLE
Formula Description
=MIND(a) Returns each value as a difference of the minimum value
Values | Result |
4 | 1 |
3 | 0 |
7 | 4 |
Calculates each value as the difference to the maximum value
SYNTAX
MAXD(array)
NOTE:
EXAMPLE
Formula Description
=MAXD(a) Returns each value as a difference of the maximum value
Values | Result |
4 | -3 |
3 | -4 |
7 | 0 |
Calculates each value as a fraction of the Total
SYNTAX
SUMX(array)
NOTE:
EXAMPLE
Formula Description
=SUMX(a) Calculates each value as a fraction of the Total
Values | Result |
10 | 0.2 |
25 | 0.5 |
15 | 0.3 |
Calculates each value as a percentage of the Total
SYNTAX
SUMP(array)
NOTE:
EXAMPLE
Formula Description
=SUMP(a) Calculates each value as a percentage of the Total
Values | Result |
10 | 20 |
25 | 50 |
15 | 30 |
Calculates each value as the cumulative total to date as a fraction of the Total
SYNTAX
SUMLX(array)
NOTE:
EXAMPLE
Formula Description
=SUMLX(a) Calculates each value as the cumulative total to date as a fraction of the Total
Values | Result |
10 | 0.2 |
25 | 0.7 |
15 | 1 |
Calculates each value as the cumulative total to date as a percentage of the Total
SYNTAX
SUMLP(array)
NOTE:
EXAMPLE
Formula Description
=SUMLP(a) Calculates each value as the cumulative total to date as a percentage of the Total
Values | Result |
10 | 20 |
25 | 70 |
15 | 100 |
Calculates each value as the difference to the Total as a fraction of the Total
SYNTAX
SUMDX(array)
NOTE:
EXAMPLE
Formula Description
=SUMDX(a) Calculates each value as the difference to the Total as a fraction of the Total
Values | Result |
10 | 0.8 |
25 | 0.5 |
15 | 0.7 |
Calculates each value as the difference to the Total as a percentage of the Total
SYNTAX
SUMDP(array)
NOTE:
EXAMPLE
Formula Description
=SUMDP(a) Calculates each value as the difference to the Total as a percentage of the Total
Values | Result |
10 | 80 |
25 | 50 |
15 | 70 |
Calculates each value as a the difference to previous value as a fraction of the Total
SYNTAX
DIFFSUMX(array)
NOTE:
EXAMPLE
Formula Description
=SUMDP(a) Subtracts each value from the previous value and divides it by the Total. The first value provided is subtracted from 0.
Values | Result |
10 | 0.222 |
15 | 0.111 |
20 | 0.111 |
Calculates each value as the difference to previous value as a percentage of the Total
SYNTAX
DIFFSUMP(array)
NOTE:
EXAMPLE
Formula Description
=DIFFSUMP(a) Subtracts each value from the previous value and divides it by the Total. This result is then converted into a percentage. The first value provided is subtracted from 0.
Values | Result |
10 | 22.222 |
15 | 11.111 |
20 | 11.111 |
Returns the average (arithmetic mean) of an array of numbers
SYNTAX
MEAN(array)
NOTE:
EXAMPLE
Formula Description Result
= MEAN(a) Returns the average (arithmetic mean) of an array of numbers 15
Values |
10 |
15 |
20 |
Returns the number in the middle of an ordered data set
SYNTAX
MEDIAN(array)
EXAMPLE
Formula Description Result
= MEDIAN(a) Returns the number in the middle of an ordered array 20
Values |
10 |
28 |
29 |
15 |
20 |
Returns the lowest most frequently occurring number in an array
SYNTAX
MODESNGL(array)
EXAMPLE
Formula Description Result
= MODESNGL(a) Returns the lowest most frequently occurring number in an array 2
Values |
2 |
1 |
2 |
3 |
2 |
Returns an array of the most frequently occurring numbers in a set of numbers
SYNTAX
MODEMULT(array)
EXAMPLE
Formula Description Result
= MODE(a) Returns an array of the most frequently occurring numbers in an array 2, 3
Values |
2 |
1 |
2 |
3 |
3 |
Returns the Modified mean, the mean without MIN and MAX values
SYNTAX
MODMEAN(array)
EXAMPLE
Formula Description Result
= MODMEAN(a) Returns the modified mean of 2, 3 and 4 3
Values |
1 |
2 |
3 |
4 |
10 |
Returns a trimmed mean, a mean without the largest and smallest n values
SYNTAX
TRIMNMEAN(array;n-values)
EXAMPLE
Formula Description Result
= TRIMNMEAN(a;1) Returns the mean of 2, 3 and 4 3
Values |
1 |
2 |
3 |
4 |
10 |
Returns a trimmed mean, a mean without the largest and smallest k% of values
SYNTAX
TRIMPMEAN(array;k-percent)
EXAMPLE
Formula Description Result
= TRIMPMEAN(a;20) Returns the mean of 2, 3 and 4 3
Values |
1 |
2 |
3 |
4 |
10 |
Returns a Winsorized mean, a mean with the smallest and largest k% of values replaced with new-min, new-max
SYNTAX
WINSMEAN(array;k-percent)
EXAMPLE
Formula Description Result
= WINSMEAN(a;20) Returns the mean of 2, 2, 3, 4 and 4 3
Values |
1 |
2 |
3 |
4 |
10 |
Interquartile mean (or midmean), a mean based on the truncated mean of the interquartile range
SYNTAX
IQM(array)
EXAMPLE
Formula Description Result
= IQM(array) Returns the mean of 2, 2, 3, 4 and 4 4.5
Values |
15 |
2 |
3 |
4 |
5 |
6 |
7 |
100 |
This is the generalized mean or power mean.
SYNTAX
GENMEAN(array;order)
Note
If k is a positive or negative number, and a is an array of positive real numbers, then the generalized mean will take the form of
EXAMPLE
Formula Description Result
= GENMEAN(a;2) Generalized mean of 1, 2, 3, 4 2.738613
Values |
1 |
2 |
3 |
4 |
This is the quadratic mean or root mean square.
SYNTAX
QUADMEAN(array)
Note
Formula
EXAMPLE
Formula Description Result
= QUADMEAN(a) Quadratic mean of 1, 2, 3, 4 2.738613
Values |
1 |
2 |
3 |
4 |
This is the cubic mean.
SYNTAX
CUBMEAN(array)
Note
Formula
EXAMPLE
Formula Description Result
= CUBMEAN(a) Cubic mean of 1, 2, 3, 4 2.924018
Values |
1 |
2 |
3 |
4 |
This is the geometric mean.
SYNTAX
GEOMEAN(array)
Note
Formula
EXAMPLE
Formula Description Result
= GEOMEAN(a) Geometric mean of 1, 2, 3, 4 2.213364
Values |
1 |
2 |
3 |
4 |
This is the harmonic mean.
SYNTAX
HARMEAN(array)
Note
Formula
EXAMPLE
Formula Description Result
= HARMEAN(a) Harmonic mean of 1, 2, 3, 4 1.920
Values |
1 |
2 |
3 |
4 |
This returns a random number between 0 and 1.
SYNTAX
RAND()
EXAMPLE
Formula Description Result
= RAND() Return a random number between 0 and 1 0.294385
This returns a random number between two specified numbers.
SYNTAX
RANDB(low; high)
EXAMPLE
Formula Description Result
= RANDB(1; 20) Return a random whole number between the values specified 7
This returns an array with the duplicates removed
SYNTAX
REMDUP(a)
EXAMPLE
Formula Description Result
= REMDUP(a) Return the array with the duplicates removed {1;2;3}
Values |
1 |
2 |
2 |
3 |
This returns an array with a specific number removed from all the values
SYNTAX
REMX(a)
EXAMPLE
Formula Description Result
= REMX(a;2) Return the array with 2 removed {1;3}
Values |
1 |
2 |
2 |
3 |
This sorts the numbers in the array from smallest to largest
SYNTAX
SORT(a)
EXAMPLE
Formula Description Result
= SORT(a) Sorts the numbers from smallest to largest {1;2;3;4}
Values |
4 |
2 |
1 |
3 |
This sorts the numbers in the array from largest to smallest
SYNTAX
SORTD(a)
EXAMPLE
Formula Description Result
= SORTD(a) Sorts the numbers from largest to smallest {4;3;2;1}
Values |
4 |
2 |
1 |
3 |
This reverses the order of an array of numbers
SYNTAX
REVERSE(a)
EXAMPLE
Formula Description Result
= REVERSE(a) Reverses the order of an array of numbers {3;1;2;4}
Values |
4 |
2 |
1 |
3 |
This randomizes the order of an array of numbers
SYNTAX
RANDOMIZE(a)
EXAMPLE
Formula Description Result
= RANDOMIZE(a) Randomizes an array of numbers {2;4;3;1}
Values |
4 |
2 |
1 |
3 |
This switches the order of values in a series from being 'read in columns' to 'read in rows'
SYNTAX
SWITCH(a)
EXAMPLE
Formula Description Result
= SWITCH(a) Switch the following array {1:4:7;2:5:8;3:6:9}
1 | 4 | 7 |
2 | 5 | 8 |
3 | 6 | 9 |
This finds the k-th value in an array
SYNTAX
FIND(a;k)
EXAMPLE
Formula Description Result
= FIND(a;3) Find the 3rd number in the following array 18
10 |
14 |
18 |
22 |
This finds the k-th smallest value in an array
SYNTAX
SMALL(a;k)
EXAMPLE
Formula Description Result
= SMALL (a;2) Find the 2nd smallest number in the following array 14
10 |
14 |
18 |
22 |
This finds the k-th largest value in an array
SYNTAX
LARGE(a;k)
EXAMPLE
Formula Description Result
= LARGE (a;2) Find the 2nd largest number in the following array 18
10 |
14 |
18 |
22 |
This returns the rank of each number in an array. If more than one numbers has the same rank, the top one is taken.
SYNTAX
RANK(a;order)
EXAMPLE
Formula Description Result
= RANK (a;1) Ranks each number in an array {2;3;1;3}
0 |
3 |
-5 |
3 |
This returns the rank of each number in an array. If more than one numbers has the same rank, the average is taken.
SYNTAX
RANKAVG(a;order)
EXAMPLE
Formula Description Result
= RANKAVG (a;1) Ranks each number in an array {2;3.5;1;3.5}
0 |
3 |
-5 |
3 |
This returns the rank of a number in an array. If more than one number has the same rank, the top one is taken.
SYNTAX
RANKN(a;x;order)
EXAMPLE
Formula Description Result
= RANKN (a;3;0) Ranks each number in an array 1
0 |
3 |
-5 |
3 |
This returns the rank of a number in an array. If more than one number has the same rank, the average is taken.
SYNTAX
RANKNAVG(a;x;order)
EXAMPLE
Formula Description Result
= RANKNAVG (a;3;0) Ranks each number in an array 1.5
0 |
3 |
-5 |
3 |
This removes every n-th point from a data series.
SYNTAX
FILTER(a;n-values)
EXAMPLE
Formula Description Result
= FILTER (a; 2) Remove every second point from an array {0;-5;6}
0 |
3 |
-5 |
3 |
6 |
2 |
This returns a data set trimmed top and bottom by n values.
SYNTAX
TRIMDATN(a;n-values)
EXAMPLE
Formula Description Result
= TRIMDATN (a; 2) Remove the top and bottom two number from the data set {3;2}
0 |
3 |
-5 |
3 |
6 |
2 |
This returns a data set trimmed top and bottom by k percent.
SYNTAX
TRIMDATP(a;k-percent)
EXAMPLE
Formula Description Result
= TRIMDATP (a; 20) Remove the top and bottom twenty percent of numbers from the data set {0;3;3}
0 |
3 |
-5 |
3 |
6 |
This returns a truncated data set using the values in the inter quartile range.
SYNTAX
IQDAT(a)
EXAMPLE
Formula Description Result
= IQDAT (a) Truncate the data set using values in the inter quartile range {3;3;1;4}
0 |
3 |
-5 |
3 |
6 |
1 |
4 |
10 |
This returns a data set with the top and bottom k% of values replaced with a new-min and a new-max
SYNTAX
WINSOR(a; k-percent)
EXAMPLE
Formula Description Result
= WINSOR (a;k-percent) Returns a data set with the top and bottom k% of values {1;3;1;3;4;1;4;4}
replaced with a new-max and a new min
0 |
3 |
-5 |
3 |
6 |
1 |
4 |
10 |
This normalizes the series by dividing each data point by the range
SYNTAX
NORMR(a)
EXAMPLE
Formula Description Result
= NORMR(a) Divides each data point by the range {0 ; 0.214286 ; -0.357143 ; 0.642857 ; 0.428571}
0 |
3 |
-5 |
9 |
6 |
This normalizes the series to lie between 0 and 1
SYNTAX
NORM(a)
EXAMPLE
Formula Description Result
= NORM(a) Divides each data point to lie between 0 and 1 {0 ; 0.214286 ; -0.357143 ; 0.642857 ; 0.428571}
0 |
3 |
-5 |
9 |
6 |
This normalizes the series to lie between 0 and 1
SYNTAX
NORML(a;actual_min;actual_max)
EXAMPLE
Formula Description Result
= NORML(a;0;0.775) Divides each data point to lie between 0 and 1 {0 : 0.129032 : 0.387097 : 0.645161 1}
0 |
0.1 |
0.3 |
0.5 |
0.775 |
This normalizes data to user-defined new min and max when data contains desired min and max values
SYNTAX
NORMA(a;new_min;new_max)
EXAMPLE
Formula Description Result
= NORMA(a;-1;2) Normalizes the data to user-defined new minimum (-1) and new maximum (2) {-1 : -0.612903 : 0.16129 : 0.935484 : 2}
0 |
0.1 |
0.3 |
0.5 |
0.775 |
This normalizes data from original scale (old_min to old_max) to a new scale (new_min to new-max)
SYNTAX
NORMB(a; old_min; old_max; new_min; new_max)
EXAMPLE
Formula Description Result
= NORMB(a;1;10) Normalizes the data from the old scale to a new user-defined scale of 1 to 10 {1 2.16129 4.483871 6.806452 10}
0 |
0.1 |
0.3 |
0.5 |
0.775 |
This returns the range of the series
SYNTAX
RANGE(a)
EXAMPLE
Formula Description Result
= RANGE(a) Returns the range of the array below 14
0 |
3 |
-5 |
9 |
6 |
This returns the midrange of the series
SYNTAX
MIDRANGE(a)
EXAMPLE
Formula Description Result
= MIDRANGE(a) Returns the midrange of the array below 7
0 |
3 |
-5 |
9 |
6 |
This returns the nth quartile of an array
QUARTILE(a;quartile)
EXAMPLE
Formula Description Result
= QUARTILE(a;1) Return the first quartile for the array below -2.5
0 |
3 |
-5 |
9 |
6 |
This returns the first quartile (25th percentile) of the series
SYNTAX
Q1(a)
EXAMPLE
Formula Description Result
= Q1(a) Returns the first quartile for the array below -2.5
0 |
3 |
-5 |
9 |
6 |
This returns the second quartile (50th percentile) of the series
SYNTAX
Q2(a)
EXAMPLE
Formula Description Result
= Q2(a) Returns the second quartile for the array below 3
0 |
3 |
-5 |
9 |
6 |
This returns the third quartile (75th percentile) of the series
SYNTAX
Q3(a)
EXAMPLE
Formula Description Result
= Q3(a) Returns the third quartile for the array below 7.5
0 |
3 |
-5 |
9 |
6 |
This returns the inter-quartile range (Q3-Q1)
SYNTAX
IQR(a)
EXAMPLE
Formula Description Result
= IQR(a) Returns the inter-quartile for the array below 10
0 |
3 |
-5 |
9 |
6 |
This returns the lower fence value for an array
SYNTAX
FENCEL(a)
EXAMPLE
Formula Description Result
= FENCEL(a) Returns the lower fence value for an array -17.5
0 |
3 |
-5 |
9 |
6 |
This returns the upper fence value for an array
SYNTAX
FENCEU(a)
EXAMPLE
Formula Description Result
= FENCEU(a) Returns the upper fence value for an array 22.5
0 |
3 |
-5 |
9 |
6 |
This returns the outlier(s) in an array
SYNTAX
OUTLIERS(a)
EXAMPLE
Formula Description Result
= OUTLIERS(a) Returns the outliers for an array Empty set
0 |
3 |
-5 |
9 |
6 |
This returns the smallest value in the series that is not an outlier
SYNTAX
BOXMIN(a)
EXAMPLE
Formula Description Result
= BOXMIN(a) Return the smallest value in the following array that is not an outlier -5
0 |
3 |
-5 |
9 |
6 |
This returns the largest value in the series that is not an outlier
BOXMAX(a)
EXAMPLE
Formula Description Result
= BOXMAX(a) Return the smallest value in the following array that is not an outlier 9
0 |
3 |
-5 |
9 |
6 |
This returns the average of the first and third quartiles
MIDHINGE(a)
EXAMPLE
Formula Description Result
= MIDHINGE(a) Return the average of the first and third quartiles 2.5
0 |
3 |
-5 |
9 |
6 |
This returns the average of the first and third quartile and median
TRIMEAN(a)
EXAMPLE
Formula Description Result
= TRIMEAN(a) Return the average of the first and third quartiles and median 2.75
0 |
3 |
-5 |
9 |
6 |
This returns the nth decile of an array
DECILE(a;decile)
EXAMPLE
Formula Description Result
= DECILE(a;1) Return the first decile for the array below 0.3
0 |
3 |
6 |
9 |
12 |
15 |
18 |
21 |
24 |
27 |
This returns the first decile of an array
D1(a)
EXAMPLE
Formula Description Result
= D1(a) Return the first decile for the array below 0.3
0 |
3 |
6 |
9 |
12 |
15 |
18 |
21 |
24 |
27 |
This returns the ninth decile of an array
D9(a)
EXAMPLE
Formula Description Result
= D9(a) Return the ninth decile for the array below 26.7
0 |
3 |
6 |
9 |
12 |
15 |
18 |
21 |
24 |
27 |
This returns the inter-decile range of an array (D9-D1)
IDR(a)
EXAMPLE
Formula Description Result
= IDR(a) Return the inter-decile range for the array below 26.4
0 |
3 |
6 |
9 |
12 |
15 |
18 |
21 |
24 |
27 |
This returns the nth percentile of an array
PERCENTILE(a;percentile)
EXAMPLE
Formula Description Result
= PERCENTILE(a;1) Return the first percentile for the array containing every number between 1-150 1.51
= PERCENTILE(a;89) Return the 89th percentile for the array containing every number between 1-150 134.39
This calculates the variance based on an entire population
SYNTAX
VARP(a)
Note
Formula
EXAMPLE
Formula Description Result
= VARP (a) Calculate the variance of the dataset below 16.9375
0 |
3 |
-5 |
3 |
6 |
1 |
4 |
10 |
This calculates the variance based on a sample
SYNTAX
VARS(a)
Note
Formula
EXAMPLE
Formula Description Result
= VARS (a) Calculate the variance of the dataset below 19.357143
0 |
3 |
-5 |
3 |
6 |
1 |
4 |
10 |
This calculates the standard deviation based on the population
SYNTAX
SDEVP(a)
Note
Formula
EXAMPLE
Formula Description Result
= SDEVP (a) Calculate the standard deviation of the dataset below 4.115519
0 |
3 |
-5 |
3 |
6 |
1 |
4 |
10 |
This calculates the standard deviation based on a sample
SYNTAX
SDEVS(a)
Note
Formula
EXAMPLE
Formula Description Result
= SDEVS (a) Calculate the standard deviation of the dataset below 4.399675
0 |
3 |
-5 |
3 |
6 |
1 |
4 |
10 |
This calculates the sum of absolute values for a sample
SYNTAX
SUMABS(a)
Formula Description Result
= SUMABS (a) Calculate the sum of absolute values standard deviation of the dataset below 4.399675
0 |
3 |
-5 |
3 |
6 |
1 |
4 |
10 |
This calculates the sum of squares for a sample
SYNTAX
SUMSQ(a)
Formula Description Result
= SUMSQ(a) Calculate the sum of squared values 196
0 |
3 |
-5 |
3 |
6 |
1 |
4 |
10 |
This calculates the sum of squares for a sample
SYNTAX
SUMSQ(a)
Formula Description Result
= SUMSQ(a) Calculate the sum of absolute values standard deviation of the dataset below 5.25
0 |
3 |
-5 |
3 |
6 |
1 |
4 |
10 |
This calculates the average absolute deviation for a sample
SYNTAX
AVEDEV(a)
Formula Description Result
= AVEDEV(a) Calculate the average absolute deviation for a sample 3.0625
0 |
3 |
-5 |
3 |
6 |
1 |
4 |
10 |
This calculates the absolute deviation for a sample
SYNTAX
ABSDEV(a)
Formula Description Result
= ABSDEV(a) Calculate the absolute deviation for a sample 24.5
0 |
3 |
-5 |
3 |
6 |
1 |
4 |
10 |
This calculates the median absolute deviation for a sample
SYNTAX
MAD(a)
Formula Description Result
= MAD(a) Calculate the median absolute deviation for a sample 2.5
0 |
3 |
-5 |
3 |
6 |
1 |
4 |
10 |
This returns the range of the series.
SYNTAX
RANGE(array)
Note
Formula: RANGE = Max(a)-Min(a)
EXAMPLE
Formula Description Result
= RANGE(a) Range between 1 and 7 6
Values |
1 |
2 |
4 |
7 |
This returns the midrange of the series.
SYNTAX
MIDRANGE(array)
Note
Formula: MIDRANGE = (Max(a)-Min(a))/2
EXAMPLE
Formula Description Result
= MIDRANGE(a) Midrange between 1 and 7 3
Values |
1 |
2 |
4 |
7 |
This returns the Inter-Quartile Range of the series.
SYNTAX
IQR(array)
Note
Formula: IQR = Q3-Q1
EXAMPLE
Formula Description Result
= IQR(a) IQR between 1 and 7 5
Values |
1 |
2 |
4 |
7 |
This is the standard error of the mean (SEM) for the whole population
SYNTAX
SEMP(array)
EXAMPLE
Formula Description Result
= SEMP(a) Standard error of the mean 1.145644
Values |
1 |
2 |
4 |
7 |
This is the standard error of the mean (SEM) for the sample
SYNTAX
SEMS(array)
EXAMPLE
Formula Description Result
= SEMS(a) Standard error of the mean 1.322876
Values |
1 |
2 |
4 |
7 |
This is the relative standard error of the mean for the whole population
SYNTAX
RSEP(array)
EXAMPLE
Formula Description Result
= RSEP(a) Relative standard error of the mean 32.732684
Values |
1 |
2 |
4 |
7 |
This is the relative standard error of the mean for the sample
SYNTAX
RSEP(array)
EXAMPLE
Formula Description Result
= RSES(a) Relative standard error of the mean 37.796447
Values |
1 |
2 |
4 |
7 |
This is the index of dispersion for the whole population
SYNTAX
DISPINP(array)
EXAMPLE
Formula Description Result
= DISPINP(a) Index of dispersion 1.5
Values |
1 |
2 |
4 |
7 |
This is the index of dispersion for the sample
SYNTAX
DISPINS(array)
EXAMPLE
Formula Description Result
= DISPINS(a) Index of dispersion 2.0
Values |
1 |
2 |
4 |
7 |
This is the coefficient of variation for the population
SYNTAX
CVP(array)
EXAMPLE
Formula Description Result
= CVP(a) Coefficient of variation of the population 0.654654
Values |
1 |
2 |
4 |
7 |
This is the coefficient of variation for the sample
SYNTAX
CVS(array)
EXAMPLE
Formula Description Result
= CVS(a) Coefficient of variation of the sample 0.755929
Values |
1 |
2 |
4 |
7 |
This is the signal-to-noise ratio for the population
SYNTAX
SNRP(array)
EXAMPLE
Formula Description Result
= SNRP(a) Signal-to-noise ratio for the population 1.527525
Values |
1 |
2 |
4 |
7 |
This is the signal-to-noise ratio for the sample
SYNTAX
SNRS(array)
EXAMPLE
Formula Description Result
= SNRS(a) Signal-to-noise ratio for the sample 1.322876
Values |
1 |
2 |
4 |
7 |
This is the Z-score for the population
SYNTAX
ZSCORP(array)
Description
= ZSCORP(a) Return the z-score for the values 1, 2, 4, 7
Values | Result |
1 | -1.0910894 |
2 | 0.6546536 |
4 | 0.2182178 |
7 | 1.527552 |
T-score scaling
SYNTAX
TSCORES(array)
EXAMPLE
Formula Description
= TSCORES(a) Return the t-score for the values 1, 2, 4, 7
Values | Result |
1 | -1.889822 |
2 | -1.133893 |
4 | 0.377964 |
7 | 2.645751 |
Returns the k-th moment of an array {a} about m
SYNTAX
MOMENT(array;k;m)
EXAMPLE
Formula Description Result
= Moment(a;2;2) Return the k-th moment of a array about m 7.5
Values |
1 |
2 |
4 |
7 |
Returns the k-th moment of an array {a} about the mean
SYNTAX
CENMOM(array)
EXAMPLE
Formula Description Result
= CENMOM(a;2) Return the 2nd moment of an array {a} about the mean 5.25
Values |
1 |
2 |
4 |
7 |
Returns the 2nd central moment of an array {a}
SYNTAX
CENMOM2(array)
EXAMPLE
Formula Description Result
= CENMOM2(a) Return the 2nd moment of an array {a} 5.25
Values |
1 |
2 |
4 |
7 |
Returns the 3rd central moment of an array {a}
SYNTAX
CENMOM3(array)
EXAMPLE
Formula Description Result
= CENMOM3(a) Return the 3rd moment of an array {a} 6
Values |
1 |
2 |
4 |
7 |
Returns the 4th central moment of an array {a}
SYNTAX
CENMOM4(array)
EXAMPLE
Formula Description Result
= CENMOM4(a) Return the 4th moment of an array {a} 48.5625
Values |
1 |
2 |
4 |
7 |
Standardized moment. The normalized k-th central moment based on a population
SYNTAX
STMOMP(array;k)
EXAMPLE
Formula Description Result
= STMOMP(a;3) Standardized moment for a population 0.498784
Values |
1 |
2 |
4 |
7 |
Standardized moment. The normalized k-th central moment based on a sample
SYNTAX
STMOMS(array;k)
EXAMPLE
Formula Description Result
= STMOMS(a;3) Standardized moment for a sample 0.32397
Values |
1 |
2 |
4 |
7 |
Returns the skewness of a distribution based on a population
SYNTAX
SKEWP(array)
EXAMPLE
Formula Description Result
= SKEWP(a) Return the skewness of a distribution based on a population 0.498784
Values |
1 |
2 |
4 |
7 |
Returns the skewness of a distribution based on a sample
SYNTAX
SKEWS(array)
EXAMPLE
Formula Description Result
= SKEWS(a) Return the skewness of a distribution based on a sample 0.863919
Values |
1 |
2 |
4 |
7 |
Returns the population kurtosis
SYNTAX
KURTP(array)
EXAMPLE
Formula Description Result
= KURTP (a) Return the population kurtosis for the array {1,2,4,7} 1.761905
Values |
1 |
2 |
4 |
7 |
Returns the sample kurtosis
SYNTAX
KURTS(array)
EXAMPLE
Formula Description Result
= KURTS (a) Return the sample kurtosis for the array {1,2,4,7} 13.214286
Values |
1 |
2 |
4 |
7 |
Returns the excess population kurtosis
SYNTAX
EXKURTP(array)
EXAMPLE
Formula Description Result
= EXKURTP (a) Returns the excess population kurtosis -1.238095
Values |
1 |
2 |
4 |
7 |
Returns the excess sample kurtosis
SYNTAX
EXKURTS(array)
EXAMPLE
Formula Description Result
= EXKURTS (a) Returns the excess sample kurtosis for the array {1,2,4,7} -0.285714
Values |
1 |
2 |
4 |
7 |
Returns a frequency table. The first set of data is the frequency the number occurs in the data set. The second column is the cumulative frequency.
SYNTAX
FREQUENCY(array)
EXAMPLE
Formula Description Result
= FREQUENCY (a) Returns the frequency and cumulative frequency for the array {1,2,3,1,1,3,3,4} See table below
FREQUENCY(a) | ||
1 | 3 | 3 |
2 | 1 | 4 |
3 | 3 | 7 |
4 | 1 | 8 |
Returns a report containing the SUM, AVERAGE, COUNT, MIN and MAX of an array
SYNTAX
REP(array)
EXAMPLE
Formula Description
= REP(a) Returns the SUM(a), AVERAGE(a), COUNT(a), MIN(a), MAX(a)
Array | Result |
1 | SUM(a) = 24 |
2 | AVERAGE(a) = 4.8 |
4 | COUNT(a) = 5 |
7 | MIN(a) = 1 |
10 | MAX(a) = 10 |
Returns a report containing the MIN, Q1, MEDIAN, Q3 and MAX of an array
SYNTAX
REPFIVE(array)
EXAMPLE
Formula Description
= REPFIVE(a) Returns the MIN(a), Q1(a), MEDIAN(a), Q3(a), MAX(a)
Array | Result |
1 | MIN(a) = 1 |
2 | Q1(a) = 1.5 |
4 | MEDIAN(a) = 4 |
7 | Q3(a) = 8.5 |
10 | MAX(a) = 10 |
Returns a report containing the P2, P9, Q1, MEDIAN, P91 and P98 of an array
SYNTAX
REPSEVEN(array)
EXAMPLE
Formula Description
= REPSEVEN(a) Returns the P2(a), P9(a), Q1,(a), MEDIAN(a), P91(a), P98 (a)
Array | Result |
1 | P2(a) = Too few datapoints to calculate |
2 | P9(a) = Too few datapoints to calculate |
4 | Q1(a) = 2 |
6 | MEDIAN(a) = 6 |
7 | Q3(a) = 10 |
10 | P91 = Too few datapoints to calculate |
12 | P98 = Too few datapoints to calculate |
Returns a report containing the MIN, D1, Q1, MEDIAN, Q3, D9 and MAX of an array
SYNTAX
REPBOWLEY(array)
EXAMPLE
Formula Description
= REPBOWLEY(a) Returns the MIN(a), D1(a), Q1(a), MEDIAN(a), Q3(a), D9(a) and MAX(a)
Array | Result |
1 | MIN(a) = -4 |
2 | D1(a) = -3.25 |
4 | Q1(a) = 1.25 |
7 | MEDIAN(a) = 6.5 |
10 | Q3(a) = 12.75 |
12 | D9 = 12.3 |
6 | MAX = 24 |
13 | |
15 | |
24 | |
-4 | |
-7 |
Returns a report containing the FENCEL, BOXMIN, Q1, MEDIAN, Q3, IQR, BOXMAX and FENCEU of an array
SYNTAX
REPBOX(array)
EXAMPLE
Formula Description
= REPBOX(a) Returns the FENCEL(a), BOXMIN(a), Q1(a), MEDIAN(a), Q3(a), IQR(a), BOXMAX(a), and FENCEU(a)
Array | Result |
1 | FENCEL(a) = -0.80625 |
2 | BOXMIN(a) = -4 |
4 | Q1(a) = -1.125 |
7 | MEDIAN(a) = 0.56 |
-4 | Q3(a) = 3.5 |
-1.5 | IQR = 4.625 |
0.12 | BOXMAX = 7 |
0 | FENCEU = 10.4375 |
Returns a report containing the VARP, SDEVP, SEMP, RSEP, DISPINP, CVP, SNRP, ZSCROREP, SKEWP, KURTP and EXKURTP of an array
SYNTAX
REPSTATP(array)
EXAMPLE
Formula Description
= REPSTATP(a) Returns the VARP(a), SDEVP(a), SEMP(a), RSEP(a), DISPINP(a), CVP(a), SNRP(a), ZSCOREP(a), SKEWP(a), KURTP(a) and EXKURTP(a)
Array | Result |
1 | VARP(a) = 273.276033 |
2 | SDEVP(a) = 16.531063 |
4 | SEMP(a) = 4.984303 |
7 | RSEP(a) = 65.436411 |
10 | DISPINP(a) = 35.871556 |
21 | CVP = 2.179949 |
54 | SNRP =0.46084 |
0 | ZSCOREP = -0.400348 : -0.339856 : -0.218872 : -0.037395 : 0.144081 : 0.809495 : 2.805737 : -0.46084 : -0.533431 : -0.702809 : -1.065762 |
-1.2 | SKEWP = 1.881759 |
-4 | KURT = 5.83748 |
-10 | EXKURTP = 2.83748 |
Returns a report containing the VARS, SDEVS, SEMS, RSES, DISPINS, CVS, SNRS, TSCRORES, SKEWS, KURTS and EXKURTS of an array
SYNTAX
REPSTATS(array)
EXAMPLE
Formula Description
= REPSTATS(a) Returns the VARS(a), SDEVS(a), SEMS(a), RSES(a), DISPINS(a), CVS(a), SNRS(a), TSCORES(a), SKEWS(a), KURTS(a) and EXKURTS(a)
Array | Result |
1 | VARS(a) = 300.603636 |
2 | SDEVS(a) = 17.337925 |
4 | SEMS(a) = 5.227581 |
7 | RSES(a) = 68.619799 |
10 | DISPINS(a) = 39.458711 |
21 | CVS = 2.275861 |
54 | SNRS =0.439394 |
0 | TSCORES = -1.266012 : -1.074719 : -0.692133 : -0.118254 : 0.455625 : 2.559849 : 8.87252 : -1.457305 : -1.686857 : -2.222478 : -3.370236 |
-1.2 | SKEWS = 2.192895 |
-4 | KURTS =9.712467 |
-10 | EXKURTS = 5.5458 |