At times, applying various operations to your data can help extract meaningful insights. In this guide, we'll walk you through the steps of adding a formula column to your labsheets/sheets. This special column is a useful tool for building calculated columns in your scientific databases. Please find all the supported formulas in the Formula Syntax Reference File attached at the end of this article.
Step 1: Open your labsheet, and edit the column that you want as a 'formula' column. Alternatively, add a new column if the column doesn't exist. Make sure it is a 'Free text' or 'Number' column. Formulas are not supported for other types of columns.
Step 2: Enter the required formula in the formula field.
Remember, when adding the formula, start with an equal (=) sign, then use the formula command in parentheses ( ), and reference the column name in square brackets [ ]. Make sure the column names inside [ ] resemble the actual column name in your Labsheet.
Note: Make sure the input columns for your formulas belong to the same Labsheet as your output.
You can also directly type your scientific notation inside Labsheet or sheets (Eg, 1.2e+3)
Verify the formula syntax and then hit 'Save'
Here are the most common formulas that customers write on their Labsheets in Scispot:
Function | Formula | Explanation |
Mean | =AVERAGE([Column_Name]) | Calculates the average of all the values in the specified column. |
Median | =MEDIAN([Column_Name]) | Finds the middle value in a set of numbers that has been arranged in ascending or descending order. |
Variance | =VAR([Column_Name]) | Measures the squared deviations from the mean of a set of numbers. |
Sum | =SUM([Column_Name]) | Adds up all the values in the specified column. |
Minimum | =MIN([Column_Name]) | Finds the smallest value in the specified column. |
Maximum | =MAX([Column_Name]) | Finds the largest value in the specified column. |
Range | =MAX([Column_Name]) - MIN([Column_Name]) | Calculates the difference between the largest and smallest values in the specified column. |
Interquartile Range | =QUARTILE([Column_Name], 0.75) - QUARTILE([Column_Name], 0.25) | Measures the variability of a set of data by calculating the difference between the 75th and 25th percentiles. |
Correlation | =CORREL([Column_Name1], [Column_Name2]) | Measures the strength and direction of the linear relationship between two variables. |
Covariance | =COVARIANCE([Column_Name1], [Column_Name2]) | Measures the degree to which two variables vary together. |
P-value for T-Test | =T.TEST([Group1_Column_Name], [Group2_Column_Name], tails=2) | Calculates the probability of obtaining a t-statistic as extreme or more extreme than the observed result, assuming that the null hypothesis is true. |
Rank | =RANK([Column_Name], [Column_Name], 0) | Assigns a rank to each value in the specified column, where the lowest value is ranked 1 and the highest value is ranked n. |
Q-value | =ADJUST([P-value_Column_Name], method="BH") | Adjusts the p-values for multiple comparisons using the Benjamini-Hochberg procedure. |
ANOVA | =F.ONEWAY([Group1_Column_Name], [Group2_Column_Name], [Group3_Column_Name]) | Performs an analysis of variance to compare the means of two or more groups. |
Log | =LOG10([Column_Name]) | Calculates the base-10 logarithm of each value in the specified column. |
Log2 fold change | =LOG2([Condition1_Column_Name]) - LOG2([Condition2_Column_Name]) | Measures the change in expression levels between two conditions by calculating the difference in log2 values. |
Average | =AVERAGE([Column_Name]) | Calculates the average of all the values in the specified column. |
Standard Deviation | =STDEV([Column_Name]) | Measures the variability of a set of numbers by calculating the square root of the average squared deviations from the mean. |
Z-score | =(ABS([Column_Name]) - AVERAGE([Column_Name])) / STDEV([Column_Name]) | Standardizes a value by calculating its number of standard deviations away from the mean. |
Pearson Correlation Coefficient | =CORREL([Column_Name1], [Column_Name2]) | Measures the strength and direction of the linear relationship between two variables. |
Spearman Rank Correlation Coefficient | =CORREL([Column_Name1], [Column_Name2], type="Spearman") | Measures the strength and direction of association between two ranked variables. |
Kendall Tau Correlation Coefficient | =CORREL([Column_Name1], [Column_Name2], type="Kendall") | Measures the strength and direction of association between two variables. |
Division | =[Column_Name1] / [Column_Name2] | Calculates the quotient of two numbers. |
Multiplication | =[Column_Name1] * [Column_Name2] | Calculates the product of two numbers. |
Addition | =[Column_Name1] + [Column_Name2] | Calculates the sum of two numbers. |
Subtraction | =[Column_Name1] - [Column_Name2] | Calculates the difference of two numbers. |
Greater Than | =[Column_Name1] > [Column_Name2] | Compares two numbers and returns TRUE if the first number is |
Less Than | =[Column_Name1] < [Column_Name2] | Compares two numbers and returns True if the first number is less than the second number |
Quantile (25th percentile) | =quantile([Column_Name], 0.25) | Value below which 25% of the data falls |
Quantile (50th percentile) | =quantile([Column_Name], 0.5) | Median value |
Quantile (75th percentile) | =quantile([Column_Name], 0.75) | Value above which 25% of the data falls |
To convert a number string to scientific notation on your Labsheet setup, you can follow these steps:
Step-by-Step Guide:
Add a Column:
In your Labsheet, add a new column. This can be a free text or a number-type column.
Apply the Scientific Notation Formula:
Use the following formula in the newly added column. Replace
[column name]with the actual name of the column containing the numbers you wish to convert.
=(LEFT([column name], 1) & "." & MID([column name], 2, 2) & "e" & (LEN([column name]) - 1))
Example:
Replace
[column name]with your actual column identifier. For example, if your column name is 'Cell Density', the formula would be:
=(LEFT([Cell Density], 1) & "." & MID([Cell Density], 2, 2) & "e" & (LE([Cell Density]) - 1))
This will transform numbers like "12345" into "1.23e4".
Formula Locking
Formula Locking is a new feature that ensures formula-generated values remain consistent across both the UI and API responses. The new Formula Lock button allows you to finalize and synchronize column values before they become searchable or filterable.
Unlocked formulas (default):
Formula columns start in an unlocked state and are highlighted in amber.Locking a formula column:
When editing a column, click the padlock icon next to the formula to lock it. Once locked, the column cannot be unlocked.Locked formulas:
Locked formula columns are highlighted in grey and become fully searchable and filterable.
Formula Syntax Reference File
Please find all the supported formulas in the PDF attached below.