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
Import items into your STACK item database
How to Customize or Create an Assembly
Working with Assembly Formulas
Find by Excel function name
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
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
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
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
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
D
Degrees
Converts radians to degrees
degrees(Number)
EX: degrees([RadiansOfAngle])
= degrees(1.05)
= 60.16
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
R
Radians
Converts degrees to radians, all degrees must be converted to radians first
radians(Number)
EX: radians([DegreeOfAngle])
= radians(60)
= 1.05
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
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
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
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
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
Square Root (SQRT)
Returns the square root of a number
sqrt(Number)
EX: sqrt([MeasuredCount])
= sqrt(16)
= 4.00
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
Sum Squares (SUMSQ)
Returns the sum of the squared values
sumsq(Number1,Number2,...)
EX: sumsq(3,4)
= 25
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