Calculated Column Custom Expression Details
- An expression must be a number, a data column, an appropriately formatted function, or a combination of these items: [5, “X”, sin(“X”), 5sin(“X”)]
- Supported operators are + – * / ^ ( )
- Functions must contain their arguments in parentheses. [abs(“X”) or sqrt(2)]
- Multiplication can be explicit and implied. [5*“X”or 5“X” or 5(“X”)]
- Constants must be entered as numbers. Variable parameters (e.g., A, B, C) are not supported.
- Trigonometric functions are evaluated in radians.
- Functions can be nested as long as the proper format is used. [sqrt(abs(“X”))]
Function Syntax
Below is a list of available functions and their syntax when using the Custom Expression option for Graphical Analysis Pro calculated columns.
Mathematical Functions
Basic mathematical functions. The arguments for these functions need to be a number, a mathematical expression that resolves to a number, or a column of numbers.
| Function | Description | Syntax: Columns | Syntax: Numbers | Notes: |
|---|---|---|---|---|
| acos | arccosine or inverse cosine | acos(“X”) | acos(0.5) ≈ 1.047 | Output is in radians |
| asin | arcsine or inverse sine | asin(“X”) | asin(0.5) ≈ .5236 | Output is in radians |
| atan | arctangent or inverse tangent | atan(“X”) | atan(1) ≈ .785 | Output is in radians |
| atan2 | 2-argument arctangent | atan2(“Y”,“X”) atan2(4,”X”) atan2(“Y”,3) |
atan2(4,3) ≈ .927 atan2(3,4) ≈ .644 |
The angle (in radians) between the x-axis and a line connecting the origin and the point (x,y) |
| cos | cosine | cos(“X”) | cos(1.407) ≈ 0.5 | Input is in radians |
| sin | sine | sin(“X”) | sin(.5236) ≈ 0.5 | Input is in radians |
| tan | tangent | tan(“X”) | tan(0.785398) ≈ 1 | Input is in radians |
| acosh | inverse hyperbolic cosine | acosh(“X”) | acosh(2) ≈ 1.317 | |
| asinh | inverse hyperbolic sine | asinh(“X”) | asinh(1) ≈ .4812 | |
| atanh | inverse hyperbolic tangent | atanh(“X”) | atanh(.5) ≈ .5493 | |
| cosh | hyperbolic cosine | cosh(“X”) | cosh(1.317) ≈ 2 | |
| sinh | hyperbolic sine | sinh(“X”) | sinh(.4812) ≈ 1 | |
| tanh | hyperbolic tangent | tanh(“X”) | tanh(.5493) ≈ .5 | |
| exp | Natural or base-e exponential function (ex) | exp(“X”) | exp(2) ≈ 7.389 | |
| exp2 | base-2 exponential function (2x) | exp2(“X”) | exp2(2) = 4 | |
| expm1 | ex–1 or exp(x)–1 | expm1(“X”) | expm1(.0005) ≈ 0.005012521 |
Greater accuracy for “x” values close to 0 compared to exp(x)-1. Inverse of log1p(x). |
| ln | Natural or base-e logarithmic function | ln(“X”) | ln(2.719) ≈ 1 | |
| log | common or base-10 logarithmic function | log(“X”) | log(1000) = 3 | |
| log2 | base-2 logarithmic function (log2x) | log2(“X”) | log2(8) = 3 | |
| log1p | ln(1+x) | log1p(“X”) | log1p(.0001) ≈ 0.000099995 | Greater accuracy for “x” values close to 0 compared to ln(1+x). Inverse of expm1(x) |
| logb | floating-point base logarithmic function | logb(“X”) | logb(0.065) = 2 logb(6.5) = 2 logb(65) = 6 |
Returns the logarithm of |x|, using FLT_RADIX as base for the logarithm. (FLT_RADIX = 2) |
| abs | absolute value | abs(“X”) | abs(-9.81) = 9.81 | |
| cbrt | cube root | cbrt(“X”) | cbrt(27) = 3 | |
| hypot | Hypotenuse – finds the square root of the sum of the squares of two numbers | hypot(“X”, “Y”) hypot(“X”, 12) hypot(5,”X”) |
hypot(5,12) = 13 | sqrt(x2 + y2) |
| pow | power (xy) | pow(“X”,“Y”) pow(“X”,3) pow(5,”X”) |
pow(5,3) = 125 | |
| sqrt | square root | sqrt(“X”) | sqrt(81) = 9 | |
| erf | error function or Gauss error function | erf(“X”) | erf(.25) ≈ 0.276 |  |
| erfc | Complementary error function | erfc(“X”) | erfc(.25) ≈ 0.724 | 1 – erf(x) |
| ceiling | Ceiling – finds the nearest integer greater than a given number. | ceiling(“X”) | ceiling(3.5) = 4 ceiling(-3.5) = -3 |
|
| floor | Floor – finds the nearest integer less than a given number | floor(“X”) | floor(3.5) = 3 floor(-3.5) = -4 |
|
| round | Rounds to the nearest integer | round(“X”) | round(3.5) = 4 round(-3.5) = -4 |
|
| trunc | Truncate – finds the nearest integer not greater than a given number. | trunc(“X”) | trunc(3.5) = 3 trunc(-3.5) = -3 |
|
| mod | Modulo – integer remainder of X/Y | mod(“X”,“Y”) mod(“X”,5) mod(8,”X”) |
mod(5,4) = 1 mod(6,4) = 2 mod(7,4) = 3 |
Equivalent to the expression… x – y*trunc(x/y) |
| remainder | ISEE version of the integer remainder of X/Y | remainder(“X”,“Y”) remainder(“X”,5) remainder(8,“X”) |
remainder(5,4) = 1 remainder(6,4) = -2 remainder(7,4) = -1 |
Equivalent to the expression… x – y*round(x/y) |
| copysign | Changes the sign of a number to match the sign of another number | copysign(“X”, “Y”) copysign(“X”, -1) copysign(1, “X”) |
copysign(-5, 1) = 5 copysign(5, -2) = -5 |
|
| nextafter | Computes next representable float after X in the direction of Y | nextafter(“X”,“Y”) | nextafter(1/0,1) = infinity | https://pubs.opengroup.org/onlinepubs/009695399/functions/nextafter.html |
| dim | Calculates the difference between two numbers and reports the difference if the value is ≥ 0 otherwise it reports 0. | dim(“X”,“Y”) dim(“X”,10) dim(10,“X”) |
dim(10,8) = 2 dim(10,9.9) = 0.1 dim(8,10) = 0 |
Digital Filter Functions
Digital Filter functions are designed to work with analog sensors and/or truly analog signals. Signals or sensors that perform digital signal processing may not work well with these features. The Digital Filter functions have the following parameters:
- “Y” – a column of data to which the filter is being applied
- “X” – the independent variable column for your data; typically Time
- bandPassRipple
- This value is the percentage of pass-band
- Low Pass and High Pass filters are typically 0, which applies a Butterworth pass filter
- Band Stop and Band Pass filters are typically 0.5 unless
- cutoff [lowCutoff, highCutoff] – the FFT frequency cutoff value used for the filter; must be a number
- timeDecay – time decay constant, typically in seconds
| Function | Description | Syntax | Notes |
|---|---|---|---|
| lowPassFilter | Filter data from analog sensors that have unwanted FFT frequencies above the cutoff value | lowPassFilter(“Y”,“X”,bandPassRipple, cutoff)
lowPassFilter(“Signal”,“Time”,0,50) |
For EKG data, use a 50 Hz low pass filter to remove noise from a power supply affecting your data. |
| highPassFilter | Filter data from analog sensors that have unwanted FFT frequencies below the cutoff value | highPassFilter(“Y”,“X”,bandPassRipple, cutoff)
highPassFilter(“Signal”,“Time”,0,1) |
For EMG data, use a 1 Hz high pass filter to remove noise from muscle movements such as a twitch. |
| bandStopFilter | Filter data from analog sensors that have unwanted FFT frequencies between two cutoff values. | bandStopFilter(“Y”, “X”, bandPassRipple, lowCutoff, highCutoff)
bandStopFilter(“Signal”, “Time”,0.5,50, 70) |
For voltage data, use a 50 to 70 Hz band pass filter to remove noise from a US power supply. |
| bandPassFilter | Use this function to apply both a low pass and high pass filter using a single expression. Frequencies below the low cutoff and frequencies above the high cutoff will be filtered from the data. | bandPassFilter(“Y”, “X”, bandPassRipple, lowCutoff, highCutoff)
bandPassFilter(“Signal”, “Time”,0.5,1, 50) |
For EKG or EMG data, filter frequencies below 1 Hz to remove noise from low frequency events, such as arm or leg movements, and filter frequencies above 50 hz to remove noise from a power supply. |
| timeDecayFilter | Use this function to apply a time decay to the data. | timeDecayFilter(“Y”, “X”, timeDecay) |
Calculus Functions
The Integral function calculates the numerical integral, the running sum of the ares of rectangles calculated using the midpoint rule. The ith rectangle is [Yi -Y(i-1)] / [Xi -X(i-1)]. The integral function has the following parameters:
- “Y” – a column of data you want to integrate
- “X” – the independent variable column for your data; typically Time
| Function | Description | Syntax | Notes |
|---|---|---|---|
| integral | Numerical Integral | integral(“Y”, “X”) |
Derivative functions are designed to find the numerical rate of change of a variable with respect to another variable, typically time. The numerical derivative is the weighted average of the slope of ‘n’ points around each point, where ‘n’ is the numberOfPoints parameter noted below. The Derivative functions have the following parameters:
- “Y” – a column of data you want to differentiate
- “X” – the independent variable column for your data; typically Time
- numberOfPoints- Optional value; must be a number.
- This is the number of points used to calculate the derivative.
- Supported values are 3 ,5, 7, 9, 11, 15, 21, 29, 39, 51, 65, 81, and 97.
- The default value, 7, is used unless otherwise specified.
- For more information on derivative calculations, see
How do Logger Pro, Graphical Analysis, LabQuest app, and Vernier Video Analysis calculate velocity and acceleration?
| Function | Description | Syntax | Notes |
|---|---|---|---|
| firstDerivative | Finds the first derivative of data in column Y with respect to column X. | firstDerivative(“Y”,“X”,numberOfPoints)
firstDerivative(“Position”,“Time”) |
The number of points is only required when you want to use a different number of points than the one set in Session Preferences. The default is 7. |
| firstDerivative TimeShift | The derivatives returned are adjusted to estimate values at the start of the timing interval, instead of the midpoint. | firstDerivativeTimeShift(“Y”, “X”)
firstDerivativeTimeShift(“Position”, “Time”) |
This function is specifically designed to be used with photogate and picket fence data. |
| secondDerivative | Finds the second derivative of data in column Y with respect to column X. | secondDerivative(“Y”,“X”,numberOfPoints)
secondDerivative(“Position”,“Time”) |
The number of points is only required when you want to use a different number of points than the one set in Session Preferences. The default is 7. |
| secondDerivative TimeShift | Calculates the second numerical derivative of “Y” with respect to “X”. The values are shifted so that the derivatives are calculated at the midpoints between each two values. | secondDerivativeTimeShift(“Y”, “X”)
secondDerivativeTimeShift(“Position”, “Time”) |
This function is specifically designed to be used with photogate and picket fence data. |
Statistical Functions
| Function | Description | Syntax | Notes |
|---|---|---|---|
| rms | Root Mean Square of the values in column “X” up to the current row. The output value reported for row 3 is calculated using the column “X” values from rows 1, 2 and 3. | rms (“X”) | |
| randReal | This function returns a column of random real numbers between min and max (inclusive). The number of rows in the returned column is determined by the value of ‘count’. | randReal (min, max, count)
randReal(-1,1.5,10) |
If ‘count’ is a column (e.g., “X”), then the number of rows in the output will be the number of rows in column “X”. |
| randInt | This function returns a column of random integers between min and max (inclusive). The number of rows in the returned column is determined by the value of ‘count’. | randInt (min, max, count)
randInt (0,5,10) |
If ‘count’ is a column (e.g., “X”), then the number of rows in the output will be the number of rows in column “X”. |
| max | Compares all the values in a single column, “X”, and reports the largest number in the column. | max (“X”) | The output is a column having one row. |
| max2 | Compares all the values in column, “X”, row by row, with values in column “Y” and outputs a column having the largest number for each row. | max2(“X”,”Y”) max2(“X”, 5) |
You can also use a constant instead of column “Y”. |
| min | Compares all the values in a single column, “X”, and reports the smallest number in the column. | min (“X”) | The output is a column having one row. |
| min2 | Compares all the values in column, “X”, row by row, with values in column “Y” and outputs a column having the smallest number for each row. | min2 (“X”, “Y”) min2 (“X”, 5) |
You can also use a constant instead of column “Y”. |
| mean | Arithmetic mean of the numbers in column “X”. | mean (“X”) | The output is a column having one row. |
| median | The median value of “X”. The values in “X” do not need to be sorted. | median (“X”) | The output is a column having one row. |
| stddev | The standard deviation of the numbers in a column “X”. | stddev (“X”) | The output is a column having one row. |
| numRows | This outputs a single value equal to the number of rows in column “X”. | numRows (“X”) | The output is a column having one row. |
Smoothing Functions
| Function | Description | Syntax | Notes |
|---|---|---|---|
| smoothAve | This function returns a column of moving averages of the values in “X”. | smoothAve(“X”) | The number of points used when calculating the moving average can be set in the Session Preferences. |
| smoothSG | Savitsky-Goley smoothing fits a polynomial to a number of points around each point and computes the value of the polynomial at that point. | smoothSG(“Y”,”X”,numberOfPoints)
smoothSG(“Position”,“Time”) |
The number of points is only required when you want to use a different number of points than the one set in Session Preferences. The default is 7. |
Manipulative Functions
| Function | Description | Syntax | Notes |
|---|---|---|---|
| value | Creates a new column based on column “X” by extracting values that are ‘offset’ rows from the current row. | value (offset, “X”) | Offset is from previous rows when n < 0 and from latter rows when n >0. |
| constant | This function generates a column filled with the value ‘constant’, where the number of rows is = the value ‘count’. | constant (constant, count)
constant (4, 5) |
If ‘count’ is a column (e.g., “X”), then the number of rows in the output will be the number of rows in column “X”. |
| integer | Extracts the integral part of values in “X”. | integer (“X”) | This function give the same result as trunc(“X”). |
| sum | The sum of the values in column “X” up to the current row. The output value reported for row 3 is the sum of the column “X” values from rows 1, 2 and 3. | sum (“X”) | |
| subset | Returns a column extracted from “X” starting with ‘startRow’ incremented by ‘step’. | subset (“X”, start, step) | For example, subset(“X”, 1, 2) will get every second row of “X” starting with row 1. |
| collapse | This removes all non-numerical cells (blanks and text) from a column. | collapse (“X”) | |
| collapseIndirect | This creates a new column containing no empty cells and having values from rows in column “X” that correspond to rows in column “Y” that are numbers. | collapseIndirect (“X”, “Y”) | |
| step | Generates a column having ‘count’ rows. The first row value is ‘start’ and subsequent rows are increased or decreased by ‘increment’. Optional parameters: ‘firstRow’ is the first non-empty row and ‘skip’ is the number of rows to skip between each value. | step (start, increment, count, firstRow, skip) | If ‘count’ is a column (e.g., “X”), then the number of rows in the output will be the number of rows in column “X”. |
| stepColumnBased | Generates a column based on non-empty values in column “X” starting with ‘start’ and incrementing by ‘increment.’ Optional parameters: ‘firstRow’ is the first non-empty row and ‘skip’ is the number of rows to skip between each value. | stepColumnBased (“X”, start, increment, firstRow, skip) | |
| interpolate | Generates a column where missing values in the “Y” column are filled in using linear interpolation. | interpolate (“Y”, “X”) | Column “X” is the independent variable, typically Time. |
| rowNumber | Provides a column of values where the cell value is the row number of the cell. | rowNumber() | The data table needs to have rows already defined. For a new Manual entry experiment, values in the calculated column are not defined unless there are values already entered in another column. |
Blood Pressure
The inputs for these calculations are the Pressure and Time columns from data collection with the wired (BTA) Blood Pressure Sensor.
| Function | Description | Syntax | Notes |
|---|---|---|---|
| systolic | The measured arterial pressure when the heart contracts | systolic (“Pressure”, “Time”) | The output is a single number |
| diastolic | The measured arterial pressure when the heart is at rest | diastolic (“Pressure”, “Time”) | The output is a single number |
| meanArterialPressure | The pressure value at the max peak used for blood pressure calculations | meanArterialPressure (“Pressure”, “Time”) | |
| pulse | Pulse rate calculated using the blood pressure sensor inputs. | pulse (“Pressure”, “Time”) | Pulse measured in beats per minute |
| oscillations | Oscillations of the peaks and valleys used for blood pressure calculations | oscillations (“Pressure”, “Time”) | |
| oscillatoryPeaks | Peaks used to calculate, systolic, diastolic, and pulse values | oscillatoryPeaks (“Pressure”, “Time”) | Relative maximum values from the oscillations data |
Miscellaneous/Analysis
| Function | Description | Syntax | Notes |
|---|---|---|---|
| delta | The difference between consecutive values from column “X”. (The ith value is ith value of “X” minus the (i-1)th value of “X”.) | delta (“X”) | The resulting column will not have a value in the first row. |
| rate | The rate of “Y” with respect to “X”, where t is the time interval measured, m1 is min percentage threshold, m2 is max percentage threshold, and n is noise threshold. dt is the offset to start of the next range. | rate (“Y”, “X”, t, m1, m2, n, dt) | Typical values: “X” is time column, t = 1/10 of the range, m1 = 40%, m2 = 60%, noise = 0, and dt ≤ t. |
| beatsPerMinute | This function is similar to the rate function except that the interval, t, is always in seconds and the returned value is always in minutes. |
beatsPerMinute(“Y”, “X”, t, m1, m2, n, dt) | If column “X” is in seconds, then beatsPerMinute(“Y”,”X”) = 60*rate(“Y”,”X”) |
| analysis | Appends data in rows “startRow” to “endRow” (including those rows) from all columns named “X” in the file into a single column of data. | analysis (“X”, startRow, endRow)
analysis(“X”,2,4) |
|
| dataSets | Creates a column of data where the row values are the data set names use in the file. “X” can be any column in the data set. | dataSets (“X”, startRow, endRow)
dataSets(“X”,2,4) |
The start/end row numbers are used to determine the number of times a data set name is repeated in successive rows. For example, dataSets(“X”,2,4) will repeat a dataset name three times. |