Difference between revisions of "Formula Specification in Templates"
Jump to navigation
Jump to search
(→Text) |
|||
| Line 128: | Line 128: | ||
|EXP | |EXP | ||
|''EXP(Number)'' | |''EXP(Number)'' | ||
| | |Returns e raised to the power of a given number | ||
|''EXP(1) ⇒ 2.71828'' | |''EXP(1) ⇒ 2.71828'' | ||
|- | |- | ||
|LN | |LN | ||
|''LN(Number)'' | |''LN(Number)'' | ||
| | |Returns the natural logarithm of a number | ||
|''LN(1)) ⇒ 0; LN(EXP(1)) ⇒ 1'' | |''LN(1)) ⇒ 0; LN(EXP(1)) ⇒ 1'' | ||
|- | |- | ||
|LOG | |LOG | ||
|''LOG(Number;[Base=10])'' | |''LOG(Number;[Base=10])'' | ||
| | |Returns the logarithm of a number to a specified base (10 if missing) | ||
|''LOG(1; 10) ⇒ 0; LOG(10; 10) ⇒ 1'' | |''LOG(1; 10) ⇒ 0; LOG(10; 10) ⇒ 1'' | ||
|- | |- | ||
|LOG10 | |LOG10 | ||
|''LOG10(Number)'' | |''LOG10(Number)'' | ||
| | |Returns the base-10 logarithm of a number | ||
|''LOG10(1) ⇒ 0; LOG10(10) ⇒ 1'' | |''LOG10(1) ⇒ 0; LOG10(10) ⇒ 1'' | ||
|- | |- | ||
|PI | |PI | ||
|''PI()'' | |''PI()'' | ||
| | |Returns the value of pi | ||
|''PI() ⇒ 3.141592653'' | |''PI() ⇒ 3.141592653'' | ||
|- | |- | ||
|POWER | |POWER | ||
|''POWER(Base;Number)'' | |''POWER(Base;Number)'' | ||
| | |Returns the result of a number raised to a power | ||
|''POWER(2;4) ⇒ 16'' | |''POWER(2;4) ⇒ 16'' | ||
|- | |- | ||
|SQR | |SQR | ||
|''SQR(Number)'' | |''SQR(Number)'' | ||
| | |Returns the result of a squared number | ||
|''SQR(5) ⇒ 25; SQR(-5) ⇒ 25'' | |''SQR(5) ⇒ 25; SQR(-5) ⇒ 25'' | ||
|- | |- | ||
|SQRT | |SQRT | ||
|''SQRT(Number)'' | |''SQRT(Number)'' | ||
| | |Returns a positive square root of a positive Number | ||
|''SQRT(36) ⇒ 6; SQRT(-36) ⇒ NAN'' | |''SQRT(36) ⇒ 6; SQRT(-36) ⇒ NAN'' | ||
|- | |- | ||
|SUM | |SUM | ||
|''SUM(Number1;Number2; ... )'' | |''SUM(Number1;Number2; ... )'' | ||
| | |Returns the sum of a series of numbers | ||
|''Inputs linked to data '''X[0;1;2;3;4;5;6]'''):'' | |''Inputs linked to data '''X[0;1;2;3;4;5;6]'''):'' | ||
''SUM('''[X⇓2:⇓5]''') ... SUM('''⟨1; 2; 3; 4⟩''') ⇒ 10'' | ''SUM('''[X⇓2:⇓5]''') ... SUM('''⟨1; 2; 3; 4⟩''') ⇒ 10'' | ||
| Line 176: | Line 176: | ||
|DEG | |DEG | ||
|''DEGREES(Angle [rad])'' | |''DEGREES(Angle [rad])'' | ||
| | |Converts radians into degrees | ||
|''DEGREES(PI()) ⇒ 180°; DEGREES(PI()/2) ⇒ 90°'' | |''DEGREES(PI()) ⇒ 180°; DEGREES(PI()/2) ⇒ 90°'' | ||
|- | |- | ||
|RAD | |RAD | ||
|''RADIANS(Number)'' | |''RADIANS(Number)'' | ||
| | |Converts degrees to radians | ||
|''RADIANS(180) ⇒ π (3.141592653)'' | |''RADIANS(180) ⇒ π (3.141592653)'' | ||
|- | |- | ||
|ROUND | |ROUND | ||
|''ROUND(Number;Places)'' | |''ROUND(Number;Places)'' | ||
| | |Rounds a number to a specified number of digits | ||
- negative Places round to the left of the decimal point | |||
|''ROUND(58.563;3) ⇒ 58.563'' | |''ROUND(58.563;3) ⇒ 58.563'' | ||
''ROUND(58.563;2) ⇒ 58.56'' | ''ROUND(58.563;2) ⇒ 58.56'' | ||
| Line 201: | Line 201: | ||
|ROUNDUP | |ROUNDUP | ||
|''ROUNDUP(Number;Places)'' | |''ROUNDUP(Number;Places)'' | ||
| | |Rounds a number up, toward zero | ||
|''ROUNDUP(58.563;3) ⇒ 58.564'' | |''ROUNDUP(58.563;3) ⇒ 58.564'' | ||
''ROUNDUP(58.563;2) ⇒ 58.57'' | ''ROUNDUP(58.563;2) ⇒ 58.57'' | ||
| Line 211: | Line 211: | ||
|ROUNDDOWN | |ROUNDDOWN | ||
|''ROUNDDOWN(Number;Places)'' | |''ROUNDDOWN(Number;Places)'' | ||
| | |Rounds a number down, toward zero | ||
|''ROUNDDOWN(58.563;3) ⇒ 58.563'' | |''ROUNDDOWN(58.563;3) ⇒ 58.563'' | ||
''ROUNDDOWN(58.563;2) ⇒ 58.56'' | ''ROUNDDOWN(58.563;2) ⇒ 58.56'' | ||
| Line 231: | Line 231: | ||
|- | |- | ||
|CONCAT | |CONCAT | ||
|CONCAT(Value1;Value2; ... ) | |''CONCAT(Value1;Value2; ... )'' | ||
|Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments | |Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments | ||
- Value1, Value2, ... can be as Text or Number | - Value1, Value2, ... can be as Text or Number | ||
|CONCAT(12;34;56) | |''CONCAT(12;34;56) ⇒ 123456.000'' | ||
CONCAT("12";"34";"56") | ''CONCAT("12";"34";"56") ⇒ 123456.000'' | ||
CONCAT(12;"34";56) | ''CONCAT(12;"34";56) ⇒ 123456.000'' | ||
''Input '''[GWT<sub>b</sub>]''' = 15'' | ''Input '''[GWT<sub>b</sub>]''' = 15'' | ||
CONCAT([ | ''CONCAT(['''GWT<sub>b</sub>'''];" m") ⇒ "15 m"'' | ||
|- | |- | ||
|LEFT | |LEFT | ||
|LEFT(Text;[Count=1]) | |''LEFT(Text;[Count=1])'' | ||
|Returns the leftmost characters from a text value | |Returns the leftmost characters from a text value | ||
|LEFT("qwert";3) | |''LEFT("qwert";3) ⇒ "qwe"'' | ||
LEFT("qwert";1) | ''LEFT("qwert";1) ⇒ "q"'' | ||
|- | |- | ||
|RIGHT | |RIGHT | ||
|RIGHT(Text;[Count=1]) | |''RIGHT(Text;[Count=1])'' | ||
|Returns the rightmost characters from a text value | |Returns the rightmost characters from a text value | ||
|RIGHT("qwert";3) | |''RIGHT("qwert";3) ⇒ "ert"'' | ||
RIGHT("qwert";1) | ''RIGHT("qwert";1) ⇒ "t"'' | ||
|- | |- | ||
|LEN | |LEN | ||
|LEN(Text) | |''LEN(Text)'' | ||
|Returns the number of characters in a text string | |Returns the number of characters in a text string | ||
|LEN("qwert") | |''LEN("qwert") ⇒ 5'' | ||
LEN("") | ''LEN("") ⇒ 0'' | ||
|- | |- | ||
|SEARCH | |SEARCH | ||
|SEARCH(Find text;Within text;[Start=1]) | |''SEARCH(Find text;Within text;[Start=1])'' | ||
|Finds one text value within another (not case-sensitive) | |Finds one text value within another (not case-sensitive) | ||
|SEARCH(" | |''SEARCH("G";"Hello GEO!";1) ⇒ 7'' | ||
|} | |} | ||
=== Statistical === | === Statistical === | ||
| Line 276: | Line 275: | ||
|AVERAGE | |AVERAGE | ||
|''AVERAGE(Number1;Number2; ... )'' | |''AVERAGE(Number1;Number2; ... )'' | ||
| | |Returns the average of its arguments | ||
|''AVERAGE(3;5;1) ⇒ 3'' | |||
''Inputs linked to data '''X[0;1;2;3;4;5;6]'''):'' | |||
''AVERAGE('''[X⇓3:⇓5]''') ... AVERAGE('''⟨2; 3; 4⟩''') ⇒ 3'' | ''AVERAGE('''[X⇓3:⇓5]''') ... AVERAGE('''⟨2; 3; 4⟩''') ⇒ 3'' | ||
|- | |- | ||
|MAX | |MAX | ||
| | |''MAX(Number1;Number2; ... )'' | ||
| | |Returns the maximum value in a list of arguments | ||
| | |''MAX(3;5;1) ⇒ 5'' | ||
''Inputs linked to data '''X[0;1;2;3;4;5;6]'''):'' | |||
''MAX('''[X⇓3:⇓5]''') ... AVERAGE('''⟨2; 3; 4⟩''') ⇒ 3'' | |||
|- | |- | ||
|MIN | |MIN | ||
| | |MIN(Number1;Number2; ... ) | ||
| | |Returns the minimum value in a list of arguments | ||
| | |''MIN(3;5;1) ⇒ 1'' | ||
''Inputs linked to data '''X[0;1;2;3;4;5;6]'''):'' | |||
''MIN('''[X⇓3:⇓5]''') ... AVERAGE('''⟨2; 3; 4⟩''') ⇒ 2'' | |||
|- | |||
|PERCENTIL | |||
|''PERCENTILE(Array;k)'' | |||
|Returns the k-th percentile of values in a range | |||
|''Inputs linked to data '''X[1;2;3;4;5]'''):'' | |||
''PERCENTILE(['''X⇕'''];0.5) ⇒ 3'' | |||
|- | |||
|PERCENTILE.EXC | |||
|''PERCENTILE.EXC(Array;k)'' | |||
|Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive | |||
|''Inputs linked to data '''X[1;2;3;4;5]'''):'' | |||
''PERCENTILE.INC(['''X⇕'''];1) ⇒ NAN'' | |||
''PERCENTILE.INC(['''X⇕'''];0.4) ⇒ 2.4'' | |||
|- | |- | ||
|'' | |PERCENTILE.INC | ||
| | |''PERCENTILE.INC(Array;k)'' | ||
| | |Returns the k-th percentile of values in a range | ||
|''Inputs linked to data '''X[1;2;3;4;5]'''):'' | |||
''PERCENTILE.INC(['''X⇕'''];1) ⇒ 5'' | |||
|- | |- | ||
|'' | |STDEV | ||
| | |''STDEV(Number1;Number2; ... )'' | ||
| | |Estimates standard deviation based on a sample | ||
|''Inputs linked to data '''X[1;2;3;4;5]'''):'' | |||
''STDEV(['''X⇕''']) ⇒ 1.581'' | |||
|- | |- | ||
|''' | |STDEV.P | ||
| | |''STDEV.P(Number1;Number2; ... )'' | ||
| | |Calculates standard deviation based on the entire population | ||
| | |''Inputs linked to data '''X[1;2;3;4;5]'''):'' | ||
''STDEV.P(['''X⇕''']) ⇒ 1.414'' | |||
|- | |||
|STDEV.S | |||
|''STDEV.S(Number1;Number2; ... )'' | |||
|Estimates standard deviation based on a sample | |||
|''Inputs linked to data '''X[1;2;3;4;5]'''):'' | |||
''STDEV.S(['''X⇕''']) ⇒ 1.581'' | |||
|- | |||
|MODE | |||
|''MODE(Number1;Number2; ... )'' | |||
|Returns the most common value in a data set | |||
|''Inputs linked to data '''X[1;2;3;4;5]'''):'' | |||
''MODE((['''X⇕''']) ⇒ NAN'' | |||
''MODE((['''X⇕'''];3) ⇒ 3'' | |||
|- | |- | ||
|'' | |MEDIAN | ||
| | |''MEDIAN(Number1;Number2; ... )'' | ||
| | |Returns the median of the given numbers | ||
|''MEDIAN(1;2;3;4;5;6;7) ⇒ 4'' | |||
|} | |} | ||
=== Matrix === | === Matrix === | ||
Revision as of 15:49, 25 May 2023
List of formula specification using in Templates. (Notes: bold inputs for functions are linked to the data in program).
Categories of functions:
Logical
| Name | Syntax, parameters | Description | Example |
|---|---|---|---|
| AND | AND(Boolean1;Boolean2; ... ) | Returns TRUE if all of its arguments are TRUE | AND(3=3;2=2) ⇒ TRUE |
| OR | OR(Boolean1;Boolean2; ... ) | Returns TRUE if any of its arguments are TRUE | OR(3=4;2=3) ⇒ FALSE |
| TRUE | TRUE() | Returns the logical value TRUE | IF(TRUE();1;2) ⇒ 1 |
| FALSE | FALSE() | Returns the logical value FALSE | IF(FALSE();1;2) ⇒ 2 |
| IF | IF(Test;Then;Else) | Specifies a logical test to perform
- inputs can be linked to data - result can be number, text, logical value |
Result is number (bold inputs linked to data X[0;1;2;3;4;5;6]):
IF(220>200;220-200;200-220) ⇒ 20 IF(120>MAX([X⇕]);120;-1) ⇒ 120 IF(MAX([X⇓2:⇓3])>[X⇓7];MAX([X⇓2:⇓3]);[X⇓7])) ⇒ 6 IF(MAX(⟨1;2⟩)>6;MAX(⟨1;2⟩);6) ⇒ 6 Result is text: Input [Coordinate Z] = 210;[GWT] = 180 IF([Coordinate Z]>0;"Above terrain";"Under terrain") ⇒ "Top above terrain" IF([GWT]<[Coordinate Z];"GWT under top of the test.";"Measure error.") ⇒ "GWT under top of the test." |
| IFS | IFS(Test1;Then1;Test2;Then2; ... ;Else) | Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition | Input [GWT] = 15
IFS([GWT]>8;10;[GWT]>6;9;[GWT]>3;8;-1) ⇒ 10 |
| NOT | NOT(Boolean) | Reverses the logic of its argument | IF(NOT(5>6);175;155) ⇒ 175
IF(NOT(5>4);175;155) ⇒ 155 IF(NOT("A"="B");175;155)⇒ 175 |
| SWITCH | SWITCH(Switch;Case1;Result1;Case2;Result2; ... ;[ Else ]) | Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned | SWITCH(50>6;TRUE();44;FALSE();56;-111) ⇒ 44
SWITCH(30/6;3;120;4;220;5;320;420) ⇒ 320 |
Mathematical
| Name | Syntax, parameters | Description | Example |
|---|---|---|---|
| ABS | ABS(Number) | Returns the absolute value of a number | ABS(120) ⇒ 120; ABS(-120) ⇒ 120 |
| ACOS | ACOS(Number) | Returns the arccosine of a number ⟨-Pi/2; Pi/2⟩ | for X=0.5 ... DEGREES(ACOS(0.5)) ⇒ 60° |
| ACOT | ACOT(Number) | Returns the arccotangent of a number ⟨0; Pi⟩ | for X=1 ... DEGREES(ACOT(1)) ⇒ 45° |
| ASIN | ASIN(Number) | Returns the arcsine of a number ⟨-Pi/2; Pi/2⟩ | for X=0.5 ... DEGREES(ASIN(0.5)) ⇒ 30° |
| ATAN | ATAN(Number) | Returns the arctangent of a number ⟨-Pi/2; Pi/2⟩ | for X=1 ... DEGREES(ATAN(1)) ⇒ 45° |
| COS | COS(Angle[rad]) | Returns the cosine of the given angle in rad | for α=60° ... COS(RADIANS(60)) ⇒ 0.5 |
| SIN | SIN(Angle[rad]) | Returns the sine of the given angle in rad | for α=30° ... SIN(RADIANS(30)) ⇒ 0.5 |
| COT | COT(Angle[rad]) | Returns the cotangent of an angle in rad | for α=45° ... COT(RADIANS(45)) ⇒ 1 |
| TAN | TAN(Angle[rad]) | Returns the tangent of an angle in rad | for α=45° ... TAN(RADIANS(45)) ⇒ 1 |
| EXP | EXP(Number) | Returns e raised to the power of a given number | EXP(1) ⇒ 2.71828 |
| LN | LN(Number) | Returns the natural logarithm of a number | LN(1)) ⇒ 0; LN(EXP(1)) ⇒ 1 |
| LOG | LOG(Number;[Base=10]) | Returns the logarithm of a number to a specified base (10 if missing) | LOG(1; 10) ⇒ 0; LOG(10; 10) ⇒ 1 |
| LOG10 | LOG10(Number) | Returns the base-10 logarithm of a number | LOG10(1) ⇒ 0; LOG10(10) ⇒ 1 |
| PI | PI() | Returns the value of pi | PI() ⇒ 3.141592653 |
| POWER | POWER(Base;Number) | Returns the result of a number raised to a power | POWER(2;4) ⇒ 16 |
| SQR | SQR(Number) | Returns the result of a squared number | SQR(5) ⇒ 25; SQR(-5) ⇒ 25 |
| SQRT | SQRT(Number) | Returns a positive square root of a positive Number | SQRT(36) ⇒ 6; SQRT(-36) ⇒ NAN |
| SUM | SUM(Number1;Number2; ... ) | Returns the sum of a series of numbers | Inputs linked to data X[0;1;2;3;4;5;6]):
SUM([X⇓2:⇓5]) ... SUM(⟨1; 2; 3; 4⟩) ⇒ 10 SUM([X⇕]) ⇒ 21 |
| DEG | DEGREES(Angle [rad]) | Converts radians into degrees | DEGREES(PI()) ⇒ 180°; DEGREES(PI()/2) ⇒ 90° |
| RAD | RADIANS(Number) | Converts degrees to radians | RADIANS(180) ⇒ π (3.141592653) |
| ROUND | ROUND(Number;Places) | Rounds a number to a specified number of digits
- negative Places round to the left of the decimal point |
ROUND(58.563;3) ⇒ 58.563
ROUND(58.563;2) ⇒ 58.56 ROUND(58.563;1) ⇒ 58.6 ROUND(58.563;0) ⇒ 59 ROUND(58.563;-1) ⇒ 60 ROUND(58.563;-2) ⇒ 100 |
| ROUNDUP | ROUNDUP(Number;Places) | Rounds a number up, toward zero | ROUNDUP(58.563;3) ⇒ 58.564
ROUNDUP(58.563;2) ⇒ 58.57 ROUNDUP(58.563;1) ⇒ 58.6 ROUNDUP(58.563;0) ⇒ 59 |
| ROUNDDOWN | ROUNDDOWN(Number;Places) | Rounds a number down, toward zero | ROUNDDOWN(58.563;3) ⇒ 58.563
ROUNDDOWN(58.563;2) ⇒ 58.56 ROUNDDOWN(58.563;1) ⇒ 58.5 ROUNDDOWN(58.563;0) ⇒ 58 ROUNDDOWN(58.563;-1) ⇒ 50 ROUNDDOWN(58.563;-2) ⇒ 0 |
Text
| Name | Syntax, parameters | Description | Example |
|---|---|---|---|
| CONCAT | CONCAT(Value1;Value2; ... ) | Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments
- Value1, Value2, ... can be as Text or Number |
CONCAT(12;34;56) ⇒ 123456.000
CONCAT("12";"34";"56") ⇒ 123456.000 CONCAT(12;"34";56) ⇒ 123456.000 Input [GWTb] = 15 CONCAT([GWTb];" m") ⇒ "15 m" |
| LEFT | LEFT(Text;[Count=1]) | Returns the leftmost characters from a text value | LEFT("qwert";3) ⇒ "qwe"
LEFT("qwert";1) ⇒ "q" |
| RIGHT | RIGHT(Text;[Count=1]) | Returns the rightmost characters from a text value | RIGHT("qwert";3) ⇒ "ert"
RIGHT("qwert";1) ⇒ "t" |
| LEN | LEN(Text) | Returns the number of characters in a text string | LEN("qwert") ⇒ 5
LEN("") ⇒ 0 |
| SEARCH | SEARCH(Find text;Within text;[Start=1]) | Finds one text value within another (not case-sensitive) | SEARCH("G";"Hello GEO!";1) ⇒ 7 |
Statistical
| Name | Syntax, parameters | Description | Example |
|---|---|---|---|
| AVERAGE | AVERAGE(Number1;Number2; ... ) | Returns the average of its arguments | AVERAGE(3;5;1) ⇒ 3
Inputs linked to data X[0;1;2;3;4;5;6]): AVERAGE([X⇓3:⇓5]) ... AVERAGE(⟨2; 3; 4⟩) ⇒ 3 |
| MAX | MAX(Number1;Number2; ... ) | Returns the maximum value in a list of arguments | MAX(3;5;1) ⇒ 5
Inputs linked to data X[0;1;2;3;4;5;6]): MAX([X⇓3:⇓5]) ... AVERAGE(⟨2; 3; 4⟩) ⇒ 3 |
| MIN | MIN(Number1;Number2; ... ) | Returns the minimum value in a list of arguments | MIN(3;5;1) ⇒ 1
Inputs linked to data X[0;1;2;3;4;5;6]): MIN([X⇓3:⇓5]) ... AVERAGE(⟨2; 3; 4⟩) ⇒ 2 |
| PERCENTIL | PERCENTILE(Array;k) | Returns the k-th percentile of values in a range | Inputs linked to data X[1;2;3;4;5]):
PERCENTILE([X⇕];0.5) ⇒ 3 |
| PERCENTILE.EXC | PERCENTILE.EXC(Array;k) | Returns the k-th percentile of values in a range, where k is in the range 0..1, exclusive | Inputs linked to data X[1;2;3;4;5]):
PERCENTILE.INC([X⇕];1) ⇒ NAN PERCENTILE.INC([X⇕];0.4) ⇒ 2.4 |
| PERCENTILE.INC | PERCENTILE.INC(Array;k) | Returns the k-th percentile of values in a range | Inputs linked to data X[1;2;3;4;5]):
PERCENTILE.INC([X⇕];1) ⇒ 5 |
| STDEV | STDEV(Number1;Number2; ... ) | Estimates standard deviation based on a sample | Inputs linked to data X[1;2;3;4;5]):
STDEV([X⇕]) ⇒ 1.581 |
| STDEV.P | STDEV.P(Number1;Number2; ... ) | Calculates standard deviation based on the entire population | Inputs linked to data X[1;2;3;4;5]):
STDEV.P([X⇕]) ⇒ 1.414 |
| STDEV.S | STDEV.S(Number1;Number2; ... ) | Estimates standard deviation based on a sample | Inputs linked to data X[1;2;3;4;5]):
STDEV.S([X⇕]) ⇒ 1.581 |
| MODE | MODE(Number1;Number2; ... ) | Returns the most common value in a data set | Inputs linked to data X[1;2;3;4;5]):
MODE(([X⇕]) ⇒ NAN MODE(([X⇕];3) ⇒ 3 |
| MEDIAN | MEDIAN(Number1;Number2; ... ) | Returns the median of the given numbers | MEDIAN(1;2;3;4;5;6;7) ⇒ 4 |
Matrix
| Name | Syntax, parameters | Description | Example |
|---|---|---|---|
| LINEARTREND | |||
| LINEARTRENDANDPOINTS | |||
| LINEARTRENDSC | |||
| LINEARTRENDSCANDPOINTS | |||
| LINEARTRENDTOPOINTS | |||
| SEMICIRCELTOPOINTS |