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 | D | |
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 | #REF! |
8 | Sample 8 | Fail | 1000 | #DIV/0! |
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)
COUNT: returns the number of cells that contain any non-empty values.
=COUNT(value1, [value2], [value3], …)
=COUNT(C2, C3, C4) = 3
ISBLANK: returns whether or not a range of cells are all blank
=ISBLANK(value1, [value2], [value3], …)
=ISBLANK(D1) = TRUE
=ISBLANK(D1:D2) = TRUE
=ISBLANK(C1:C2) = FALSE
ISERROR: returns whether or not any cells from a range of cells contains an error
=ISERROR(value1, [value2], [value3], …)
=ISERROR(D8) = TRUE
=ISERROR(B8:D8) = TRUE
=ISERROR(D7) = #REF