# Common Table Formulas

Aarthi
• Updated

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

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.

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”

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

Have more questions? Submit a request

<% if (previousArticle || nextArticle) { %>
<% if (previousArticle) { %>
<% if (previousTitle) { %>

#### <%= previousTitle %>

<% } %> <%= previousArticle.title %>
<% } %> <% if (nextArticle) { %>
<% if (nextTitle) { %>

#### <%= nextTitle %>

<% } %> <%= nextArticle.title %>
<% } %>
<% } %>
<% if (items.length > 1) { %>