Excel Functions in Formulas

Excel functions extend our range of options when calculating an item or a condition. Below are Excel functions, with examples, that can be used in a custom formula.

More Articles about Formulas

What Is An Item?

Import items into your STACK item database

What Is An Assembly?

How to Customize or Create an Assembly

Working with Assembly Formulas

Mastering Custom Formulas

Find by Excel function name

A C D E F I L

M O P Q R S T

A

Absolute Value (ABS)

Returns the absolute value of a number, a number without its sign

abs(Number)

EX: abs([MeasuredLinear]*-4.23)
    = abs([144.89]*-4.23)
    = abs(-612.88)
    = 612.88

back to top

C

Ceiling

Rounds a number up, to the nearest multiple of significance

NOTE: Rounding will occur for each measurement drawn before the values are added together. Rounding may cause a larger value than expected. Use with caution.

ceiling(Number,Significance)

EX: ceiling([MeasuredLinear],1)
    = ceiling(664.77,1)
    = 665.00

EX: ceiling([MeasuredLinear],2)
    = ceiling(664.77,2)
    = 666.00

EX: ceiling([MeasuredLinear],5)
    = ceiling(664.77,5)
    = 665.00

EX: ceiling([MeasuredLinear],10)
    = ceiling(664.77,10)
    = 670.00

back to top

Cosecant (CSC)

Returns the cosecant of an angle, angles provided in degrees must first be converted to radians

csc(Number)

EX: csc(radians([DegreeOfAngle]))
    = csc(radians([45]))
    = csc(0.79)
    = 1.41

back to top

Cosine (COS)

Returns the cosine of an angle, angles provided in degrees must first be converted to radians

cos(Number)

EX: cos(radians([DegreeOfAngle]))
    = cos(radians([30]))
    = cos(0.52)
    = 0.87

back to top

Cotangent (COT)

Returns the cotangent of an angle, angles provided in degrees must first be converted to radians

cot(Number)

EX: cot(radians([DegreeOfAngle]))
    = cot(radians([60]))
    = cot(1.05)
    = 0.58

back to top

D

Degrees

Converts radians to degrees

degrees(Number)

EX: degrees([RadiansOfAngle])
    = degrees(1.05)
    = 60.16

back to top

E

E-constant (EXP)

Returns e raised to the power of number. The constant e equals 2.71828182845904, the base of the natural logarithm.

exp(Number)

EX: exp(1)
    = 2.72

EX: exp(2)
    = 7.39

back to top

Even

Rounds a positive number up and a negative number down to the nearest even integer

NOTE: Rounding will occur for each measurement drawn before the values are added together. Rounding may cause a larger value than expected. Use with caution.

even(Number)

EX: even([MeasuredLinear])
    = even(153.25)
    = 154.00

back to top

F

Factorial (FACT)

Returns the factorial of a number. The factorial of a number is equal to 1*2*3*...* number.

NOTE: The value must be a nonnegative number for which you want the factorial. If number is not an integer, it is truncated.

fact(Number)

EX: FACT(5)
    = 120.00

EX: FACT(1.9)
    = 1.00

EX: FACT(0)
    = 1.00

EX: FACT(-1)
    = invalid formula

back to top

Floor

Rounds a number down to the nearest multiple of significance.

NOTE: Rounding will occur for each measurement drawn before the values are added together. Rounding may cause a larger value than expected. Use with caution.

floor(Number,Significance)

EX: floor([MeasuredLinear],1)
    = floor(663.77,1)
    = 663.00

EX: floor([MeasuredLinear],2)
    =floor(663.77,2)
    = 662.00

EX: floor([MeasuredLinear],5)
    =floor(663.77,5)
    = 660.00

EX: floor([MeasuredLinear],10)
    =floor(663.77,10)
    = 660.00

back to top

I

If-Statement (IF)

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

NOTE: The entire formula is taken into consideration when validating a formula, meaning the Logical Test, TRUE statement, and FALSE statement are checked for calculation errors before a value is displayed.

if(LogicalTest,ValueWhenTrue,ValueWhenFalse)

EX: if([CustomVariable]>0,[MeasuredPitchedLinear],[MeasuredLinear])
    = if([12]>0,[52],[40])
    = if(TRUE,52,40)
    = 52.00

back to top

Round to Integer (INT)

Rounds a number down to the nearest integer

NOTE: Rounding will occur for each measurement drawn before the values are added together. Rounding may cause a larger value than expected. Use with caution.

int(Number)

EX: int([MeasuredArea])
    = int(255.97)
    = 255.00

back to top

L

Logarithm (LOG)

Returns the logarithm of a number to the base you specify

log(Number,Base)

EX: log(10,10)
    = 1.00

EX: log(8,2)
    = 3.00

EX: log (86,2.7182818)
    = 4.45

EX: log(10)
    = invalid formula

back to top

Logarithm, Base-10 (LOG10)

Returns the base-10 logarithm of a number (assumes base of 10)

log10(Number)

EX: log10(10)
    = 1.00

EX: log10([MeasuredLinear])
    = log(527.66)
    = 2.72

back to top

Natural Logarithm (LN)

Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904).

ln(Number)

EX: ln(86)
    = 4.45

EX: ln([MeasuredLinear])
    = ln(527.66)
    = 6.27

back to top

M

Round to Multiple (MROUND)

Returns a number rounded to the nearest desired multiple

NOTE: Rounding will occur for each measurement drawn before the values are added together. Rounding may cause a larger value than expected. Use with caution.

mround(Number,Multiple)

EX: mround([MeasuredLinear],20)
    = mround(844.95,20)
    = 840.00

EX: mround([MeasuredLinear],20)
    = mround(874.95,20)
    = 880.00

back to top

O

Odd

Rounds a positive number up and an negative number down to the nearest odd integer

NOTE: Rounding will occur for each measurement drawn before the values are added together. Rounding may cause a larger value than expected. Use with caution.

odd(Number)

EX: odd([MeasuredLinear])
    = odd(153.25)
    = 155.00

back to top

P

Pi

Returns the value of Pi, to two decimals, takes no arguments

pi()

EX: pi()*([DiameterInches]/2)
    = 3.14*([12]/2)
    = 3.14*(6)
    = 18.84

back to top

Power

Returns the result of a number raised to a power:

power(Number,Power)

EX: power([DiameterInches]/12,2)
    = power([36]/12,2)
    = power(3,2)
    = (3*3)
    = 9.00

back to top

Product

Multiplies all the numbers given as arguments

product(Number1,Number2,Number3...)

EX: product([MeasuredLinear],power([DiameterInFeet/2],2),pi())
    = product(100.54,power(4/2,2),3.14159)
    = product(100.54,2,3.1159)
    = 1263.42

back to top

Q

Quotient

Returns the integer portion of a division

quotient(Numerator,Denominator)

EX: quotient([MeasuredLinear],4)
    = quotient([25.63],4)
    = quotient(25.63/4)
    = quotient(6.4075)
    = 6.00

back to top

R

Radians

Converts degrees to radians, all degrees must be converted to radians first

radians(Number)

EX: radians([DegreeOfAngle])
    = radians(60)
    = 1.05

back to top

Round

Rounds a number to a specified number of digits

NOTE: Rounding will occur for each measurement drawn before the values are added together. Rounding may cause a larger value than expected. Use with caution.

round(Number,NumberOfDigitsAfterDecimal)

EX: round([MeasuredLinear],0)
    = round(156.23,0)
    = 156.00

EX: round([MeasuredLinear],0)
    = round(156.53,0)
    = 157.00

back to top

Round Down (ROUNDDOWN)

Rounds a number down, toward zero

NOTE: Rounding will occur for each measurement drawn before the values are added together. Rounding may cause a larger value than expected. Use with caution.

rounddown(Number,NumberOfDigitsAfterDecimal)

EX: rounddown([MeasuredArea],0)
    = rounddown (1584.63,0)
    = 1584.00

back to top

Round Up (ROUNDUP)

Rounds a number up, away from zero

NOTE: Rounding will occur for each measurement drawn before the values are added together. Rounding may cause a larger value than expected. Use with caution.

roundup(Number,NumberOfDigitsAfterDecimal)

EX: roundup([MeasuredArea],0)
    = roundup (1584.63,0)
    = 1585.00

back to top

S

Secant (SEC)

Returns the secant of an angle, angles provided in degrees must first be converted to radians

sec(Number)

EX: sec(radians([DegreeOfAngle]))
    = sec(radians([30]))
    = sec(0.52)
    = 1.15

back to top

Sine (SIN)

Returns the sine of an angle, angles provided in degrees must first be converted to radians

sin(Number)

EX: sin(radians([DegreeOfAngle]))
    = sin(radians([60]))
    = sin(1.05)
    = 0.87

back to top

Square Root (SQRT)

Returns the square root of a number

sqrt(Number)

EX: sqrt([MeasuredCount])
    = sqrt(16)
    = 4.00

back to top

Square Root * Pi (SQRTPI)

Returns the square root of (Value * Pi)

sqrtpi(Number)

EX: sqrtpi([DiameterInches])
    = sqrtpi([12]*3.14)
    = sqrt(37.68)
    = 6.14

back to top

Sum Squares (SUMSQ)

Returns the sum of the squared values

sumsq(Number1,Number2,...)

EX: sumsq(3,4)
    = 25

back to top

T

Tangent (TAN)

Returns the tangent of an angle, angles provided in degrees must first be converted to radians

tan(Number)

EX: tan(radians([DegreeOfAngle]))
    = tan(radians([45]))
    = tan(0.79)
    = 1.00

back to top