abs
Return the absolute value of the supplied parameter.
abs(–12)=12;
abs(ColumnA)
acos
Return the inverse cosine of parameter1.
acos(–0.5)=2.094395
acosh
Calculate the inverse hyperbolic cosine of parameter1.
acosh(10)=2.99322
asin
Calculate the inverse sine of parameter1.
asin(–0.5)= –0.5236
asinh
Calculate the inverse hyperbolic sine of parameter1.
asinh(–2.5)= –1.6472311
atan
Calculate the inverse tangent of parameter1.
atan(1)=0.785398
atan2
Calculate the arc tangent of two coordinates.
atan2(x coord, y coord);
atan2(–1,1)= –2.3561945
atanh
Calculate the inverse hyperbolic tangent of a value.
atanh(0.76159416)=1
average
Return the average of the supplied parameters.
average(A1:A10);
average(12, 5, 7, 8, 11.4)
ceiling
Return the smallest integer value greater than, but not equal to, the value supplied.
ceiling(12.5)
chiinv
Calculate the inverse chi-square distribution
chiinv(p, df)
clean
Clean text of non-printable characters.
clean(“string with non-printable chars”);
clean(A1)
concatenate
Combine two or more strings into one string.
concatenate(A1:A5);
concatenate(ColumnA, “& “, ColumnB)
cos
Calculate the cosine of parameter1.
cos(1.047)=0.50017107
cosh
Calculate the hyperbolic cosine of parameter1.
cosh(4)=27.30823
count
Return the number of values in a cell range or column, including empty cells.
count(A1:A5)
counta
Return the number of non-empty cells in a given cell range or column.
counta(A1:A5)
date
Return the sequential serial number that represents a particular date.
date(2007, 11, 27)=39411
datevalue
Return the serial number of the date represented by date_text. Converts a date represented by text to a serial number. If time is associated with the date, then the serial date will be a decimal and not a whole number.
datevalue(“date_text”)
datevalue(“11/27/2017”)=39411
day
Return the day of a date represented by a serial number. Integer ranging from 1 to 31.
day(serial_time);
day(39411)=27
even
Round up for x >= 0 and round down for x < 0 to nearest even integer.
even(12.1)
even(ColumnA)
exp
Return e raised to the power of the supplied parameter.
exp(3);
exp(ColumnA)
fact
Return the factorial of the supplied parameter.
fact(3)
Finv
Calculates the quantile of F-distribution and returns the absolute value.
Finv(p,df1,df2);
Finv(0.7, 1, 10) = 1.194776
floor
Return the largest integer less than the supplied parameter.
floor(12.9)
format
Format a string using .NET string formatting commands.
format(ColumnA, “G8”);
format(“.12345”, “G8”)
geomean
Return the geometric mean of a range of positive data.
geomean(A1:A15)
geovar
Return the geometric variance of a range of positive data.
geovar(A1:A15)
harmean:
Return the harmonic mean of a range of data.
harmean(A1:B32)
harmvar
Return the harmonic variance of a range of data.
harvar(A1:C33)
hour
Return the hour of a time value. Integer ranging from zero (12:00 A.M.) to 23 (11:00 P.M.).
hour(serial_time);
hour(38711.06302)=1;
hour(37811.27135)=6
if
Return parameter2 if condition in parameter 1 is true; otherwise parameter3.
if(A1 =0.25), 1, 0)
Note that parameter 1 (logical test) must return true or false. Place space between cell reference and '='.
if(trt=”Reference”, “R”, “T”);
if(CVWR<=30, ‘125.00’, if(CVWR<=50, 100*exp(+0.76*sqrt(Estimate)), 143.19))
The “if” function also supports using & (and) and | (or) in statements and nested “if” statements.
if((Time=1) & (Conc>2),…);
if((Time=1) | (Conc>2),…);
IsNull
Return true if value of cell is NULL (empty).
IsNull(ColumnA)
left
Return the specified number of characters (from left to right) of a string.
left(“This is a test”, 1)=T;
left(“This is a test”, 3)=Thi;
left(A1, 3)
length
Return the length of a string.
length(“this is a test”)=14;
length(A1)
ln
Return the natural log of the supplied parameter.
ln(2.3);
ln(ColumnA)
log
Return the log of the first parameter to the base of the second parameter.
log(number, base);
log(5,3)
log10
Return the log base 10 of the parameter.
log10(5);
log10(ColumnA)
lower
Return the supplied string as lower case.
lower(“This Is A Test”)=“this is a test”;
lower(A1)
mid
Return a specific number of characters from a string from a given position and length parameters.
mid(text, start pos, distance);
mid(“This is a test”, 2, 3)=his
minute
Return minutes of a time value. Integer ranging from zero to 59.
minute(serial_time);
minute(38711.06302)=30
mod
Return remainder after parameter1 is divided by parameter2.
mod(number, divisor);
mod(12,2)
month:
Return month of a date represented by a serial number. Integer ranging from 1 (January) to 12 (December).
month(serial_time);
month(39411)=11
now
Return current date and time as a double (serial date/time).
now()=current date
odd
Round up for x >= 0 and round down or x < 0 to nearest odd integer.
odd(13.1)
odd(ColumnA)
pi
Return the value of pi.
pi()
product
Return the product of a list of real numbers.
product(A1:Z88)
rand
Return a random number based on a lower limit, an upper limit, and a seed.
rand(,);
rand(0,1,92756)
replace
Replace a sub-string with another string.
replace(text, start pos, distance, replace with);
replace(“T_%_025”, 2, 3, “–”)=T–025;
replace(ColumnA,1,4,””)
right
Return specific number of characters from a string starting from the right and moving left.
right(“This is a test”, 3)=“est”
root
Return root of a parameter1, using parameter2/parameter3.
x^(a/b);
root(x, a, b);
root(2, 3, 4)
round
Round parameter1 to the number of decimal places defined by parameter2.
round(12.345, 2)=12.35;
round(ColumnA,1)
roundsig
Round parameter1 to the number of significant digits defined by parameter2. Note that Phoenix worksheets do not support displaying trailing zeros in decimals, so not all significant digits will be displayed in this case.
roundsig(12.234, 3)=12.3
round(ColumnA,3)
search
Return one-based starting position of a substring within a larger string. The third argument is the one-based starting position for the search. The function returns –1 if there is no match.
search(“Phoenix”, “oe”, 1)=3
second
Return seconds of a time value. Integer in the range zero to 59.
second(38711.06302)=44
sign
Return sign of a number. 1 if positive, 0 if zero, or –1 if negative.
sign(–12);
sign(0);
sign(200)
sin
Return sine of parameter1.
sin(1)=0.84147098
sinh
Return hyperbolic sine of parameter1.
sinh(1)=1.175201194
sqrt
Return square root of parameter1.
sqrt(9)
stdev
Estimate standard deviation based on a range of data.
stdev(A1:C1)
strcmp
Return an integer indicating the case sensitive lexical relationship between the two comparands.
Value condition < zero, stringA is < stringB
Value condition = zero, stringA = stringB
Value condition > zero, stringA is > stringB
strcmp(stringA, stringB)
substitute
Substitute new text for old text in a text string and optionally [for a given instance]. [] is optional but the preceding comma is required; substitute(ColumnA,”_%_”,””,)
substitute(text, target text, replacement text, [instance])
When substituting question marks, the escape character ‘\’ has to be used before ‘?’ in the function or the process fails.
sum
Return sum of the specified values.
sum(A1:A12);
sum(1,2,3,4,5)
sumsq
Return sum of the squares of the cell range.
sumsq(C1:C33)
tan
Return tangent of parameter1.
tan(0.785)=0.99920399
tanh
Return hyperbolic tangent of parameter1.
tanh(–2)= –0.96403
time
Return decimal number for a particular time.
time(hour, min, sec);
time(12,2,3)=0.50142361
timevalue
Return decimal number of the time represented by a text string, with precision up to the second.
timevalue(“2:24 AM”)=0.1
tinv
Return absolute value of inverse t distribution.
tinv(p, df);
tinv(0.7, 10)=tinv(0.3, 10)=0.541528
today
Return serial number of the current date.
today()=current date as serial time
trim
Remove all spaces from text except for single spaces between words.
trim(“ this is a test”)=“this is a test”
trunc
Truncate a real number to an integer by removing the decimal places.
trunc(number);
trunc(12.1234)
upper
Return parameter1 in upper case.
upper(“this is a test”)=“THIS IS A TEST”
var
Return variance of a set of real numbers.
var(A1:A5)
weekday
Return day of the week corresponding to a date. Integer ranging from 1 (Sunday) to 7 (Saturday).
weekday(serial_time);
weekday(39411)=Monday (Sunday is the first day)
year
Return year corresponding to a date. Integer in the range 1900–9999.
year(39411)=2007
Legal Notice | Contact Certara
© Certara USA, Inc. All rights reserved.