RLPlot formulas and and functions
RLPlot uses a common parser for spread sheet cells, function plotting and curve fitting. This parser interprets formulas which are readable to humans and executes them on the computer. Spread sheet formulas are preceeded by a '=' sign. For example '=1+2' assigns a value of 3 to the current cell and '=b3+2' assings the value of cell b3 to the cell and increments it by 2. Funcion plots accept formulas with several expressions on a line separated by ';' or on different lines. By default the last expression is used as result. In this case you may explicitly state 'y= ...' to determine which expression gives the result.
Operators
| ^ | exponentiation | |
| * | multiplication | |
| / | division | |
| + | addition | |
| - | subtraction | |
| == | logical equal | |
| < | logical less than | |
| > | logical greater than | |
| !=, <> | logical not equal | |
| <= | logical less or equal |
| >= | logical greater or equal | |
| && | logical AND | |
| || | logical OR | |
| ++ | increment | |
| -- | decrement | |
| += | add value | |
| -= | subtract value | |
| *= | multiply with value | |
| /= | divide by value |
This means that the whole expression evaluates to exp2 if exp1 is true, and to exp3 if exp1 is false.
Examples:
- =count(a1:b20 where $$>0)
- count all values in a1:b20 wich are higher than 0.0
- =count(a1:b20 where $$>=10 && $$ <20)
- count all values in a1:b20 wich are between 10 and 20
- =mean(a1:b20 where $$!=0)
- calculate the mean of all values in a1:b20 wich are not zero
Examples:
="Value is "+a2
Valid contants are: 1, 3.14, -123, 1.0e-13.
Predefined constants:
e = 2.71828182845905
true = 1
false = 0
inf = a very huge number
Spread sheet cells may be onsidered as global variables. They may be used as arguments on the right and left side of an assignment. References to spread sheet cells begin with a letter and end with a digit (any '$' sign is ignored). A reference to outside the spread sheet is treated like a local variable.
Examples:
| ab=1+2*3 | assign a value of '7' to the local variable 'ab' | |
| b3=a1+2*3 | take the value of cell 'a1', increment the value by '6' and place the result in the spread sheet cell 'b3' |
Predefined variables:
| zdiv = 1.0 | return this value upon zero divide error | |
| $$ | sytactical part of the 'where' clause |
NOTE: Names of predefined constants and functions are reserved. They must not be used as variable names. All these names are lowercase. A save way to define variables is to include uppercase letters or an underscore '_' in the variable names.
- abs(expr)
- absolute value of 'expr'
- acos(expr)
- arccosine of 'expr'
- asin(expr)
- arcsine of 'expr'
- atan(expr)
- arctangent of 'expr'
- cos(expr)
- cosine of 'expr'
- cosh(expr)
- hyperbolic cosine of 'expr'
- exp(expr)
- exponential of 'expr'
- floor(expr)
- truncate 'expr'
- log(expr)
- natural logarithm of 'expr', same as 'ln(expr)'
- log10(expr)
- base-10 logarithm of 'expr'
- rand()
- return a random number in the range 0.0 - 1.0
- sign(expr)
- sign (1, 0, or -1) of 'expr'
- sin(expr)
- sine of 'expr'
- sinh(expr)
- hyperbolic sine of 'expr'
- sqrt(expr)
- square root of 'expr'
- srand(expr>
- use 'expr' as new seed for random numbers
- tan(expr)
- tangent of 'expr'
- tanh(expr)
- hyperbolic tangent of 'expr'
- asc(string)
returns a value representing the character code corresponding to the first letter of string.
- call(range[;parameters])
executes the script in 'range'. An optional string defining 'parameters' may be specified. 'Call' uses the same name space as the calling process and variables of the calling process are global to the called script.
See also exec()- chr(value)
returns a string containing the character associated with the character code value. If value is not a valid number "?" is returned.
- eval(expr)
evaluates the expression expr and returns the result.
examples: eval("1+2") returns 3
a=2;eval("a*4") returns 8
eval('"hello"+" world"') returns "hello world"
exec(range[;parameters])executes the script in 'range'. An optional string defining 'parameters' may be specified. 'Exec' creates a new instance of the parser and variables of the calling process are hidden. see also call().
example: exec(c1:e10; "x=4")- ltrim(string)
removes leading white space from string.
- rtrim(string)
removes trailing white space from string.
- strlen(string)
returns the length of string.
example: strlen("hello") returns 5.- strpos(needle; haystack)
returns the position of the first occurence of needle in haystack, -1 on error.
example: strpos("r"; "world") returns 2.- strrepl(search; replace; haystack)
replaces all occurences of search with replace in haystack.
example: strrepl("a"; "e"; "hallo") returns "hello".- substr(text; pos1; pos2)
returns a string containing the characters of text from pos1 through pos2.
example: substr("hello"; 2; 3) returns "ll".- tolower(string)
convert string to lower case.
- toupper(string)
convert string to upper case.
- trim(string)
removes leading and trailing white space from string.
- ucfirst(string)
make first character of string upper case.
- ucword(string)
make first character of every word upper case.
RLPlot's internal storage of date- and time data is a floating point number representing days after 1-Jan-1900 starting with 1 while the decimals give the fraction of the day. For further information about date- and time formats click here.
make value a date
convert the datetime value to a string, optionally using the format given
make value a date and time value
convert string to a datetime value, optionally using the format given
extracts day of month from datetime (1-31)
extracts day of week from datetime (1-7)
extracts day of year from datetime (1-366)
extracts hours of day from datetime (0-23)
- leapyear(year)
returns true if year is a leapyear
example: leapyear(2000) returns ' true '- minutes(datetime)
extracts minutes from datetime (0-59)
- month(datetime)
extracts month from datetime
- now()
return the current system time
- seconds(datetime)
extracts seconds from datetime (0-59)
- time(value)
make value a time
- today()
return the current system date
- year(datetime)
extracts year from datetime
Statistical Functions
Some of these functions use a range to return results. For further information about destination ranges click here.
- average(array)
returns the average of 'array', a synonym for 'mean(array)'
- beta(u, v)
evaluate the beta function with shape factors 'u' and 'v'
- betai(u, v, x)
the incomplete beta function
- bincof(n, k)
the binomial coefficient for 0 < k < n
- binomdist(s, n, p)
the cumulative binomial distribution for 0 < s < n, and 0 < p < 1
- binomfreq(s, n, p)
the frequecies (densities) of the binomial distribution for 0 < s < n, and 0 < p < 1
- cauchydist(x, location[, scale=1])
the cumulative Cauchy (Lorentz) distribution
- cauchyfreq(x, location[, scale=1])
densities of the Cauchy (Lorentz) distribution
- cauchyinv(p, location[, scale=1])
the inverse of the cumulative Cauchy (Lorentz) distribution
- chidist(chi2, df)
returns probability for 'chi2' with 'df' degrees of freedom
- chifreq(chi2, df)
the chi2 density (frequencies) function
- chiinv(p, df)
returns the critical value for chi2 for probability 'p' with 'df' degrees of freedom,
a replacement for printed versions of the chi2 distribution- classes(start, step, array, range)
create a frequency distribution using 'start' and 'step' to define classes for 'array'. Returns the number of items in 'array' as result and the size of each class in the destination range.
example: classes(0;1;a1:a10;b1:b5)- correl(range1; range2[;"destination"])
A synonym for pearson(). For non parametric correlations see spearman() and kendall().
- count(array)
count elements of 'array'
examples: count(1, 2, 3), count(a1:a10), count(a1:a10, c1:d10)- covar(range1; range2)
returns the covariance between array1 and array2.
- erf(expr)
evaluate the error function at 'expr'
- erfc(expr)
the complementary error function
- expdist(x, lamda)
the cumulative exponential distribution
- expfreq(x, lamda)
the density function of the exponential distribution
- expinv(p, lamda)
the inverse of the cumulative exponential distribution
- factorial(expr)
returns the factorial of 'expr' (expr!) where expr is a positive integer
- fdist(f, df1, df2)
returns the probability (1-alpha) of 'f' with 'df1' degrees of freedom in the numerator and 'df2' in the denominator
- ffreq(f, df1, df2)
the probability density function of the F-distribution
- finv(p, df1, df2)
returns the critical value of the F-distribution,
a replacement for printed versions of the F-distribution- ftest(array1; array2[;"destination"])
compare variances and return the probability of the F-statistics.
If destination is given this range is filled with mean, SD, n of array1 and mean, SD, n of array2.
examples: ftest(a1:a10;b1:b10), ftest(a1:a10; 1,2,3,4; "c1:c10")- gammaln(x)
returns the natural logarithm of the gamma function, ln(Γ(x))
- gammp(a, x)
the incomplete gamma function
- gammq(a, x)
complementary incomplete gamma function
- geomdist(x, p)
the cumulative geometric distribution
- geomfreq(x, p)
densities of the geometric distribution
- gmean(array)
returnes the geometric mean of 'array'
- hmean(array)
returnes the harmonic mean of 'array'
- hyperdist(k, N, m, n)
the cumulative hypergeometric distribution
- hyperfreq(k, N, m, n)
densities of the hypergeometric distribution
- kendall(range1; range2[;"destination"])
Kendall's non parametric rank correlation.
If destination is given this range is filled with Kendall's tau, the number of standard deviations from zero, the two sided significance level, and the number of valid cases.- kurt(array)
returnes the kurtosis of 'array'
- logisdist(x, location[, scale=1])
the cumulative logistic distribution
- logisfreq(x, location[, scale=1])
densities of the logistic distribution
- logisdist(p, location[, scale=1])
the inverse cumulative logistic distribution
- lognormdist(x, m, s)
cumulative lognormal distribution
- lognormfreq(x, m, s)
lognormal density function
- lognorminv(p, m, s)
inverse cumulative lognormal distribution
- max(array)
highest value of 'array'
examples: max(1, 2, 3), max(a1:a10), max(a1:a10, c1:d10)- mean(array)
returns the arithmetic mean of 'array'
examples: mean(1, 2, 3), mean(a1:a10), mean(a1:a10, c1:d10)- median(array)
returnes the median of 'array', a synonym for quartile2(array)
- min(array)
lowest value of 'array'
examples: min(1, 2, 3), min(a1:a10), min(a1:a10, c1:d10)- normdist(x, m, s)
probability of the cumulative normal distribution at 'x' with mean 'm' and standard deviation 's',
a replacement for printed versions of the cumulative normal distribution- normfreq(x, m, s)
frequency (density) of the normal distribution at 'x' with mean 'm' and standard deviation 's'.
- norminv(p, m, s)
return the critical value of the cumulative normal distribution for probability 'p' with mean 'm' and standard deviation 's', a replacement for printed versions of the inverse cumulative normal distribution
- pearson(array1; array2[;"destination"])
Pearsons parametric correlation.
If destination is given this range is filled with Pearsons r, Fisher's z, the probability of r <> 0, and the number of valid cases.- poisdist(x, m)
the cumulative poisson distribution
- poisfreq(x, m)
the frequencies (densities) of the poisson distribution
- ptukey(q, nmeans, df[,nranges=1])
the cumulative density function of the Studentized Range Distribution.
- qtukey(p, nmeans, df[,nranges=1])
returns the quantiles of the Studentized Range Distribution, the inverse of ptukey().
- quartile1(array)
calculates the 25% quartile of 'array'
- quartile2(array)
calculates the 50% quartile of 'array', a synonym for median(array)
- quartile3(array)
calculates the 75% quartile of 'array'
- rank(value; array)
returns the rank of 'value' in 'array' with mid-tie ranking. If 'value' is not present in 'array' a rank of 0 is returned.
- regression(range1; range2; "destination")
linear regression analysis of independent variable 'array1' and dependent variable 'array2'.
The destination range is filled with slope, intersept, mean1, mean2, SE of slope, variance1, variance2, variance of fit, F of regression and significance.
example:regression(a1:a10;b1:b10;"c1:c10")- skew(array)
returnes the skewness of 'array'
- spearman(range1; range2[;"destination"])
Spearmans non parametric rank correlation.
If destination is given this range is filled with sum of squared rank differences, number of SD's the sum differs from expected, the significance of this SD, Spearmans rs, the probability of rs <> 0, and the number of valid cases.- stdev(array)
standard deviation of mean
examples: stdev(1, 2, 3), stdev(a1:a10), stdev(a1:a10, c1:d10)- sterr(array)
standard error of mean
examples: sterr(1, 2, 3), sterr(a1:a10), sterr(a1:a10, c1:d10)- sum(array)
sum of all values of 'array'
examples: sum(1, 2, 3), sum(a1:a10), sum(a1:a10, c1:d10)- tdist(t, df)
returns the probability (1-alpha) of 't' with 'df' degrees of freedom
- tfreq(t, df)
the probability density function of the t-distribution
- tinv(p, df)
returns the critical t of Student's t-distribution,
a replacement for printed versions of the t-distribution- ttest(array1; array2[;"destination"])
compare means and return the probability of the t-statistics.
If destination is given this range is filled with mean, SD, n of array1, mean, SD, n of array2, probability for equal variances, Welch's corrected df, corrected probability.
examples: ttest(a1:a10;6,7,4), ttest(a1:a10; b1:b10; "c1:c10")- ttest2(range1; range2[;"destination"])
paired t-test for dependent samples. If destination is given this range is filled with mean and SD of range1, mean and SD of range2, number of valid cases and probability.
- utest(array1; array2[;"destination"])
Mann-Whitney U Test, a non parametric alternative to t-test().
If destination is given this range is filled with rank sum 1, rank sum 2, U, Z, n1, n2, p-level, Z corrected for ties, and the corrected p-level.- variance(array)
variance of 'array'
examples: variance(1, 2, 3), variance(a1:a10), variance(a1:a10, c1:d10)- weibdist(x, shape[, scale=1])
the cumulative weibull distribution
- weibfreq(x, shape[, scale=1])
densities of the weibull distribution
- weibinv(p, shape[, scale=1])
inverse cumulative weibull distribution
Array Functions
Arrays are lists of numerical values. Note that some of the statistical functions are in fact array functions. For further information about arrays click here.
- asort(array)
sorts 'array' and returns the sorted array
- asort2(array1, array2)
sorts 'array1' making the corresponding rearrangements in 'array2'. The function returns the number of elements which have been sorted.
- count(array)
return the number of elements in 'array'
- crank(array)
replaces all elements of 'array by their rank. 'array' must be sorted before 'crank' is called. The function returns theta.
- fill(array; dest)
copies 'array' to the spreadsheet range given by dest. The function returns the number of elements copied.
example: a=(a1:a10); fill(a;"b1:b10")
copy data from the spreadsheet to array 'a' and then the array back to the spreadsheet.
- invert(array)
inverts 'array' and returns the inverted array
- mkarr(range; MD)
returns an array which contains the same number of elements as 'range'. All missing and non-numerical data are replaced by the MD value.
example: a=(a1:a10;-9999)- randarr(array)
randomizes 'array' and returns the new array
- resample(array)
resamples 'array' and returns the new array
- subarr(array1; pos1[; pos2])
return an array containing the elements pos1 through pos2 of array1.
- sum(array)
sum of all values of 'array'