Prism provides a comprehensive library of mathematical, statistical, and logical functions that you can use when defining custom nonlinear regression models and creating calculated variables with in-table formulas on multiple variables data tables. This reference documents every available function, organized by category to help you find what you need.

Where You Can Use These Functions

Most functions work in both contexts - when writing user-defined equations for nonlinear regression and when creating formulas in Multiple Variables tables. A few functions are context-specific. Random number generators (GAUSS and RND) and aggregate functions that operate on entire variables (like MEAN, STDEV, and the single-variable forms of MAX, MIN, and SUM) are only available with created variables from in-table formulas, since they wouldn't make sense in the context of fitting a regression model.

How to Read This Reference

Each function entry shows its syntax, explains what it does, provides examples, and notes any important details or limitations. When functions accept optional parameters, those parameters are shown with default values. If you omit an optional parameter, Prism uses its default value. Optional parameters must be specified in order - you can't skip earlier optional parameters to provide later ones.

Function names aren't case-sensitive. You can write SIN, sin, or Sin - they all work identically. The same applies to the logical operators AND, OR, and NOT, which can be written in any case.

Throughout this reference, you'll see notes about Excel and R equivalents. These are included to help users familiar with those environments translate their knowledge to Prism. In most cases, the functions work identically, but there are occasional differences in argument order or behavior that are specifically called out.

Important Behaviors and Limitations

Functions generally operate element-wise on columns or variables, processing each row independently. When arithmetic operations encounter blank cells, they propagate the blank - that is, any calculation involving a blank value returns a blank result. Functions return blank values rather than errors when given invalid inputs, such as taking the square root of a negative number or calculating the logarithm of zero.

The maximum representable numeric value in Prism is approximately 1.655 × 10³⁰⁸. Calculations that would exceed this limit return blank values. This limit matches the IEEE 754 double-precision standard used by most computing platforms.

All trigonometric functions use radians for angles, not degrees. If you're working with degrees, use the RAD function to convert them before passing them to trigonometric functions, or use DEG to convert radians back to degrees for display.

Operators and Precedence

Prism supports standard arithmetic operators: + for addition, - for subtraction, * for multiplication, / for division, and ^ for exponentiation. The MOD operator returns the remainder after division and must be written in infix form, like "10 MOD 3" rather than as a function call.

Comparison operators include = (equals), <> (not equals), < (less than), > (greater than), <= (less than or equal), and >= (greater than or equal). These work with both numbers and text. String comparisons are not case-sensitive.

Logical operators can be written either as functions or using infix notation. You can write "AND(x > 0, x < 100)" or "x > 0 AND x < 100" - both forms work identically. The same applies to OR and NOT.

When multiple operators appear in the same expression, Prism evaluates them according to standard mathematical precedence: exponentiation first, then multiplication and division (left to right), then addition and subtraction (left to right), then comparison operators, and finally logical operators (NOT, then AND, then OR). Use parentheses to control evaluation order when needed.

Understanding Distribution Function Tail Types

Several statistical distribution functions accept a tail_type parameter that controls which probability is calculated. Understanding these options helps you use the functions correctly for statistical tests and probability calculations.

The "left" tail type calculates the cumulative probability from negative infinity up to your specified value - essentially P(X ≤ x). This is the standard cumulative distribution function. The "right" tail type calculates the probability from your value to positive infinity - P(X ≥ x). This is the complement of the left tail, equal to 1 minus the left-tail probability.

The "one" tail type returns the smaller of the two tail probabilities, which is always 0.5 or less. For symmetric distributions like the normal or t distribution, this gives you the one-tailed P value for a hypothesis test. At the mean of a symmetric distribution, the one-tail probability is exactly 0.5.

The "two" tail type returns twice the smaller tail probability. This gives you the two-tailed P value commonly used in hypothesis testing, where you're testing whether a value differs from the mean in either direction.

Basic Arithmetic and Rounding

These functions handle fundamental mathematical operations beyond what operators provide. They're particularly useful for controlling precision, handling signs, and performing basic calculations.

ABS(x)

Returns the absolute value of a number, stripping away any negative sign. ABS(5) equals 5, ABS(-3.7) equals 3.7, and ABS(0) equals 0. This is useful when you care about magnitude but not direction - for instance, when calculating distances or when you need to ensure a value is positive.

Excel equivalent: ABS(x) | R equivalent: abs(x)

CEIL(x)

Rounds a number up to the nearest integer. CEIL(2.1) and CEIL(2.9) both return 3. For negative numbers, it rounds toward zero, so CEIL(-2.5) returns -2. Think of it as always moving away from negative infinity.

Excel equivalent: CEILING(x) | R equivalent: ceiling(x)

FLOOR(x)

Rounds a number down to the nearest integer. FLOOR(2.9) and FLOOR(2.1) both return 2. For negative numbers, it rounds away from zero, so FLOOR(-2.5) returns -3. This is the opposite behavior of CEIL.

Excel equivalent: FLOOR(x) | R equivalent: floor(x)

INT(x)

Truncates a number to an integer by simply removing the decimal portion. INT(3.5) returns 3, INT(-2.3) returns -2, and INT(7.9) returns 7. Unlike FLOOR and CEIL, INT doesn't round - it just chops off everything after the decimal point.

Excel equivalent: TRUNC(x) | R equivalent: as.integer(x) or trunc(x)

ROUND(x, digits)

Rounds a number to a specified number of decimal places using standard rounding rules - values of 0.5 or greater round up. ROUND(3.14159, 2) returns 3.14, ROUND(2.5, 0) returns 3. You can use negative values for digits to round to the left of the decimal point - ROUND(1234.567, -1) returns 1230.

Excel equivalent: ROUND(x, digits) | R equivalent: round(x, digits)

a MOD b

Returns the remainder after dividing a by b. This uses infix syntax - you write "10 MOD 3" rather than "MOD(10, 3)". The result is 1 because 10 divided by 3 is 3 with remainder 1. The result takes the sign of the divisor (b), so 10 MOD -3 returns -2. Returns blank when b equals 0.

Excel equivalent: MOD(a, b) | R equivalent: a %% b

SGN(x)

Returns the sign of a number: 1 for positive values, -1 for negative values, and 0 for zero. SGN(42) returns 1, SGN(-17.3) returns -1, SGN(0) returns 0. This function doesn't return a value for blank cells.

Excel equivalent: SIGN(x) | R equivalent: sign(x)

SQR(x)

Returns the square of a number. SQR(5) returns 25, SQR(-3) returns 9, SQR(2.5) returns 6.25. You can also use the ^ operator for the same result - "x^2" produces identical output to "SQR(x)".

Excel equivalent: x*x or POWER(x,2) | R equivalent: x^2

SQRT(x)

Returns the square root of a number. SQRT(25) returns 5, SQRT(2) returns approximately 1.414, SQRT(0) returns 0. Returns blank for negative values since the square root of a negative number isn't defined for real numbers.

Excel equivalent: SQRT(x) | R equivalent: sqrt(x)

MAX(x, y, ...) or MAX(Variable)

Returns the maximum value from a set of numbers. When you provide multiple values like MAX(5, 8, 12, 3, 9), it returns 12. When comparing row-by-row across columns in transforms like MAX(VarA, VarB, VarC), it returns the largest value in each row. When used with a single variable like MAX(VarB), it broadcasts the maximum value from that entire column to every row. In user-defined equations, only the multi-value form is available.

Excel equivalent: MAX(x, y, ...) | R equivalent: max(x, y, ...) or max(x)

MIN(x, y, ...) or MIN(Variable)

Returns the minimum value from a set of numbers. MIN(5, 8, 12, 3, 9) returns 3. MIN(-3, -7) returns -7. Like MAX, it can work row-by-row across columns or broadcast a single minimum value when given one variable. In user-defined equations, only the multi-value form is available.

Excel equivalent: MIN(x, y, ...) | R equivalent: min(x, y, ...) or min(x)

SUM(x, y, ...) or SUM(Variable)

Returns the sum of values. SUM(2, 3, 5) returns 10. SUM(1.5, -2.5, 3) returns 2. When used row-by-row, it adds values across columns. When used with a single variable, it broadcasts the total sum to every row. In user-defined equations, only the multi-value form is available.

Excel equivalent: SUM(x, y, ...) | R equivalent: sum(x)

FACT(n)

Returns the factorial of a non-negative integer. The factorial of n is the product of all positive integers from 1 to n. FACT(5) returns 120 (calculated as 5×4×3×2×1). FACT(0) returns 1 by mathematical convention. FACT(3) returns 6. This function only accepts non-negative integers.

Excel equivalent: FACT(n) | R equivalent: factorial(n)

Trigonometric Functions

Trigonometric functions relate angles to ratios of sides in right triangles, but they're useful far beyond geometry - they appear in wave phenomena, periodic processes, and many physical models. Remember that all angles are in radians unless you convert them using RAD or DEG.

SIN(x)

Returns the sine of an angle in radians. SIN(0) returns 0, SIN(RAD(90)) returns 1. If you have an angle in degrees, convert it first with RAD.

Excel equivalent: SIN(x) | R equivalent: sin(x)

COS(x)

Returns the cosine of an angle in radians. COS(0) returns 1, COS(RAD(90)) returns 0 (or very close to it - computers can't represent all values exactly).

Excel equivalent: COS(x) | R equivalent: cos(x)

TAN(x)

Returns the tangent of an angle in radians. TAN(0) returns 0, TAN(RAD(45)) returns 1. The tangent is undefined at odd multiples of π/2 (like 90°, 270°), where it would involve division by zero.

Excel equivalent: TAN(x) | R equivalent: tan(x)

ARCSIN(x)

Returns the arcsine (inverse sine) of a value, giving you the angle whose sine is x. The input must be between -1 and 1, and the result is an angle in radians between -π/2 and π/2. ARCSIN(0) returns 0, ARCSIN(1) returns approximately 1.5708 (which is π/2). Returns blank if the absolute value of x is greater than 1.

Excel equivalent: ASIN(x) | R equivalent: asin(x)

ARCCOS(x)

Returns the arccosine (inverse cosine) of a value. The input must be between -1 and 1, and the result is an angle in radians between 0 and π. ARCCOS(1) returns 0, ARCCOS(0) returns approximately 1.5708 (π/2). Returns blank if the absolute value of x is greater than 1.

Excel equivalent: ACOS(x) | R equivalent: acos(x)

ARCTAN(x)

Returns the arctangent (inverse tangent) of a value. Unlike ARCSIN and ARCCOS, this accepts any real number and returns an angle in radians between -π/2 and π/2. ARCTAN(0) returns 0, ARCTAN(1) returns approximately 0.7854 (π/4).

Excel equivalent: ATAN(x) | R equivalent: atan(x)

ARCTAN2(y, x)

Returns the angle from the positive X-axis to the point (x, y), handling all four quadrants correctly. This is better than plain ARCTAN for working with coordinates because it preserves information about which quadrant the point is in. The result is in radians between -π and π. ARCTAN2(0, 1) returns 0, ARCTAN2(1, 0) returns approximately 1.5708 (π/2), ARCTAN2(1, 1) returns approximately 0.7854 (π/4). Critical note: Prism uses (y, x) argument order, while Excel reverses this to (x, y).

Excel equivalent: ATAN2(x, y) [WARNING: Arguments reversed!] | R equivalent: atan2(y, x)

SINH(x)

Returns the hyperbolic sine of a value, calculated as (e^x - e^-x)/2. Hyperbolic functions appear in solutions to differential equations and in models of hanging cables or hyperbolic geometry. SINH(0) returns 0, SINH(1) returns approximately 1.1752.

Excel equivalent: SINH(x) | R equivalent: sinh(x)

COSH(x)

Returns the hyperbolic cosine of a value, calculated as (e^x + e^-x)/2. COSH(0) returns 1, COSH(1) returns approximately 1.5431.

Excel equivalent: COSH(x) | R equivalent: cosh(x)

TANH(x)

Returns the hyperbolic tangent of a value. Unlike regular tangent, hyperbolic tangent is defined everywhere and always returns values between -1 and 1, making it useful for modeling saturation effects. TANH(0) returns 0, TANH(1) returns approximately 0.7616.

Excel equivalent: TANH(x) | R equivalent: tanh(x)

ARCSINH(x)

Returns the inverse hyperbolic sine. Unlike ARCSIN, this accepts any real number. ARCSINH(0) returns 0, ARCSINH(1.1752) returns approximately 1.

Excel equivalent: ASINH(x) | R equivalent: asinh(x)

ARCCOSH(x)

Returns the inverse hyperbolic cosine. The input must be greater than or equal to 1. ARCCOSH(1) returns 0, ARCCOSH(1.5431) returns approximately 1. Returns blank if x is less than 1.

Excel equivalent: ACOSH(x) | R equivalent: acosh(x)

ARCTANH(x)

Returns the inverse hyperbolic tangent. The input must be strictly between -1 and 1 (not including -1 or 1 themselves). ARCTANH(0) returns 0, ARCTANH(0.7616) returns approximately 1. Returns blank if the absolute value of x is greater than or equal to 1.

Excel equivalent: ATANH(x) | R equivalent: atanh(x)

RAD(degrees)

Converts an angle from degrees to radians. RAD(180) returns approximately 3.14159 (π), RAD(90) returns approximately 1.5708 (π/2). Use this before passing degree measurements to trigonometric functions.

Excel equivalent: RADIANS(degrees) | R equivalent: degrees * pi/180

DEG(radians)

Converts an angle from radians to degrees. DEG(3.14159) returns approximately 180, DEG(1.5708) returns approximately 90. Use this to convert results from trigonometric functions back to degrees for display or interpretation.

Excel equivalent: DEGREES(radians) | R equivalent: radians * 180/pi

Exponential and Logarithmic Functions

Exponential and logarithmic functions are fundamental to growth models, decay processes, and many statistical distributions. They're inverses of each other - exponential functions rapidly increase, while logarithms compress large ranges of values.

EXP(x)

Returns e raised to the power of x, where e is Euler's number (approximately 2.71828). EXP(0) returns 1, EXP(1) returns approximately 2.71828. This function grows very rapidly - EXP(709.7) returns approximately 1.655×10³⁰⁸, which is near Prism's maximum representable value. EXP(709.8) returns blank due to overflow.

Excel equivalent: EXP(x) | R equivalent: exp(x)

LN(x)

Returns the natural logarithm (base e) of x. This is the inverse of EXP. LN(2.71828) returns approximately 1, LN(1) returns 0. The natural logarithm is only defined for positive numbers, so LN returns blank for x less than or equal to zero.

Excel equivalent: LN(x) | R equivalent: log(x)

LOG(x) or LOG10(x)

Returns the base-10 logarithm of x. LOG(100) returns 2, LOG(1000) returns 3. LOG and LOG10 are identical - they're two names for the same function. Like LN, this returns blank for x less than or equal to zero.

Excel equivalent: LOG10(x) | R equivalent: log10(x)

LOG2(x)

Returns the base-2 logarithm of x. This is particularly useful in information theory and computer science. LOG2(8) returns 3, LOG2(1024) returns 10. Returns blank for x less than or equal to zero.

Excel equivalent: LOG(x, 2) | R equivalent: log2(x)

LOGIT(p)

Returns the log-odds (logit transformation) of a probability. The logit is calculated as ln(p/(1-p)) and is the inverse of the logistic function. This transformation is central to logistic regression. LOGIT(0.5) returns 0, LOGIT(0.75) returns approximately 1.0986. The input must be strictly between 0 and 1 - LOGIT is undefined at exactly 0 or 1.

Excel equivalent: LN(p/(1-p)) | R equivalent: qlogis(p)

Statistical Distribution Functions

Statistical distribution functions calculate probabilities, quantiles, and probability densities for common probability distributions. These are the building blocks of statistical inference and appear throughout hypothesis testing, confidence intervals, and probability modeling.

Normal Distribution

The normal (Gaussian) distribution is the familiar bell curve. It's symmetric around its mean, and about 68% of values fall within one standard deviation of the mean, 95% within two standard deviations.

NORMDIST(x, mean, sd, tail_type)

Returns cumulative probability from a normal distribution. The mean parameter (default 0) sets the center, and sd (standard deviation, default 1) controls the spread. The tail_type (default "left") determines which probability is calculated. NORMDIST(1.96, 0, 1, "left") returns 0.975 - about 97.5% of a standard normal distribution falls below 1.96. NORMDIST(1.96, 0, 1, "two") returns 0.050, giving you the two-tailed P value used in hypothesis testing.

Excel equivalent: NORM.DIST(x, mean, sd, TRUE) | R equivalent: pnorm(x, mean, sd)

NORMINV(p, mean, sd, tail_type)

Returns the quantile (inverse cumulative distribution function) corresponding to a probability p. This is the inverse of NORMDIST. NORMINV(0.5, 0, 1, "left") returns 0 - the median of a standard normal distribution. NORMINV(0.975, 0, 1, "left") returns approximately 1.96. Use this to find critical values for confidence intervals.

Excel equivalent: NORM.INV(p, mean, sd) | R equivalent: qnorm(p, mean, sd)

NORMPDF(x, mean, sd)

Returns the probability density at x for a normal distribution. This gives you the height of the bell curve at a particular point. NORMPDF(0, 0, 1) returns approximately 0.3989, the peak height of a standard normal curve. Use this for plotting distributions or in maximum likelihood calculations.

Excel equivalent: NORM.DIST(x, mean, sd, FALSE) | R equivalent: dnorm(x, mean, sd)

Standard Normal (Z) Distribution

The standard normal distribution is just the normal distribution with mean 0 and standard deviation 1. Z-scores are values from this distribution, and they represent how many standard deviations away from the mean an observation falls.

ZDIST(z, tail_type)

Returns cumulative probability from the standard normal distribution. This is equivalent to NORMDIST with mean=0 and sd=1. ZDIST(1.96, "left") returns 0.975, ZDIST(1.96, "two") returns 0.050.

Excel equivalent: NORM.S.DIST(z, TRUE) | R equivalent: pnorm(z, 0, 1)

ZINV(p, tail_type)

Returns the z-score corresponding to a probability. ZINV(0.975, "left") returns approximately 1.96, ZINV(0.025, "right") also returns approximately 1.96 since these represent the same point on opposite tails.

Excel equivalent: NORM.S.INV(p) | R equivalent: qnorm(p, 0, 1)

ZPDF(z)

Returns the probability density at z for the standard normal distribution. ZPDF(0) returns approximately 0.3989, the peak of the curve. ZPDF(1) returns approximately 0.2420.

Excel equivalent: NORM.DIST(z, 0, 1, FALSE) | R equivalent: dnorm(z, 0, 1)

Student's t Distribution

The t distribution is similar to the normal distribution but with heavier tails, making it more appropriate when working with small samples or unknown population standard deviations. As degrees of freedom increase, it approaches the normal distribution.

TDIST(t, df)

Returns the upper-tail probability (right tail) for a t-statistic with df degrees of freedom. This gives you P(T ≥ t), commonly used in t-tests. TDIST(2.086, 20) returns 0.025 - about 2.5% of the distribution exceeds 2.086. TDIST(0, 10) returns 0.5 since zero is at the center of the symmetric distribution.

Excel equivalent: T.DIST.RT(t, df) | R equivalent: pt(t, df, lower.tail=FALSE)

TINV(p, df)

Returns the t-value corresponding to a two-tailed probability p with df degrees of freedom. TINV(0.05, 20) returns approximately 2.086 - this is the critical value for a 95% confidence interval with 20 degrees of freedom. TINV(0.01, 10) returns approximately 3.169.

Excel equivalent: T.INV.2T(p, df) | R equivalent: qt(p/2, df, lower.tail=FALSE)

TPDF(t, df)

Returns the probability density at t for a t distribution with df degrees of freedom. TPDF(0, 10) returns approximately 0.3891. Use this for plotting t distributions.

Excel equivalent: T.DIST(t, df, FALSE) | R equivalent: dt(t, df)

Chi-Square Distribution

The chi-square distribution appears in tests for variance, goodness-of-fit tests, and tests of independence. It's always positive and right-skewed, becoming more symmetric as degrees of freedom increase.

CHIDIST(x2, df)

Returns the upper-tail probability for a chi-square statistic x2 with df degrees of freedom. This gives you P(χ² ≥ x2). CHIDIST(3.841, 1) returns 0.05 - about 5% of chi-square values with 1 degree of freedom exceed 3.841. CHIDIST(5.991, 2) returns 0.05.

Excel equivalent: CHISQ.DIST.RT(x2, df) | R equivalent: pchisq(x2, df, lower.tail=FALSE)

CHIINV(p, df)

Returns the chi-square value corresponding to an upper-tail probability p with df degrees of freedom. CHIINV(0.05, 1) returns approximately 3.841. CHIINV(0.05, 5) returns approximately 11.071. Use this to find critical values for chi-square tests.

Excel equivalent: CHISQ.INV.RT(p, df) | R equivalent: qchisq(1-p, df)

CHISQC(x, df)

Returns the cumulative probability (left tail) for a chi-square value x with df degrees of freedom. This gives you P(χ² ≤ x), which is the complement of CHIDIST. CHISQC(3.841, 1) returns 0.95. CHISQC(0, 5) returns 0.

Excel equivalent: CHISQ.DIST(x, df, TRUE) | R equivalent: pchisq(x, df, lower.tail=TRUE)

F Distribution

The F distribution is the ratio of two chi-square distributions, used primarily in ANOVA and for comparing variances. It has two sets of degrees of freedom - one for the numerator and one for the denominator.

FDIST(f, df1, df2)

Returns the upper-tail probability for an F-statistic with df1 numerator degrees of freedom and df2 denominator degrees of freedom. This gives you P(F ≥ f), used in ANOVA tables and F-tests. FDIST(4.0, 5, 10) returns approximately 0.0299. FDIST(1.0, 10, 10) returns 0.5.

Excel equivalent: F.DIST.RT(f, df1, df2) | R equivalent: pf(f, df1, df2, lower.tail=FALSE)

FINV(p, df1, df2)

Returns the F-value corresponding to an upper-tail probability p. FINV(0.05, 5, 10) returns approximately 3.326. FINV(0.01, 1, 20) returns approximately 8.096. Use this to find critical values for F-tests.

Excel equivalent: F.INV.RT(p, df1, df2) | R equivalent: qf(1-p, df1, df2)

FPDF(f, df1, df2)

Returns the probability density at f for an F distribution. FPDF(1.0, 5, 10) returns approximately 0.4595. Use this for plotting F distributions.

Excel equivalent: F.DIST(f, df1, df2, FALSE) | R equivalent: df(f, df1, df2)

Binomial Distribution

The binomial distribution models the number of successes in a fixed number of independent trials, where each trial has the same probability of success.

BINOMIAL(k, n, p)

Returns the probability of k or more successes in n trials, where each trial has probability p of success. This calculates P(X ≥ k). BINOMIAL(3, 10, 0.5) returns approximately 0.9453 - there's about a 94.5% chance of getting 3 or more heads in 10 fair coin flips. BINOMIAL(0, 5, 0.1) returns 1.0 since you're guaranteed to get at least 0 successes.

Excel equivalent: 1 - BINOM.DIST(k, n, p, TRUE) + BINOM.DIST(k, n, p, FALSE) | R equivalent: 1 - pbinom(k-1, n, p)

Error Functions

Error functions are related to the area under the normal curve and appear in solutions to diffusion equations and other applications.

ERF(x)

Returns the error function, which is related to the cumulative distribution function of the normal distribution. ERF(0) returns 0, ERF(1) returns approximately 0.8427.

Excel equivalent: ERF(x) | R equivalent: 2*pnorm(x*sqrt(2)) - 1

ERFC(x)

Returns the complementary error function, equal to 1 - ERF(x). ERFC(0) returns 1, ERFC(1) returns approximately 0.1573.

Excel equivalent: ERFC(x) | R equivalent: 2*pnorm(x*sqrt(2), lower.tail=FALSE)

Other Distribution Functions

PROBIT(p)

Returns the probit transformation with offset, calculated as 5 + Φ⁻¹(p), where Φ⁻¹ is the inverse standard normal cumulative distribution function. The offset of 5 is a historical convention that keeps most probit values positive. PROBIT(0.5) returns 5.0. PROBIT(0.975) returns approximately 6.96. If you need the standard probit without the offset, use ZINV instead. More information about the probit function is available at https://www.graphpad.com/support/faq/the-probit-function/

Excel equivalent: 5 + NORM.S.INV(p) | R equivalent: 5 + qnorm(p)

Random Number Generation

Random number generators produce unpredictable values for simulations, adding noise to data, or creating synthetic datasets. These functions are only available in transforms, not in user-defined equations, since regression models need deterministic behavior.

GAUSS(mean, sd)

Generates a random value from a normal distribution with specified mean and standard deviation. Each call produces a different random number drawn from this distribution. GAUSS(0, 1) might return -0.534 on one call and 1.203 on another, both drawn from a standard normal distribution. GAUSS(100, 15) generates values centered around 100. Use this for Monte Carlo simulations, adding random noise to models, or generating synthetic normally-distributed data. For deterministic probability calculations, use NORMDIST, NORMINV, or NORMPDF instead.

Excel equivalent: NORM.INV(RAND(), mean, sd) | R equivalent: rnorm(1, mean, sd)

RND(lower, upper)

Generates a random value from a uniform distribution between lower and upper bounds. Unlike random integer generators, this produces continuous values. RND(0, 1) might return 0.3729 or 0.8156 - any value between 0 and 1 is equally likely. RND(1, 100) might return 47.382. Use this for random sampling, creating randomized conditions, or Monte Carlo simulations when you need uniformly distributed random values.

Excel equivalent: RAND()*(upper-lower)+lower | R equivalent: runif(1, min=lower, max=upper)

Descriptive Statistics Functions

These functions calculate summary statistics from entire variables or columns of data. They're only available in transforms since they operate on complete datasets rather than individual values. When used, they broadcast their result to every row in the output.

MEAN(Variable)

Returns the arithmetic mean (average) of all values in a variable. The result is broadcast to every row. If Variable contains the values [10, 20, 30, 40, 50], MEAN(Variable) returns 30 on every row. This is useful for centering data or comparing individual values to the group average.

Excel equivalent: AVERAGE(range) | R equivalent: mean(x)

STDEV(Variable)

Returns the sample standard deviation of all values in a variable, using the n-1 denominator for the sample estimate. The result is broadcast to every row. Use this in combination with other functions to calculate z-scores or standardize data.

Excel equivalent: STDEV.S(range) | R equivalent: sd(x)

STDERR(Variable)

Returns the standard error of the mean, calculated as the standard deviation divided by the square root of the sample size. The result is broadcast to every row. Standard error quantifies uncertainty about the mean estimate.

Excel equivalent: STDEV.S(range)/SQRT(COUNT(range)) | R equivalent: sd(x)/sqrt(length(x))

COUNT(Variable)

Returns the count of non-blank values in a variable. Unlike the other statistics functions, COUNT only reports in rows that contain non-blank values. Use this to determine sample sizes or to identify which rows have complete data.

Excel equivalent: COUNT(range) or COUNTA(range) | R equivalent: length(na.omit(x))

CENTER(Variable)

Centers the values by subtracting the mean from each value, producing data with a mean of zero while preserving the spread and shape of the distribution. If Variable contains [1, 2, 3, 4, 5], CENTER(Variable) produces [-2, -1, 0, 1, 2]. Use this to remove the mean level from data before analysis or to create mean-centered predictors for regression.

Excel equivalent: value - AVERAGE(range) | R equivalent: scale(x, center=TRUE, scale=FALSE)

STANDARDIZE(Variable)

Standardizes values to z-scores by subtracting the mean and dividing by the standard deviation. The result has mean 0 and standard deviation 1. This transformation puts different variables on the same scale, making them directly comparable. Use this before analyses that are sensitive to scale differences, or when you want to express values in units of standard deviations from the mean.

Excel equivalent: (value - AVERAGE(range))/STDEV.S(range) | R equivalent: scale(x)

Special Mathematical Functions

Special functions are mathematical functions beyond elementary operations that arise in advanced applications. While you might not use these every day, they're essential for certain scientific and engineering calculations, and they underlie many statistical distributions.

Gamma Functions

The gamma function generalizes the factorial function to non-integer values. For positive integers, Γ(n) = (n-1)!. Gamma functions appear throughout probability theory and are essential for working with many statistical distributions.

GAMMA(x)

Returns the gamma function value at x. GAMMA(1) returns 1, GAMMA(5) returns 24 (which is 4!). GAMMA(0.5) returns approximately 1.7725 (which is √π). Returns blank for negative integers where the gamma function is undefined.

Excel equivalent: GAMMA(x) | R equivalent: gamma(x)

GAMMALN(x)

Returns the natural logarithm of the gamma function. This form prevents overflow for large values where GAMMA(x) itself would exceed Prism's maximum representable number. GAMMALN(1) returns 0, GAMMALN(5) returns approximately 3.1781 (which is ln(24)). Use this in likelihood calculations and anywhere you need to compute with very large factorial-like values.

Excel equivalent: GAMMALN(x) | R equivalent: lgamma(x)

IGAMMA(shape, x)

Returns the incomplete gamma function, which gives the cumulative probability for the gamma distribution. IGAMMA(1, 0) returns 0, IGAMMA(1, 1) returns approximately 0.6321. This function appears in various probability calculations and special function definitions.

Excel equivalent: GAMMA.DIST(x, shape, 1, TRUE) | R equivalent: pgamma(x, shape)

IGAMMAC(shape, x)

Returns the complementary incomplete gamma function, equal to 1 - IGAMMA(shape, x). IGAMMAC(1, 0) returns 1, IGAMMAC(1, 1) returns approximately 0.3679. Use this when you need the upper tail probability.

Excel equivalent: 1 - GAMMA.DIST(x, shape, 1, TRUE) | R equivalent: pgamma(x, shape, lower.tail=FALSE)

PSI(x)

Returns the digamma function, which is the derivative of the natural logarithm of the gamma function. PSI(1) returns approximately -0.5772 (the negative of the Euler-Mascheroni constant). PSI(2) returns approximately 0.4228. This function appears in maximum likelihood estimation and Bayesian statistics.

Excel equivalent: No direct equivalent | R equivalent: digamma(x)

Beta Functions

Beta functions are related to gamma functions through the relationship B(a,b) = Γ(a)×Γ(b)/Γ(a+b). They're essential for the beta distribution, which is used extensively in Bayesian statistics and for modeling proportions.

BETA(a, b)

Returns the beta function value. BETA(1, 1) returns 1, BETA(2, 2) returns approximately 0.1667. The beta function appears in probability distributions and in combinatorial identities.

Excel equivalent: EXP(GAMMALN(a) + GAMMALN(b) - GAMMALN(a+b)) | R equivalent: beta(a, b)

IBETA(a, b, x)

Returns the incomplete beta function, which gives the cumulative probability for the beta distribution. The value x must be between 0 and 1. IBETA(2, 2, 0.5) returns 0.5, IBETA(5, 3, 0.7) returns approximately 0.9308. This function is used in statistical tests involving proportions and in Bayesian inference.

Excel equivalent: BETA.DIST(x, a, b, TRUE) | R equivalent: pbeta(x, a, b)

Bessel Functions

Bessel functions are solutions to Bessel's differential equation and appear in problems with cylindrical or spherical symmetry. They're essential in wave propagation, heat transfer, electromagnetic fields, and quantum mechanics. Note that Prism uses (n, x) argument order, while Excel reverses this to (x, n).

BESSELJ(n, x)

Returns the Bessel function of the first kind of order n. These functions (denoted Jₙ(x)) are finite at the origin and oscillate with decreasing amplitude for large x. BESSELJ(0, 0) returns 1, BESSELJ(0, 1) returns approximately 0.7652. Use these for problems involving cylindrical waves or vibrations.

Excel equivalent: BESSELJ(x, n) [WARNING: Arguments reversed!] | R equivalent: besselJ(x, n)

BESSELY(n, x)

Returns the Bessel function of the second kind (also called Neumann function) of order n. These functions (denoted Yₙ(x)) are singular at the origin but are needed along with J functions for complete solutions. BESSELY(0, 1) returns approximately 0.0883, BESSELY(1, 1) returns approximately -0.7812. Returns blank for x ≤ 0.

Excel equivalent: BESSELY(x, n) [WARNING: Arguments reversed!] | R equivalent: besselY(x, n)

BESSELI(n, x)

Returns the modified Bessel function of the first kind of order n. These functions (denoted Iₙ(x)) grow exponentially for large x and appear in solutions to modified Bessel's equation. BESSELI(0, 0) returns 1, BESSELI(0, 1) returns approximately 1.2661. Use these in problems involving diffusion or heat conduction in cylindrical geometries.

Excel equivalent: BESSELI(x, n) [WARNING: Arguments reversed!] | R equivalent: besselI(x, n)

BESSELK(n, x)

Returns the modified Bessel function of the second kind (also called Macdonald function) of order n. These functions (denoted Kₙ(x)) decay exponentially for large x. BESSELK(0, 1) returns approximately 0.4210, BESSELK(1, 1) returns approximately 0.6019. Returns blank for x ≤ 0.

Excel equivalent: BESSELK(x, n) [WARNING: Arguments reversed!] | R equivalent: besselK(x, n)

Hypergeometric Functions

Hypergeometric functions are solutions to the hypergeometric differential equation. Many elementary functions (like exponentials, trigonometric functions, and logarithms) and special functions (including Bessel functions) are special cases of hypergeometric functions. They appear in quantum mechanics, mathematical physics, and statistics.

HYPGEOMETRICM(a, b, x)

Returns the confluent hypergeometric function M (also called Kummer's function of the first kind), denoted M(a, b, x) or ₁F₁(a; b; x). This function appears in solutions to quantum mechanical problems and in various special function relationships.

Excel equivalent: No equivalent | R equivalent: Not in base R

HYPGEOMETRICU(a, b, x)

Returns the confluent hypergeometric function U (also called Kummer's function of the second kind or Tricomi's function). This provides a second, independent solution to Kummer's equation that's needed for complete solutions in certain domains.

Excel equivalent: No equivalent | R equivalent: Not in base R

HYPGEOMETRICF(a, b, c, x)

Returns the Gaussian hypergeometric function, denoted ₂F₁(a, b; c; x). This is the most general hypergeometric function and includes many elementary and special functions as limiting or special cases. The series is convergent for |x| < 1.

Excel equivalent: No equivalent | R equivalent: Not in base R

Logical and Conditional Functions

Logical functions let you make decisions in your formulas based on conditions. They're essential for creating formulas that behave differently depending on the data, implementing thresholds, or combining multiple criteria.

IF(condition, value_if_true, value_if_false)

Returns one value when a condition is true and another when it's false. The condition is evaluated row-by-row, so different rows can produce different results. IF(5 > 3, "Yes", "No") returns "Yes". IF(X < 0, 0, X) returns 0 for negative values of X and returns X itself for non-negative values - a common pattern for implementing thresholds. You can nest IF statements to create multiple branches, though complex nesting can become hard to read.

String comparisons in conditions are not case-sensitive. IF(Status = "PASS", 1, 0) treats "PASS", "Pass", and "pass" identically.

Excel equivalent: IF(condition, value_if_true, value_if_false) | R equivalent: ifelse(condition, value_if_true, value_if_false)

AND(condition1, condition2, ...) or condition1 AND condition2

Returns TRUE (represented as 1) only when all conditions are true. If any condition is false, AND returns FALSE (represented as 0). You can write this as a function like AND(5 > 3, 10 < 20), which returns TRUE, or using infix notation like "5 > 3 AND 10 < 20". Both forms work identically. AND(5 > 3, 10 > 20) returns FALSE because the second condition fails. You can combine AND with IF to require multiple criteria: IF(AND(X > 0, X < 100), "Valid", "Invalid").

Excel equivalent: AND(condition1, condition2, ...) | R equivalent: condition1 & condition2 or all(condition1, condition2, ...)

OR(condition1, condition2, ...) or condition1 OR condition2

Returns TRUE (1) when at least one condition is true. Only returns FALSE (0) when all conditions are false. OR(5 > 3, 10 > 20) returns TRUE because the first condition is true, even though the second is false. OR(5 < 3, 10 > 20) returns FALSE because both conditions are false. Like AND, you can write this as a function or using infix notation. Use OR when any of several conditions should trigger a result: IF(OR(Status = "error", Status = "warning"), "Check", "OK").

Excel equivalent: OR(condition1, condition2, ...) | R equivalent: condition1 | condition2 or any(condition1, condition2, ...)

NOT(condition)

Returns the logical opposite of a condition. NOT(5 > 3) returns FALSE (0), NOT(5 < 3) returns TRUE (1). Use this to invert conditions or to express "not equal" more explicitly: NOT(X = 0) is equivalent to X <> 0, but the NOT form might be clearer in some contexts. Combine NOT with other logical operators for complex conditions: IF(NOT(OR(X < 0, X > 100)), "Valid", "Out of range").

Excel equivalent: NOT(condition) | R equivalent: !condition

IS_DEFINED(x)

Returns TRUE if a value exists (is not blank) and FALSE for blank cells. This is particularly useful in transforms where you might have incomplete data. IS_DEFINED(5) returns TRUE, IS_DEFINED(0) returns TRUE (zero is a defined value), but IS_DEFINED(blank_cell) returns FALSE. Use this to handle missing data conditionally: IF(IS_DEFINED(X), X, 0) returns X when it exists and 0 otherwise. You can also use this to count or identify complete cases: IF(AND(IS_DEFINED(Height), IS_DEFINED(Weight)), "Complete", "Missing data").

Excel equivalent: NOT(ISBLANK(x)) | R equivalent: !is.na(x)

String Functions

String functions let you work with text, which is useful for creating labels, combining information, or building descriptive output.

CONCATENATE(text1, text2, ...)

Joins multiple text strings or values together into a single string. Numeric values are automatically converted to text. CONCATENATE("John", "Smith") returns "JohnSmith". To add spaces or other separators, include them as arguments: CONCATENATE("John", " ", "Smith") returns "John Smith". CONCATENATE("Value: ", 42) returns "Value: 42".

When concatenated output is later converted to numeric form (which can happen in some contexts), any entries that aren't valid numbers are omitted from the result. This means CONCATENATE("Value: ", 42) would not convert to a number, but CONCATENATE(3, 4) would convert to the number 34.

Excel equivalent: CONCATENATE(text1, text2, ...) or the & operator | R equivalent: paste(text1, text2, ..., sep="")

Utility Functions

Utility functions provide helpful conversions and transformations that don't fit neatly into other categories.

PERC(x)

Converts a percentage value to a fraction by dividing by 100. This function interprets its input as a percentage and returns the decimal equivalent. PERC(50) returns 0.5, PERC(12.5) returns 0.125. Use this when you want to enter percentages directly in formulas without having to divide by 100 yourself. For instance, "100 * PERC(5)" returns 5, calculating 5% of 100. Note that PERC returns the fractional form (the actual multiplier), not the percentage itself.

Excel equivalent: x / 100 | R equivalent: x / 100