Spreadsheet calculation

To see a list of available spreadsheet functions, choose Edit/Function Selection, or press F7

Calculation cells must begin with '=' followed by the name and arguments. Arguments of the function can be constants - numeric or textual, or cell references. Cells can be referred by their row and column. For instance A2 refers to the first column, second row. B3 - second column, 3rd row.

Examples of spreadsheet functions:

You can also:

Full list of spreadsheet functions:

--------- Model related functions|
MODEL("Column";parameters;units)| Calculates the model, defined in column "COLUMN", for factors specified in "parameters". If the model is designed to work in coded factors, use "coded" for units, otherwise, use "natural". The number of parameters should correspond to the number of variables as defined in the model. For example: =MODEL("I";A1:B1;"coded") will compute the model with 2 coded factors with values taken from cells A1 and B1. =MODEL("L";2.1;2.2;"natural")
CODE(x;xmin;xmax)| Converts a variable "x" from natural units (xmin; xmax) to coded units ranging between (-1; 1)
DECODE(x;xmin;xmax)| Converts a coded variable "x" with a range of (-1; 1), to natural units in range (xmin; xmax)
GCODE(x;xmin1;xmax1;xmin2;xmax2)| Rescales a variable "x" from (xmin1; xmax1) to (xmin2; xmax2)
QS_GETRESULT(p)| returns the value of output variable with index p
QS_SETRESULT(p,v)| stores the value v in output variable with index p. Returns 0 if successful or -1234567890 if failed.
--------- Trigonometric function|
SIN(parameter)| Sine of parameter
COS(parameter)| Cosine of parameter
TAN(parameter) | Tangens of parameter
COTAN(parameter) | Cotangens of parameter
SINH(parameter) | Hiperbolic sine of parameter
COSH(parameter) | Hiperbolic cosine of parameter
TANH(parameter) | Hiperbolic tangens of parameter
COTANH(parameter) | Hiperbolic cotangens of parameter
ASIN(parameter) | ASIN of parameter
ACOS(parameter) | ACOS of parameter
ATAN(parameter) | ATAN of parameter
ACOTAN(parameter) | ACOTAN of parameter
RADIANS(parameter) | Converts degrees to radians
DEGREES(parameter) | Converts radians to degrees
--------- Algebraic functions |
SQR(parameter)| parameter squared
SQRT(parameter)| square root of parameter
CUBE(parameter) | parameter to the power of 3
POWER(parameter,exp) | parameter to the power of exp
LN(parameter) | Natural logarithm of parameter
LOG2(parameter) | Log Base2 of parameter
LOG10(parameter) | Log Base10 of parameter
EXP(parameter) | exponent of parameter
ABS(parameter) | absolute value of parameter
ROUND(parameter) | rounds parameter
TRUNC(parameter) | integer bit of parameter
CEILING(parameter; s) | rounds parameter with accuracy of "s"
FRAC(parameter) | fractional bit of parameter
FACT(parameter) | factorial of parameter
INT(parameter) | the integer bit of parameter
RAND(R) | random number between 0 and R
CHS(parameter) | change of sign of parameter
--------- Statistical functions |
SUM(R1:R10)| Sum of values in range R1:R10
STDEV(R1:R10)| Standard deviation of values in range R1:R10 based on sample (n-1)
STDEVP(R1:R10)| Standard deviation of values in range R1:R10 based on entire population (n)
DEVSQ(R1:R10)| sum of squares of the standard deviations of values in range R1:R10
VAR(R1:R10)| Variation of values in range R1:R10
AVERAGE(R1:R10)| Mean of values in range R1:R10
PRODUCT(R1:R10)| Product of the values in range R1:R10
MIN(R1:R10)| The smallest number in range R1:R10
MAX(R1:R10)| The largest number in range R1:R10
COUNT(R1:R10)| Number of cells in range R1:R10
COUNTA(R1:R10)| Number of non-empty cells in range R1:R10
COUNTIF(R1:R10;condition)| Number of cells in range R1:R10, that meet "Condition"
STN1(R1:R10)| Signal-to-noise ratio for cells in range R1:R10
STN2(R1:R10)| Signal-to-noise ratio for cells in range R1:R10 for the case "the smaller the better"
STN3(R1:R10)| Signal-to-noise ratio for cells in range R1:R10 for the case "the larger the better"
LOSS1(k;target;ymean;sigma)| Loss function - L = k*(sqr(ymean-target)+sqr(sigma))
LOSS2(k;y)| Loss function - L = k*y*y
LOSS3(k;y)| Loss function - L = k/(y*y); L=1234567890 if y=0

--------- Time and date|
HOUR(parameter)| extracts the "hour" from parameter
MIN(parameter) | extracts "minutes" from parameter
SECOND(parameter) | extracts "seconds" from parameter
DAY(parameter) | extracts "day" from parameter
MONTH(parameter) | extracts "month" from parameter
YEAR(parameter) | extracts "year" from parameter
WEEKDAY(parameter) | extracts "weekday" from parameter
TODAY | returns current date
NOW | return current time
--------- Logical functions|
GT(param1;param2) | "greater than" returns 1 if param1>param2
LT(param1;param2) | "less than" ; returns 1 if param1<param2
EQ(param1;param2) | "equals" ; returns 1 if param1=param2 or 0 if param1<>param2
CHOOSE(sel;param1;param2) | returns param1 if sel>0, otherwise returns param2
AND(parameters) | logical "AND"
OR((parameters) | logical "OR"
NAND(parameters) | logical "NOT-AND"
NOR((parameters) | logical "NOT-OR"
XOR((parameters) | logical "X-OR"
NOT(parameter) | logical "NO"
TRUE | logical constant
FALSE| logical constant
--------- String functions|
LEN(parameter) | length of parameter
LOWER(parameter) | lowecase of parameter
UPPER(parameter) | uppercase of parameter
CONCATENATE(parameter list) | concatenates specified strings
SUBSTITUTE(param text; param oldtext; param new text) | replaces "oldtext" in "text" with "new text"
LEFT(param string;len integer) | returns the first "len" characters in "string"
RIGHT(param string;len integer) | returns the last "len" characters in "string"
MID(param string; pos; len; integer)| returns the len charachters from "string" starting from "pos"
TRIM(param) | removes blank characters from parameter
SEARCH(find text; text) | returns the position of "find text" in "text"
LOOKUP(param; R1:R10, S1:S10) | returns the value of elemtn in R1:R10 that corresponds to the value of "param" in S1:S10
MATCH(lookup; R1:R10) | returns the index of element "lookup" in the range R1:R10
INDEX(R1:R10; val1, val2) | returns the value of of element indexed "val1, val2" from the range R1:R10
 

Back to Data Entry