The following function are available for use in formulae. Functions accept variables, constants, expressions, or comma-separated lists of parameters depending on their requirements.
Base 10 logarithm of the value
A positive number is required
| Example | Result |
|---|---|
| log10(10.5) | 1.021189 |
| log10(20.3) | 1.307496 |
| log10(0.37978) | -0.420679 |
Base 10 antilog of the value
| Example | Result |
|---|---|
| alog10(1.021189) | 10.49999 |
| alog10(1.307496) | 20.3 |
| alog10(-0.420679) | 0.3795955 |
Natural logarithm of the value
A positive number is required
| Example | Result |
|---|---|
| log(10.5) | 2.351375 |
| log(20.3) | 3.010621 |
| log(0.37978) | -0.9681631 |
Base e antilog of the value
| Example | Result |
|---|---|
| alog(2.351375) | 10.5 |
| alog(3.010621) | 20.3 |
| alog(-0.9681631) | 0.37978 |
Logit to base 10 of the value
Value must be between 0 and <1
| Example | Result |
|---|---|
| Logit(0.123232) | -1.962174 |
| Logit(0.431232) | -0.2768264 |
| Logit(0.866734) | 1.872385 |
Sine of the angle
This function requires a value in radians. If your angle is in degrees convert it first using the radians function.
| Example | Result |
|---|---|
| sin(0.123232) | 0.1229203 |
| sin(0.431232) | 0.4179903 |
| sin(6.866734) | 0.5509889 |
Cosine of the angle
This function requires a value in radians. If your angle is in degrees convert it first using the radians function.
| Example | Result |
|---|---|
| cos(0.123232) | 0.9924166 |
| cos(0.431232) | 0.9084515 |
| cos(6.866734) | 0.8345126 |
Tangent of the angle
This function requires a value in radians. If your angle is in degrees convert it first using the radians function.
| Example | Result |
|---|---|
| tan(0.123232) | 0.1238596 |
| tan(0.431232) | 0.4601130 |
| tan(6.866734) | 0.6602523 |
Arcsine of the value in radians
Input value must be between -1 to +1, inclusive. If you want the result in degrees, use the degrees function afterwards.
| Example | Result |
|---|---|
| asin(0.1229203) | 0.123232 |
| asin(0.4179903) | 0.431232 |
| asin(0.5509889) | 0.5835488 |
Arccosine of the value in radians
Input value must be between -1 to +1, inclusive. If you want the result in degrees, use the degrees function afterwards.
| Example | Result |
|---|---|
| acos(0.1229203) | 1.447564 |
| acos(0.4179903) | 1.139564 |
| acos(0.5509889) | 0.9872475 |
Arctangent of the value in radians
If you want the result in degrees, use the degrees function afterwards.
| Example | Result |
|---|---|
| atan(0.1229203) | 0.1223068 |
| atan(0.4179903) | 0.3959184 |
| atan(0.5509889) | 0.5036021 |
The angle in degrees
Provide an angle in radians
| Example | Result |
|---|---|
| degrees(0.1229203) | 7.042814 |
| degrees(0.9179903) | 52.59697 |
| degrees(1.5048340) | 86.22064 |
The angle in radians
Provide an angle in degrees
| Example | Result |
|---|---|
| radians(0) | 0 |
| radians(45) | 0.7853982 |
| radians(90) | 1.570796 |
The square root of the value
A positive number is required
| Example | Result |
|---|---|
| sqr(4) | 2 |
| sqr(16) | 4 |
| sqr(3283) | 57.29747 |
The cubed root of the value
A positive number is required
| Example | Result |
|---|---|
| croot(4) | 1.587401 |
| croot(16) | 2.519842 |
| croot(3283) | 14.86245 |
The e to the power of the value. The number e, also known as Euler's number, is a mathematical constant approximately equal to 2.71828 which can be characterised in many ways. It is the base of the natural logarithms.
The numbers returned by Exp can get very large very quickly. Values above 88 will result in "Infinity" being returned.
| Example | Result |
|---|---|
| exp(1) | 2.718282 |
| exp(2) | 7.389056 |
| exp(10) | 22026.46 |
The nearest highest whole number
| Example | Result |
|---|---|
| ceiling(10.5) | 11 |
| ceiling(-10.5) | -10 |
| ceiling(-95.5834) | -95 |
The nearest lower whole number
Negative numbers also round to a lower number
| Example | Result |
|---|---|
| floor(10.5) | 10 |
| floor(-10.5) | -11 |
| floor(-95.5834) | -96 |
The number rounded to the nearest whole number.
If the fractional part of the number is 0.5 or above will be rounded up, values less than 0.5 round down
| Example | Result |
|---|---|
| round(10.5) | 11 |
| round(10.49) | 10 |
| round(-10.5) | -11 |
The sign of the number
-1 for negative values, for 0 a zero value and +1 for positive values
| Example | Result |
|---|---|
| sign(10.5) | +1 |
| sign(0) | 0 |
| sign(-10.5) | -1 |
The absolute value of the number, ie its positive equivalent
| Example | Result |
|---|---|
| abs(10.92) | +10.92 |
| abs(0) | 0 |
| abs(-10.29) | 10.29 |
A random integer taken from a uniform distribution between 0 and <Max
The results of this function will change each time the workbook is recalculated. If you want a fixed set of random numbers apply this formula, allow the results to be generated and then disable the formula. This will fix the current values in the column.
| Example | Result |
|---|---|
| random(1000) | 52 597 265 95 473 179 834 180 417 688 553 117 |
A random number taken from a Gaussian or normal distribution. The resultant data would have a mean of 0 and a standard deviation of 1.
This function does not require a parameter. To set your own mean and standard deviation use the following formula Gaussian(0)*SD+Mean.
The results of this function will change each time the workbook is recalculated. If you want a fixed set of random numbers apply this formula, allow the results to be generated and then disable the formula. This will fix the current values in the column.
| Example | Result |
|---|---|
| gaussian(0) | -0.1319901 -1.475542 -0.1804773 -0.7937815 -0.8656253 0.1076434 -0.8452033 -0.284827 1.064022 -0.1710456 -2.044435 1.241533 -0.7431112 -0.8190027 -0.9442617 0.3572066 |
The row number of the current row
This function does not require a parameter.
This function can be used with mathematical operators to generate pretty much any sequence of numbers.
| Example | Result |
|---|---|
| RowNumber(0) | 1 2 3 4 5 6 7 8 9 10 |
| Example | Result |
|---|---|
| RowNumber(0)+9 | 10 11 12 13 14 15 16 17 18 19 |
| Example | Result |
|---|---|
| RowNumber(0)*10 | 10 20 30 40 50 60 70 80 90 100 |
The numerical day of the month (from 1 to 31) for a given date value.
This function isolates the day component from a dedicated date column or a date literal. It operates independently of the month or year.
| Example | Result |
|---|---|
| Day( #1 Jun 2020# ) | 1 |
| Day( #2026-08-11# ) | 11 |
The numerical month of the year (from 1 to 12) for a given date value.
This function translates calendar months directly into integers (e.g., January returns 1, December returns 12). This format is ideal for sorting and grouping data during seasonal statistical evaluations.
| Example | Result |
|---|---|
| Month( #1 Jun 2020# ) | 6 |
| Month( #2026-08-11# ) | 8 |
The four-digit numerical year (e.g., 2026) for a given date value.
This function extracts the complete, century-accurate year integer. It avoids truncation errors to ensure your trend analyses remain precise across long-term studies.
| Example | Result |
|---|---|
| Year( #1 Jun 2020# ) | 2020 |
| Year( #2026-08-11# ) | 2026 |
The day of the week for a given date as text.
You must provide a date to this function. It can be either a column of type Date or a literal date value, delimited by ## symbols, for example #1 Jun 2020#.
| Example | Result |
|---|---|
| DayOfWeek( #1 Jun 2020# ) | Wednesday |
The day of the year for a given date as a number.
You must provide a date to this function. It can be either a column of type Date or a literal date value, delimited by ## symbols, for example #1 Jun 2020#.
| Example | Result |
|---|---|
| DayOfYear( #1 Jun 2020# ) | 153 |
The week of the year for a given date as a number.
You must provide a date to this function. It can be either a column of type Date or a literal date value, delimited by ## symbols, for example #1 Jun 2020#.
| Example | Result |
|---|---|
| WeekOfYear( #1 Jun 2020# ) | 23 |
A dedicated calendar interval structure representing a specific duration of years, months, and days.
This function builds a fixed calendar offset value. It is primarily used within formulas to shift date baselines or to evaluate if an individual's follow-up window falls within a specific chronological range.
| Example | Result |
|---|---|
| DateSpan( 1, 6, 0 ) | 1 Year 6 Months. |
| DateSpan( 0, 0, 14 ) | 14 Days |
| DateSpan( 0, 0, 4 ) | A DateSpan of 4 days. |
| DateSpan( 1, 2, 3 ) | A DateSpan of 1 year, 2 months, 3 days. |
| #11 Aug 2026# + DateSpan( 0, 1, 0 ) | A Date of 11 Sep 2026. |
The hour part of a given time.
You must provide a time to this function. It can be either a column of type Time or a literal time value, delimited by ## symbols, for example #10:02:03#.
| Example | Result |
|---|---|
| Hour( #10:02:03# ) | 10 |
The minute part of a given time.
You must provide a time to this function. It can be either a column of type Time or a literal time value, delimited by ## symbols, for example #10:02:03#.
| Example | Result |
|---|---|
| Minute( #10:02:03# ) | 2 |
The second part of a given time.
You must provide a time to this function. It can be either a column of type Time or a literal time value, delimited by ## symbols, for example #10:02:03#.
| Example | Result |
|---|---|
| Second( #10:02:03# ) | 3 |
An absolute time duration structure representing a length of elapsed clock hours, minutes, and seconds.
This function records pure physical durations. It allows you to track total incubation periods, animal behavioral trials, or machine operational logs.
Because Date and Time are separate data types, they cannot communicate automatically during calculations. Adding a TimeSpan to a Time column will never alter or update your Date column.If a TimeSpan calculation passes midnight (24:00:00), the Time column will wrap around to the new clock time, but the calendar Date remains completely unaffected.
| Example | Result |
|---|---|
| TimeSpan( 1, 0, 0 ) | A TimeSpan of 1 hour. |
| TimeSpan( 0, 2, 0 ) | A TimeSpan of 2 minutes. |
| TimeSpan( 0, 0, 4 ) | A TimeSpan of 4 seconds. |
| TimeSpan( 1, 2, 3 ) | A TimeSpan of 1 hour, 2 minutes, 3 seconds. |
| #01:00:00# + TimeSpan( 1, 2, 3 ) | A Time of 02:02:03. |
The leftmost 'number' characters of a given piece of text.
You must provide Text and a number to this function. It can be either a column of type Text or a literal text value, delimited by "" symbols, for example "Hello world!".
| Example | Result |
|---|---|
| Left( "Hello world!", 3 ) | Hel |
The rightmost 'number' characters of a given piece of text.
You must provide Text and a number to this function. It can be either a column of type Text or a literal text value, delimited by "" symbols, for example "Hello world!".
| Example | Result |
|---|---|
| Right( "Hello world!", 3 ) | ld! |
The middle of the text from start, the first character being number 1, for the specified length for a given piece of text.
You must provide Text and two numbers to this function. The text can be either a column of type Text or a literal text value, delimited by "" symbols, for example "Hello world!".
| Example | Result |
|---|---|
| Middle( "Hello world!", 4, 5 ) | lo wo |
A True/False value indicating whether a specific sequence of characters exists anywhere inside a text string.
Unlike BeginsWith or EndsWith, this function scans the entire string from left to right. It will return True if the search sequence appears at the start, middle, or end of the text. The evaluation is case-insensitive.
| Example | Result |
|---|---|
| Contains("Plate1,WellA01,0.452", "WELL") | True |
| Contains("Plate1,WellA01,0.452", "plate") | True |
A True/False value indicating whether a text string starts with a specific sequence of characters.
This function checks for a match at the very beginning of your data. The evaluation is case-insensitive, meaning uppercase and lowercase letters are treated as identical. For example, SERUM, Serum, and serum will all match.
| Example | Result |
|---|---|
| BeginsWith("SERUM-04B", "SERUM") | True |
| BeginsWith(Field("WT-04-RepB", 1, "-"), "wt") | True |
A True/False value indicating whether a text string finishes with a specific sequence of characters.
This function checks for a match at the far right edge of your data string. The evaluation is case-insensitive, ignoring differences in capitalization.
| Example | Result |
|---|---|
| EndsWith("WT-04-RepB", "RepB") | True |
| EndsWith("WT-04-RepB", "REPB") | True |
A specific segment of text from a string that is split by a separating character.
You must provide a text string and the position of the segment you want to extract (starting at 1).
The third parameter is optional. If you do not specify a delimiter, the function automatically splits the text by spaces. If your data uses commas, hyphens, or underscores to separate values, pass that character as the third parameter enclosed in quotes.
If the field_number is higher than the number of segments actually present in the text, the function will return an empty text value.
Consecutive delimiters (such as ,,), leading delimiters (such as ,A), and trailing delimiters (such as B,) are treated as containing empty fields. This ensures your positional columns remain properly aligned when missing values occur at the edges or middle of your text data.
| Example | Result |
|---|---|
| Field("Serum Baseline GroupA", 2) | Baseline |
| Field("WT-04-ReplicateB", 3, "-") | ReplicateB |
| Field("Plate1,WellA01,0.452", 2, ",") | WellA01 |
| Field("Control_Treatment", 5, "_") | <blank> |
| Field("A,,B", 2, ",") | <blank> |
| Field(",A,B", 2, ",") | A |
| Field("A,B,", 2, ",") | <blank> |
| Field("WT-04-ReplicateB", 3, "-") | ReplicateB |
The text string with all alphabetical characters converted to lowercase.
This function maps all uppercase letters (including international characters and accented letters like É or Ö) into their lowercase equivalents. Mathematical symbols, numbers, punctuation, and constants (like the degree ° symbol) remain unchanged.
| Example | Result |
|---|---|
| Lowercase("MÜNCHEN") | münchen |
| Lowercase("BATCH-04B") | batch-04b |
This function converts lowercase characters to capitals across all UTF-8 alphabets (such as Latin, Greek, or Cyrillic).
Scientific Data Warning: In rare instances, transforming a character to uppercase can change the length of your text string. For example, the German character ß expands into two characters (SS) when capitalized. If you are relying on exact character indices with the Field or Middle functions, perform your case shifts after extracting your data.
| Example | Result |
|---|---|
| Uppercase("35µL_serum") | 35µL_SERUM |
| Uppercase("große") | GROSSE |
The text string with the first letter of every separate word capitalised, and all other letters converted to lowercase.
Words are defined as groups of characters separated strictly by space characters. The function identifies the first character of each token and capitalizes it—including international characters—while forcing the trailing letters to lowercase.
| Example | Result |
|---|---|
| Titlecase("échantillon control") | Échantillon Control |
| Titlecase("ALPHA-α BLOCK")) | Alpha-α Block |
A Text value. Every occurrence of Text 2 contained within Text 1 is replaced with Text 3.
You must provide three Text values to this function. Each text can be either a column of type Text or a literal text value, delimited by "" symbols, for example "Hello world!".
| Example | Result |
|---|---|
| ReplaceAll( "Hello world!", "o", "P" ) | HellP WPrld! |
| ReplaceAll( "Hello world!", "l", " POP " ) | He POP POP o Wor POP d! |
| ReplaceAll( "Hello world!", "ll", "-" ) | He-p World! |
The text string with all leading and trailing space characters completely removed.
This function targets only the blank spaces at the very beginning or the very end of your data string. It does not alter single spaces between words inside the text.
Using Trim is a highly recommended safety step before parsing text columns with the Field function. It ensures that accidental spaces do not leave invisible padding or shift your data alignment columns.
| Example | Result |
|---|---|
| Trim( " Serum Sample A " ) | Serum Sample A |
| Trim("Control Group") | Control Group |
| Field(Trim(" Batch1 , Well2 "), 1, ",") | Batch1 |
A True/false value which is the opposite of that provided, ie False if True is passed, True if False is passed.
You must provide a Text value to this function. The text can be either a column of type Text or a literal text value, delimited by "" symbols, for example "Hello world!".
| Example | Result |
|---|---|
| Not( "hello world!", 1 ) | Hello World! |
Either option1 or option2, depending upon the value of the first input.
You must provide a True/False value and two other inputs, which must be of the same type, to this function. The true/false value can be either a function that returns a true/false value or a literal value of either True or False. Input 2 and 3 must be of the same type and can be either Numbers, Text values, Dates, Times, DateSpans or TimeSpans. When returning a DateSpan or a TimeSpan you can use it to add to a Date or Time accordingly and store that result.
| Example | Result |
|---|---|
| if( True, "Man", "Woman" ) | "Man" |
| if( False, "Man", "Woman" ) | "Woman" |
| if( Column_1 = 1, "Male", "Female" ) | "Man" in any row that is 1, "Woman" in the others. |
The value passed as a number.
You can provide a Text value, Date, Time or Code List value. It will return the value as a number.
| Example | Result |
|---|---|
| Number( "1.23" ) | 1.23 |
| Number( "1e-3" ) | 0.001 |
| Number( "1e6" ) | 1000000 |
| Number( #11:15# ) | 11.25 |
| Number( #11:30# ) | 11.5 |
The value passed as a number.
You can provide a Number, Date, Time or Code List value. It will return the value as Text.
| Example | Result |
|---|---|
| Text( 1.23 ) | "1.23" |
| Text( 1e-3 ) | "0.001" |
| Text( 1e6 ) | "1e6" |
| Text( #1 Jan 2020# ) | "1 Jan 2020" |
| Text( #11:15# ) | "11.15" |
| Text( #1 Day 3 Months# ) | "1 Day 3 Months" |
A formal Date value converted from a text string or an Excel-compatible serial day number.
This function translates text or numerical data into an internal Date structure.
| Example | Result |
|---|---|
| Date("2026-08-11") | #11 Aug 2026# |
| Date("11/08/2026") | #11 Aug 2026# |
| Date( "1 Jan 2020" ) | 1 Jan 2020 |
| Date(44000) | #18 Jun 2020# |
A formal Time value converted from a text string or a decimal hour value.
This function extracts or sets a clean, clock-based timeline marker.
| Example | Result |
|---|---|
| Time("14:30:00") | #14:30:00# |
| Time(11.5) | #11:30:00# |
| Time(9.75) | #09:45:00# |
Function reference