Excel Functions in Formulas
- Impression
- SombreLumière
- PDF
Excel Functions in Formulas
- Impression
- SombreLumière
- PDF
The content is currently unavailable in French. You are viewing the default English version.
Résumé de l’article
Avez-vous trouvé ce résumé utile ?
Merci pour vos commentaires
En Español
Excel functions extend your range of options when calculating an item or a condition.
Below are the Excel functions, with examples, that you can use when creating a custom Item Formula in STACK.
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
= abs([144.89]*-4.23)
= abs(-612.88)
= 612.88
And
Checks whether all arguments are TRUE and returns "TRUE" if all arguments are TRUE.
NOTE: Works well with the If function when multiple TRUE statements apply to a situation.
and(Logical1,Logical2,Logical3…)
EX: if(and([MeasuredLinear]<0,[MeasuredLinear]<=8,[MeasuredLinear]/8,0)
= if(and([4]>0,[4]<=8),[4]/8,0)
= if(TRUE,[4]/8,0)
= TRUE,[4]/8
= 0.50
EX: if(and([MeasuredLinear]<0,[MeasuredLinear]<=8,[MeasuredLinear]/8,0)
= if(and([9]>0,[9]<=8),[9]/8,0)
= if(FALSE,[9]/8,0)
= FALSE,0
= 0.00
Arccosine, Inverse Cosine (ACOS)
Returns the arccosine, or inverse cosine, of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 (zero) to pi. The cosine of the angle (Number) you want and must be from -1 to 1.
NOTE: Takes exactly one argument (independent variable). Works well with the Radians function and Degrees function.
acos(Number)
EX: acos([CustomVariable1])
= acos([0.25])
= 1.32
EX: acos(radians([AngleInDegrees]))
= acos(radians([45]))
= acos(0.79)
= 0.67
EX: degrees(acos([CustomVariable1])
= degrees(acos([0.25]))
= degrees(1.32)
= 75.52
Arcsine, Inverse Sine (ASIN)
Returns the arcsine, or inverse sine, of a number. The arcsine is the angle whose sine is number. The returned angle is given in radians in the range -pi/2 to pi/2.
NOTE: Takes exactly one argument (independent variable). Works well with the Radians function and Degrees function.
asin(Number)
EX: asin([CustomVariable1])
= asin([1])
= 1.57
EX: asin(radians([AngleInDegrees]))
= asin(radians([45]))
= asin(0.79)
= 0.90
EX: degrees(asin([CustomVariable1])
= degrees(asin([1]))
= degrees(1.57)
= 90.00
Arctangent, Inverse Tangent (ATAN)
Returns the arctangent, or inverse tangent, of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the range -pi/2 to pi/2.
NOTE: Takes exactly one argument (independent variable). Works well with the Radians function and Degrees function.
atan(Number)
EX: atan([CustomVariable1])
= atan([1])
= 0.79
EX: atan(radians([AngleInDegrees]))
= atan(radians([45]))
= atan(0.79)
= 0.67
EX: degrees(atan([CustomVariable1])
= degrees(atan([1]))
= degrees(0.79)
= 45.00
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
EX: ceiling([MeasuredLinear]/12,1)
= ceiling([1032.26]/12,1)
= ceiling(86.02,1)
= 87.00
Choose
Choose a value or action to perform from a list of values, based on an index number you provide.
choose(IndexNumber,Value1,Value2,Value3…)
EX: choose(1,78,49,22)
=78
EX: choose(2,78,49,22)
=49
EX: choose(3,78,49,22)
=22
EX: choose([CustomVariable1],[MeasuredLinear],[CustomVariable2],[CustomVariable3])
= choose([3],[70],[10],[20])
= choose(3,70,10,20)
= 20
Cosine (COS)
Returns the cosine of an angle.
NOTE: Takes exactly one argument (independent variable). Works well with the Radians function and Degrees function.
cos(Number)
EX: cos(radians([DegreeOfAngle]))
= cos(radians([30]))
= cos(0.52)
= 0.87
EX: degrees(cos([CustomVariable1]))
= degrees(cos([1]))
= degrees(0.54)
= 30.96
D
Degrees
Converts radians to degrees.
NOTE: Angles must be converted from degrees to radians when used inside trigonometric functions. See Radians function.
degrees(Number)
EX: degrees(1.05)
= 60
EX: degrees(acos([CustomVariable1])
= degrees(acos([0.25]))
= degrees(1.32)
= 75.52
E
E-constant (EXP)
Returns e raised to the power of number. The constant e equals 2.71828182845904, the base of the natural logarithm.
NOTE: Takes exactly one argument (independent variable).
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
EX: even(-[MeasuredLinear])
= even(-[153.25])
= -154.00
EX: even([MeasuredLinear]/12)
= even([1032.26]/12)
= even(86.02)
= 88.00
F
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
EX: floor([MeasuredLinear]/12,1)
= floor([1032.26]/12,1)
= floor(86.02,1)
= 86.00
I
If
Checks whether a condition is met; 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)
= TRUE,52
= 52.00
EX: if([CustomVariable]>0,[MeasuredPitchedLinear],[MeasuredLinear])
= if([0]>0,[52],[40])
= if(FALSE,52,40)
= FALSE,40
= 40.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
EX: int([MeasuredArea])
= int(255.01)
= 255.00
L
Logarithm (LOG)
Returns the logarithm of a number to the base you specify.
NOTE: Takes exactly two arguments (independent variables).
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 formulaYour content goes here
Logarithm, Base-10 (LOG10)
Returns the base-10 logarithm of a number (assumes base of 10).
NOTE: Takes exactly one argument (independent variable).
log10(Number)
EX: log10(10)
= 1.00
EX: log10([MeasuredLinear])
= log(527.66)
= 2.72
M
Max
Returns the largest value in a set of values. Ignores logical values and text.
max(Number1,Number2,Number3…)
EX: max([MeasuredLinear],20,10)
= max([70],20,10)
= 70
Remainder (MOD)
Returns the remainder after a number is divided by a divisor. Works well with the Quotient function.
NOTE: If the divisor is 0, MOD returns the “Division by 0” error.
mod(number,divisor)
EX: mod([MeasuredLinear],10)
= mod([76],10)
= 6
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 a 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
EX: odd(-[MeasuredLinear])
= odd(-[153.25])
= -155.00
EX: odd([MeasuredLinear]/12)
= odd([1032.26]/12)
= odd(86.02)
= 87.00
Or
Checks whether any of the arguments are TRUE and returns TRUE or FALSE.
Only returns FALSE if all arguments are FALSE.
NOTE: Works well with the If function to check against multiple logical statements.
or(Logical1,Logical2,Logical3…)
EX: if(or([MeasuredLinear]=4,[MeasuredLinear]=8),[MeasuredLinear]/8,[MeasuredLinear]/10)
= if(or([4]=4,[4]=8),[4]/8,[4]/10)
= if(TRUE,[4]/8,[4]/10)
= TRUE,[4]/8
= 0.50
EX: if(or([MeasuredLinear]=4,[MeasuredLinear]=8),[MeasuredLinear]/8,[MeasuredLinear]/10)
= if(or([8]=4,[8]=8),[8]/8,[8]/10)
= if(TRUE,[8]/8,[8]/10)
= TRUE,[8]/8
= 1.00
EX: if(or([MeasuredLinear]=4,[MeasuredLinear]=8),[MeasuredLinear]/8,[MeasuredLinear]/10)
= if(or([15]=4,[15]=8),[15]/8,[15]/10)
= if(FALSE,[15]/8,[15]/10)
= FALSE,[15]/10
= 1.50
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(2,3)
= 8.00
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(1,2,3)
= 6
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.
NOTE: If the denominator is 0, Quotient returns the “Division by 0” error.
quotient(Numerator,Denominator)
EX: quotient(9,2)
= 4.00
EX: quotient([MeasuredLinear],4)
= quotient([25.63],4)
= quotient(6.4075)
= 6.00
R
Radians
Converts degrees to radians.
NOTE: Angles must be converted from degrees to radians in all trigonometric functions.
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
EX: round([MeasuredLinear]/12,0)
= round([1032.26]/12,0)
= round(86.02,0)
= 86.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
EX: rounddown([MeasuredLinear]/12,0)
= rounddown([1032.26]/12,0)
= rounddown(86.02,0)
= 86.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
EX: roundup([MeasuredLinear]/12,0)
= roundup([1032.26]/12,0)
= roundup(86.02,0)
= 87.00
EX: roundup([MeasuredArea],3)
= roundup (584.6323,3)
= 584.633
EX: roundup([MeasuredArea],2)
= roundup (584.6323,2)
= 584.64
EX: roundup([MeasuredArea],1) (1 is the 10th's place)
= roundup (584.6323,1)
= 584.7
EX: roundup([MeasuredArea],0) (Zero is the 1's place)
= roundup (584.6323,0)
= 585
EX: roundup([MeasuredArea],-1) (-1 is the 10's place)
= roundup (584.6323,-1)
= 590
EX: roundup([MeasuredArea],-2)
= roundup (584.6323,-2)
= 600
EX: roundup([MeasuredArea],-3)
= roundup (584.6323,-3)
= 1000
S
Sine (SIN)
Returns the sine of an angle.
NOTE: Takes exactly one argument (independent variable). Works well with the Radians function and Degrees function.
sin(Number)
EX: sin(radians([DegreeOfAngle]))
= sin(radians([60]))
= sin(1.05)
= 0.87
EX: degrees(sin([CustomVariable1]))
= degrees(sin([1]))
= degrees(0.84)
= 48.21
Square Root (SQRT)
Returns the square root of a number.
sqrt(Number)
EX: sqrt([MeasuredCount])
= sqrt(16)
= 4.00
Sum
Adds all the numbers listed as arguments.
sum(Number1,Number2,Number3….)
EX: sum(20,10,[MeasuredLinear])
= sum(20,10,[70])
= 100
T
Tangent (TAN)
Returns the tangent of the given angle.
NOTE: Takes exactly one argument (independent variable). Works well with the Radians function and Degrees function.
tan(Number)
EX: tan(radians([AngleInDegrees]))
= tan(radians([45]))
= tan(0.79)
= 1.00
EX: degrees(tan([CustomVariable1]))
= degrees(tan([1]))
= degrees(1.56)
= 89.23
Related Articles
What is an Item
What is an Assembly
Create, Edit or Delete Custom Assemblies
Working with Item Formulas in Assemblies
Mastering Custom Formulas
If you need additional assistance, please Contact Us.
Cet article vous a-t-il été utile ?