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.
EX: abs([MeasuredLinear]*-4.23)
= 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]>
= if(and([4]0,[4]<=8),[4]>
= if(TRUE,[4]/8,0)
= TRUE,[4]/8
= 0.50
EX: if(and([MeasuredLinear]<0,[measuredlinear]><=8,[measuredlinear]>
= if(and([9]0,[9]<=8),[9]>
= 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.
NOTE: You must enter a non-zero number as the multiple of significance. No argument, or an argument of ZERO (0) will result in a calculation error because you cannot round to a multiple of zero.
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
See how others use this feature in the STACK Community.
If you need additional assistance, please Contact Us.
Comments
Please sign in to leave a comment.