Tables in Benchling have formulas that follow Excel functionality. And so Excel help documentation for these formulas will conveniently apply to Benchling. In this article you will find a list of common functions, each with a brief description and an equation example using the table of values below:
A | B | C | |
1 | Sample 1 | Fail | 1 |
2 | Sample 2 | Fail | 5 |
3 | Sample 3 | Fail | 10 |
4 | Sample 4 | Pass | 100 |
5 | Sample 5 | Fail | 1000 |
6 | Sample 6 | Fail | 1000 |
7 | Sample 7 | Fail | 1000 |
8 | Sample 8 | Fail | 1000 |
ABS: returns the absolute value of an integer or float number
- =ABS(number)
- =ABS(C2) = 5
CEILING: returns a value rounded up to a specified significance
- =CEILING(number, significance)
- =CEILING(C3, 10) = 10
- =CEILING(C3, 100) = 100
FLOOR: returns a value rounded down to a specified significance
- =FLOOR(number, significance)
- =FLOOR(C3, 10) = 10
- =FLOOR(C3, 100) = 0
LN: returns the natural log of a value
- =LN(number)
- =LN(C4) = 4.605
LOG: returns the log of a value
- =LOG(number)
- =LOG(C4) = 2
MOD: returns the remainder of a value-divisor pair
- =MOD(number, divisor)
- =MOD(C5, 7) = 6
ROUND: returns a value rounded to a defined number of digits
- =ROUND(number, num_digits)
- =ROUND(C5, 6) = 1000
SQRT: returns the square root of a value
- =SQRT(number)
- =SQRT(C4) = 10
SUM: returns the summation of numbers, including lists of numbers
- =SUM(number1, [number2], [number3], …)
- =SUM(C2, C3, C4) = 16
- =SUM(C2:C4) = 115
AVERAGE: returns the average of a set or list of numbers
- =AVERAGE(number1, [number2], …)
- =AVERAGE(C2:C4) = 38.33
MAX: returns the largest numeric value in a range of values
- =MAX(number1, [number2], ...)
- =MAX(C2:C4) = 100
MIN: returns the smallest numeric value in a range of values
- =MIN(number1, [number2], ...)
- =MIN(C2:C4) = 5
STDEV: returns the standard deviation of a set of data
- =STDEV(number1, [number2], ...)
- =STDEV(C2:C4) = 53.46
CONCATENATE:
- =CONCATENATE(text1, text2, [text3], ...)
- =CONCATENATE(A1, A2, A3) = “Sample 1Fail1”
LEN: returns the length of characters
- =LEN(text)
- =LEN(A1) = 8
MID: takes a text input, and returns the portion of text defined by the start position and number of characters to return
- =MID(text, start_num, num_chars)
- =MID(A1, 2,3) = “amp”
SUBSTITUTE: For a given field, text is replaced (all instances by default)
- =SUBSTITUTE(text, old_text, new_text, [instance])
- =SUBSTITUTE(B1, "Fail", "Pass") = “Pass”
Logical operator IF: used to test a condition and return a desired value; these can be nested and include the AND() and OR() functions for additional complexity
- IF(logical_test, [value_if_true], [value_if_false])
- IF(B4="Pass", "Done", "Rerun") = “Done”
Logical operator AND: All conditions within the function must be valid to equal TRUE, otherwise the value returned is FALSE
- =AND(Condition 1, Condition 2)
- =AND(B4="Pass", C4>100) = FALSE
Logical operator OR: If any condition within the function is valid, TRUE is returned; if all conditions are FALSE, then FALSE is returned
- =OR(Condition 1, Condition 2)
- =OR(B4="Pass", C4>100) = TRUE
LOOKUP: finds a value in a reference list, and returns the corresponding value in a second list
- =LOOKUP((lookup_value, lookup_vector, [result_vector])
- =LOOKUP(“Pass”, B1:B8, A1:A8) = Sample 4 (The function will lookup ‘Pass’ in B1-B8, then report the corresponding ‘A’ column value)