Create formulas in the Table Editor.
There are three types of columns you can create when you click the “Add Column” button in the Table Editor: Statistic, Keyword, and Formula (described below) columns. In each case you can name the column, define it as a control value, choose whether or not to display its values in the finished table, and format the output according to criteria of your choice.
In the Table Editor, click the Edit tab. In the Columns band, click “Add Column”. When the Column Information dialog appears, select the Formula tab:
Here you can construct a formula that 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 (as long as the statistics contributing to the equation are available).
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 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).
Next, insert a function (see the full list of all options at the bottom of this document):
See that the function has been appended to the name of your first inserted reference:
Finally, set a number or insert another column reference:
Click OK and your column definition will be added to the Table Editor. For help with other aspects of the “Add Column” tool, click here.
To change the characteristics of the column you have defined click on the “Edit Column” button in the Table Editor or double-click its row in the table.
Insert Reference – (Column references)
A column reference has the format , where “name” is a user-defined column name. This appears in the last column of the table definition window. Before a formula can use a table column, it must be assigned a custom name. It will then show up in the Insert Reference list. See this page for instructions on naming columns.
A column reference can also specify a specific row in the final spreadsheet. So a formula such as:
*2
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 it by 2.
Square brackets allow you to specify a particular population; for example, the formula:
*2
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, the formula:
/*100
gives an output value which is the percent of the control sample (first in the table).
Row references can also be “relative” to the current row. Thus, the formula:
-
will generate an output column where each cell represents the difference between the current and the previous row’s values for “MeanCD4”.
The formula:
-
is the same, but references the subsequent row in the final table.
Insert Function
Expand the Insert Function drop-down menu, shown below, to add function notation to your formula. There are 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(x) converts numbers in text form to arabic numerals and Num(x) converts arabic numerals to their text form.
Here is a complete list of the math functions available:
1. +, -, /, * : addition, subtraction, division, & multiplication, respectively
2. %: modulus; e.g., 7 % 2 = 1
Numeric functions:
3. Abs(x): returns absolute value of x
4. ceil: rounds up to the next whole number
5. floor: rounds down to the next whole number
6. Neg(x): returns the negative of x (or “-x”)
7. min(x1, x2, …): returns the minimum value of the 2 or more parameters
8. max(x1, x2, …): returns the maximum value of the 2 or more parameters
9. round: returns the rounded value
Transcendental functions:
10. pow(x): “e” raised to the power of x
11. exp(x): “e” raised to the power of x
12. Ln(x): the base-e log of x
13. Log(x): the base-10 log of x
14. sqrt(x): the square root of x
Trigonometric functions:
15. acos: Arc cosine
16. asin: Arc sine
17. atan: Arc tangent
18. cos: Cosine
19. cosh: Hyperbolic cosine
20. sin: Sine
21. sinh: Hyperbolic sine
22. tan: Tangent
23. tanh: Hyperbolic tangent
Textual (String) functions:
- Note that string constants must be specified in single quotes. Any text that is not in single quotes is considered to be a variable or table column reference!
24. +: concatenate; ‘Abc’ + ‘def’ = ‘Abcdef’
25. Char(x): converts a number to a string; char(123) = ‘123’
26. Cont(a, b): returns 1 if a contains b
27. Del(a, b, c): delete from a starting at position b with length c: del(‘abcd’, 2, 2) returns ‘ad’
28. End(a, b): returns 1 if a ends with b, otherwise 0
29. find(x, y): finds the first position of substring y in string x; find(‘abce’, ‘c’) = 3
30. Ins(a, b, c): insert b into a at position c; ins(‘abcd’, ‘x’, 2) returns ‘axbcd’
31. Len: returns the length of the string in number of characters
32. Lowr(a): returns the lowercase equivalent of a
33. Max(x1, x2, …): returns the alphabetically first of the parameters
34. Min(x1, x2, …): returns the alphabetically last of the parameters
35. Num(x): converts a string to a number; num(‘123’) = 123
36. Rep(a, b, c): replaces the first instance of b in a with c: rep(‘ababab’, ‘a’, ‘c’) returns ‘cbabab’
37. Repall(a, b, c): replaces all instances of b in a with c; repall(‘ababab’,’a’, ‘c’) returns ‘cbcbcb’.
38. Strt(a, b): returns 1 if a starts with b, otherwise 0; Strt(‘abcd’, ‘ab’) = 1
39. Sub(x, a, b): substring of string x, starting at position a, of length b’ sub(‘abc’ 2, 1) = ‘b’
40. 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’
41. Uppr(a): returns the uppercase equivalent of a
42. Word(x, a, b): selects word number a from string x, using b as a delimiter; word(‘this is fun’, 2, ‘ ‘) returns “is”
Logical functions:
- All of these functions return a 1 if true or a 0 if false.
43. <, >,<=, >=, =, != : thus, the expression “a < b” returns 1 if a is less than b; otherwise 0
44. &&: and a & b: if both a and b are nonzero, than 1; otherwise 0
45. ||: or a || b: if either a or b are nonzero, than 1; otherwise 0
46. !: not !A: if A = 0, than 1; otherwise 0
Conditional functions:
47. Ifthen(a,b,c): if expression a evaluates to nonzero, then return expression b, otherwise expression c
Tags: FlowJo