Common table formulas

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

Math, numbers, and statistics

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

FACT: returns the factorial of a number

• =FACT(number)

• =FACT(4) = 24

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

POWER: returns the power of a number raised to a power

• =POWER(number, number)

• =POWER(4, 3) = 64

• =POWER(2, 0) = 1

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

Note: Benchling uses the sample SD formula. It's not currently possible to switch to the population SD formula.

SLOPE: calculates the slope of a set of points using linear regression

• =SLOPE(known_ys, known_xs)
• =SLOPE(C2:C4,C1:C3) = 10.861

INTERCEPT: calculates the y-intercept of a set of points using linear regression

• =INTERCEPT(known_ys, known_xs)
• =INTERCEPT(C2:C4,C1:C3) = -19.590

Text and strings

CONCATENATE: link together words or numbers

• =CONCATENATE(text1,text2,text3,...)

• =CONCATENATE(A1,A2,A3) = 123

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”

TEXTJOIN: Combines the text from multiple strings and/or arrays, with a specified delimiter.

• =TEXTJOIN(delimiter, skip_blanks, text1, [text2],...)

• =TEXTJOIN("-",TRUE,A1:D1) = “Sample 1-Fail-1”

Logic and lookups

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 or XLOOKUP: finds a value in a reference list, and returns the corresponding value in a result list.  If any cell contains a reference error or formula error in either the lookup list or the result list, then this function will output an error.  

• =LOOKUP(lookup_value, lookup_list, [result_list])

• =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 numbers

• =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

Binary Operators

Any value or reference can be used with a binary operator in order to produce a boolean (TRUE or FALSE).

These operators can be used directly, like =C2<6, or inside of other formulas, like =IF(C2<6, 'a', 'b'), to produce boolean values.

These operators abide by the following rules:

• All empty cells are treated as an empty string value: ''
• All strings are greater than numbers
• No string is greater than or less than any other string

Benchling supports the following operators:

= Equals: Returns true if the two sides of the operator are exactly equal

• C2=5 is TRUE
• A2='Sample 2' is TRUE
• 1=5 is FALSE
• A1=A2 is FALSE

<> Not equals: Returns true if the two sides of the operator are not exactly equal

• C2<>5 is FALSE
• A2<>'Sample 2' is FALSE
• 1<>5 is TRUE
• A1<>A2 is TRUE

< Less than: Returns true if the left operator is less than the right operator

• C2<5 is FALSE
• 3<C2 is TRUE
• 1<5 is TRUE
• A2<'Sample 2' is FALSE

<= Less than or equal to: Returns true if the left operator is less than or equal to the right operator

• C2<=5 is TRUE
• 3<=C2 is TRUE
• 1<=5 is TRUE
• A2<='Sample 2' is FALSE

> Greater than: Returns true if the left operator is greater than the right operator

• C2>5 is FALSE
• 7>C2 is TRUE
• 5>1 is TRUE
• A2>'Sample 2' is FALSE

>= Greater than or equal to: Returns true if the left operator is greater than or equal to the right operator

• C2>=5 is TRUE
• 7>=C2 is TRUE
• 5>=1 is TRUE
• A2>='Sample 2' is FALSE