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










Did this answer your question?