Excel Functions in Formulas
    • Dark
      Light
    • PDF

    Excel Functions in Formulas

    • Dark
      Light
    • PDF

    Article Summary

    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 assembly formula in STACK.

    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

    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



    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

    What Is an Item
    What Is an Assembly
    Customize or Create an Assembly
    Working with Assembly Formulas
    Mastering Custom Formulas


    If you need additional assistance, please Contact Us.


    Was this article helpful?