Formulas in Tables
If you select the Function button in the Table Editor, you will see the following...
Here you can construct a formula the output of which will appear as a column in your finished table. The calculation will be applied to each population through which the Table Editor iterates when it builds the table. It allows users to create a numerical,
logical or programmatic relationship between existing Table Editor columns, much like a spreadsheet editor's “reference calculations”.
The pre-requisites for this functionality are:
– one or more columns in the Table Editor
– a custom “column name” - this is used as an “alias” in creating the formulas.
You can include values appearing in other columns in your formula by including the column name in your formula. The names of other columns in the table editor are listed in the Insert Reference to Column drop-down box. Click on the column name you want to include in your formula. The column name will be inserted in the formula window.
A table formula can be any operation on other columns in the table (only columns which do not depend on the current column can be chosen; i.e., you can’t have recursive relationships).
Column references
A column reference has the format “
A column reference can also specify a specific row in the final spreadsheet. So a formula such as:
will generate a new column in the table whose values are always twice the value in the “MeanCD4”
column. For each row in the output table, the formula computes the value of “MeanCD4” column in the current row, and multiplies by 2.
Square brackets allow you to specify a particular population, the formula:
will generate a column where all of the values are twice the first entry in the “MeanCD4” column. Let’s assume the first row in the output table is the control sample, then:
Gives an output value which is the percent of control sample (first in the table).
Row references can also be “relative” to the current row. Thus:
will generate an output column where each cell represents the difference between the current and the
previous row’s values for “MeanCD4”.
is the same, but references the subsequent row in the final table.
Insert Function
Select the Insert Function drop-down menu shown at the left, to add function notation to your formula. There is a wide variety of functions that can be used in FlowJo Table Editor formulas. Some of these functions operate on numbers, and some on text. FlowJo is reasonably clever at converting strings to numbers and vice versa if needed, but there are two functions that do this explicitly just in case. char -> converts numbers in text form to arabic numerals and num -> converts arabic numerals to their text form.
Simple math functions:
+, -, /, * Are self-explanatory.
%
= modulus: e.g., 7 % 2 = 1.
^
= exponentiation: 2 ^ 3 = 8.
Statistics functions:
avg(x1,
x2, …); sd(x1, x2, …); cv(x1, x2, …). These functions can have 2 or more parameters. Each parameter can be a column reference, a constant, or an expression. Example: “avg(
Numeric functions:
min(x1,
x2, …); max(x1, x2, ….). These return the minimum or maximum value of the 2 or more parameters.
Abs(x): returns absolute value of x.
%: Modulus
Neg(x): returns the negative of x (or “-x”).
Transcendental functions:
sqrt(x) is the square root of x.
exp(x) is “e” raised to the power of x.
Ln(x) is the base-e log of x.
Log(x) is the base-10 log of x.
Text (String) functions:
Note that string constants must be specified in double quotes. Any text that is not in double quotes is considered to be a variable or table column reference!
+: concatenate. “Abc” + “def” = “Abcdef”
sub(x, a, b): substring of string x, starting at position a, of length b. sub(“abc”, 2, 1) = “b”
find(x, y): finds the first position of substring y in string x: find(“abce”, “c”) = 3.
Char(x): converts a number to a string: char(123) = “123”
Num(x): converts a string to a number: num(“123”) = 123
Word(x, a, b): selects word number a from string x, using b as a delimiter: word(“this is fun”, 2, “ “) returns “is”.
Repl(a, b, c): replace all instances of b in a with c: repl(“ababab”, “a”, “c”) returns “cbcbcb”.
Rep1(a, b, c): replace the first instance of b in a with c: rep1(“ababab’, “a”, “c”) returns “cbabab”.
Len: returns the length of the string in number of characters
Del(a, b, c): delete from a starting at position b with length c: del(“abcd”, 2, 2) returns
Ins(a, b, c): insert b into a at position c. ins(“abcd”, “x”, 2) returns “axbcd”. “ad”.
Strt(a, b): returns 1 if a starts with b, otherwise 0. Strt(“abcd”, “ab”) = 1
End(a, b): returns 1 if a ends with b, otherwise 0.
Cont(a, b): returns 1 if a contains b
Uppr(a): returns the uppercase equivalent of a
Lowr(a): returns the lowercase equivalent of a
Trun(a, b): truncates a to a length of b characters. If b is less than zero, then delete the last “-b” characters from a. Trun(“abc”, -1) = “ab”.
Max(x1, x2, …); Min(x1, x2, …): return the alphabetically last or first of the parameters
Comparator functions:
All of these functions return a 1 if true or a 0 if false.
<, >,≤, ≥, =, ≠
Thus, the expression “a < b” returns 1 if a is less than b, otherwise zero.
Boolean functions:
& : and: a & b: if both a and b are nonzero, then 1, otherwise 0
| : or: a | b: if either a or b are nonzero, then 1, otherwise 0
!
: not: !A: if A = 0, then 1, otherwise 0
Programmatic functions:
If(a,b,c): If the expression a evaluates to nonzero, then return expression b, otherwise expression c
For(a, b, c, d, e): Evaluate a; then evaluate b: if b returns non zero, then evaluate expression d (the main loop); then evaluate expression c; then go back to test expression b again
to decide whether or not to loop. Finally, return expression e as the result. See below for example. A for loop will quit after 1000 iterations unless a variable “MaxLoops” has been defined with a different
value (to prevent bad coded infinite loops)
Var(v, e): Set the variable v to have the value e.
(semicolon): use a semi-colon to separate expressions; the right-most expression value is the result. Thus: “3;4;5” returns a value of 5.
Example for loop: For(var(c,1); var(x, 0), x < 3, var(x, x+1), var(c, c*2), c)
This loop begins by evaluating the expression “var(c,1); var(x, 0)”; i.e., it defines two variables, “c” and “x”, giving them values of 1 and 0 respectively.
It then evaluates the loop control expression, (“x < 3”), which returns a value of 1 (since 0 < 3)—meaning the loop should be executed.
The end-of-loop expression “var(c, c*2)” is evaluated, which sets c to be twice the value of c, or 2.
The end of loop expression is evaluated “var(x, x+1)”, which sets the value of x to be x+1, or assigns it to be 1.
Then the loop control is evaluated again—and the looping continues until x is incremented to 3, by which time c has a value of 4. Now the loop control expression tests false, and looping is terminated.
Now the last expression is evaluated; “c”, which returns the value of 4. Thus, the output of this loop expression is 4.
Extra functions:� The following functions are available but are not shown in the menu:
Rand(x, y): generates a random number between 0 and y with uniform distribution.� (y can be omitted; Rand(x) is the same as Rand(x, 1)).
Norm(x, y): Random number with a normal distribution, between 0
and y (Norm(x) is the same as Norm(x,1)).
Sin,
Cos, Tan, asin, acos, atan, sinh, cosh, tanh: trig functions operating on (x).
Select the Create Formula button to add a new column to the Table Editor. When the Table Editor creates your table, you can have results appear in bold or in color if they exceed a desired threshold. For information on this, click here.