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: 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 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