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 Item 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.

      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

      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.


      Was this article helpful?