RLPlot parser

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

Conditional assignment

A conditional assignment has the following syntax exp1 ? exp2 : exp3 .
This means that the whole expression evaluates to exp2 if exp1 is true, and to exp3 if exp1 is false.

Select cases 'where'

Functions operating on ranges or arrays can be modified by adding a 'where' clause. The most common use of this clause is probably the count() function. A dummy variable, $$, is introduced which represents each element of the array. This clause is available for all functions operating on ranges and arrays.

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

String operations

Strings can be concatenated using a '+' sign. This may also be used to combine strings and values.

Note:	This operation results in a string which cannot be used for further calculations.
Examples:	=a3/b1*100+" %" ="Value is "+a2>a1?"higher":"lower"

Constants

Constants usually consist of digits, decimal point, and a preceeding unary '-'.
Valid contants are: 1, 3.14, -123, 1.0e-13.

Predifined constants:

pi = 3.14159265358979
e = 2.71828182845905
true = 1
false = 0
inf = a very huge number

Variables

Local variables are any combination of letters. They are case sensitive and may contain digits bracketed by letters.
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'
Predifined variables:	zdiv = 1.0	return this value upon zero divide error
	$$	syntactical 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.

Arithmetic Functions

Following functions may be used in formulas and expressions:

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' to integer
ln(expr): natural logarithm of 'expr', synonym to 'log(expr)'
log(expr): natural logarithm of 'expr', synonym to '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'

String Functions

asc(string)

returns a value representing the character code corresponding to the first letter of string.

call(string[;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.

Date- and Time Functions

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.

date(value): make value a date
datestr(datetime[, format]): convert the datetime value to a string, optionally using the format given
datetime(value): make value a date and time
dateval(string[, format]): convert string to a datetime value, optionally using the format given
dom(datetime): extracts day of month from datetime (1-31)
dow(datetime): extracts day of week from datetime (1-7)
doy(datetime): extracts day of year from datetime (1-366)
hours(datetime): 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 chi² 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 r_s, the probability of r_s <> 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'