en:statpqpl:usepl:arkpl:formulapl

Formulas

Defining a formula is a way to recalculate data, resulting in new values in variables.

The window for defining formulas is opened via Data→Formulas…

Formulas assigned to a datasheet variable as the format of that variable are stored with the worksheet data. Their result is automatically recalculated when any of the input data is changed. Formula can be assigned in th Formula… window or by setting the Column format (Ctrl+F10).

Creating formulas

Formulas are entered in the edition box.

The variables referred to in the formulas are entered with their numbers, e.g v1+v2
Text values are entered using an apostrophe, e.g. 'house'.
You can enter functions by double-clicking on the name of the selected function - then the name will appear in the formula edition box, or you can enter the name yourself in the edition box, though the function name is case-insensitive. The function arguments are given in brackets using the syntax given in the function description.

Results of formulas

The results of formulas will be displayed in the selected column.

If the function's arguments include values that it cannot interpret, the program displays a message asking whether to ignore the uninterpreted variables. If you choose Yes, the formula will be recalculated by omitting the unexpanded data. If you choose No, the formula returns error (NA). For example, for the values in columns v1, v2 and v3 respectively: 1, 2, 'ada', the sum function sum(v1;v2;v3) will return a result of 3 - when omitting the uninterpreted value 'ada', or will return NA - when not omitting this value from the calculation.

An empty value (no data) will be returned only if all arguments used in the formula are empty.

You can limit the number of rows involved in a formula by selecting the appropriate number of rows in the datasheet and choosing only from selected rows in the formula window.

Operators

$+$ addition,

$-$ subtraction,

$*$ multiplication,

$/$ division,

$\%$ modulo division (resulting in the remainder of the division),

$>$ greater,

$<$ less,

$=$ equal.

Mathematical functions

Mathematical functions require numeric arguments.

ln(v1) - outputs the natural logarithm of the given number,

log10(v1) - outputs the logarithm of the base 10 for a given number,

logn(v1) - outputs the logarithm of $n$ for the given number,

sqr(v1) - outputs the square of the given number,

sqrt(v1) - outputs the square root of the given number,

fact(v1) - outputs the power of a given number,

degrad(v1) - outputs the angle in radians (the argument of the function is in degrees),

raddeg(v1) - outputs the angle in degrees (the argument of the function is in radians).,

sin(v1) - outputs the sine of the given angle, (the argument of the function is in radians),

cos(v1) - outputs the cosine of the given angle, (the argument of the function is in radians),

tan(v1) - outputs the tangent of the given angle, (the argument of the function is in radians),

ctng(v1) - outputs the cotangent of the given angle, (the argument of the function is in radians),

arcsin(v1) - outputs the arcus sine of the given angle, (the argument of the function is in radians),

arctan(v1) - outputs the arcus tangent of the given angle, (the argument of the function is in radians),

exp(v1) - outputs the value of the number $e$ raised to the power specified by the given value,

frac(v1) - outputs the fractional part of a given number,

int(v1) - outputs the integer part of a given number,

abs(v1) - outputs the absolute value of the specified number,

odd(v1) - if given number is even, outputs 1, 0 otherwise,

sum(v1;…) - outputs the result of adding the specified numbers,

multip(v1;…) - outputs the result of multiplication of specified numbers,

power(v1;n) - outputs the result of raising a number to the power of $n$ ,

norme(v1;…) - outputs the Euclidean norm of the vector,

round(v1;n) - outputs a number rounded to $n$ decimal places.

Statistical functions

Statistical functions require numeric arguments.

stand(v1) - outputs the standardized value of the specified variable,

max(v1,…) - outputs the largest value,

min(v1,…) - outputs the smallest value,

mean(v1,…) - outputs the value of the arithmetic mean,

meanh(v1,…) - outputs the value of the harmonic mean,

meang(v1,…) - outputs the value of the geometric mean,

median(v1,…) - outputs the median value,

q1(v1,…) - outputs the value of the bottom quartile,

q3(v1,…) - outputs the value of the top quartile,

cv(v1,…) - outputs the value of the coefficient of variation,

range(v1,…) - outputs the value of the interval,

iqrange(v1,…) - outputs the value of the quartile interval,

variance(v1,…) - outputs the variance value,

sd(v1,…) - outputs the value of the standard deviation.

Text functions

Text functions work on any string.

upperc(v1) - converts characters from a string to uppercase,

lowerc(v1) - converts characters from a string to lowercase,

clean(v1) - removes characters that cannot be printed,

trim(v1) - removes leading and trailing spaces,

length(v1) - outputs the length of the string,

search('abc';v1) - outputs the position of the beginning of the searched text,

concat(v1;…) - combines texts,

compare(v1;…) - compares texts,

copy(v1;i;n) - returns a portion of text starting from the i-th character, where n is the number of returned characters,

count(v1;…) - outputs the number of cells that are not empty,

counte(v1;…) - outputs the number of cells that are empty,

countn(v1;…) - outputs the number of cells that contain numbers.

Date and time functions

Date and time functions should be used on data formatted as date or time (see chapter \ref{format_kom}). If this is not the case, the program tries to automatically recognize the format, and if not possible gives the value NA.

year(v1;) - outputs the year corresponding to the date,

month(v1;) - outputs the month corresponding to the date,

day(v1;) - outputs the day corresponding to the date,

hour(v1;) - outputs the time corresponding to the specified time,

minute(v1;) - outputs the minute corresponding to the specified time,

second(v1;) - outputs the second corresponding to the specified time,

yeardiff(v1;v2) - outputs the number of years separating two dates,

monthdiff(v1;v2) - outputs the number of months separating two dates,

weekdiff(v1;v2) - outputs the number of weeks separating two dates,

daydiff(v1;v2) - outputs the number of days between two dates,

hourdiff(v1;v2) - outputs the number of hours between the two times,

minutediff(v1;v2) - outputs the number of minutes separating the two times,

seconddiff(v1;v2) - outputs the number of seconds separating the two times,

compdate(v1;v2) - compares dates and outputs the number 1 when v1>v2; 0 when v1=v2, -1 when v1<v2.

Logic functions

if(question;'yes - answer';'no - answer') - a question is formulated as an expression that can be true or false; the function outputs one value if the expression is true and the other if it is false,

and - conjunction operator - returns true (1) when all conditions it combines are true, false (0) otherwise,

or - the alternative operator returns true (1) when at least one of its conditions is true, false (0) otherwise,

xor - disjunctive alternative operator - returns true (1) when one of the conditions it combines is true, false (0) otherwise,

not - negation operator used in a conditional statement if,

empty(v1) - outputs true (1) when empty cells are present, false (0) otherwise,

text(v1) - outputs true (1) when text is present, false (0) otherwise,

number(v1) - outputs true (1) when a number is present, false (0) otherwise.