## Cost Schedule Formula List

The following is a list of the available formula that can be utilised within the Cost Schedule formula column.

This list can also be accessed by using CTRL + Space in the formula column of either the Direct Cost or Overhead Cost Schedule.

These functions may be used in combinations or as part of a larger formula, with annotations and operators such as add (+), subtract (-), multiply (*) and divide (/).

FUNCTION

DESCRIPTION

CirArea

The CirArea function will return the area of a circle of the radius defined.

Syntax:
CirArea(x) – Where x is the radius of the circle.

CirArea(x)/y – Where x the radius and the total area is then divided by y.

Example:
CirArea(100) gives the area of a circle with a radius of 100 resulting in a value of 31,415.9  entered into the Quantity column.

CirCircum

The CirCircum function returns the circumference of a circle of a specified radius.

Syntax:
CirCircum(x) – Where x is the radius.

Example:
CirCircum(100) returns the circumference of a circle with a radius of 100 resulting in a value of 628.32 entered into the Quantity column.

CirFrustumVol

CirFrustumVol returns the volume of the frustum of a cone calculated on defined values for upper and lower radii and the height.

Syntax:
CirFrustumVol(x,y,z) – Where x is the lower radius, y is the upper radius and z is the height.

Example:
CirFrustumVol(15, 10, 7.5) returns the volume of a frustum with lower radius of 15, upper radius of 10 and a height of 7.5 resulting in a value of 3,730.641 entered into the Quantity column.

Alternatively, you can use this formula to return the volume of a complete cone by setting one of the radius values to 0 creating the vertex of the cone.

CirSectArea

This function returns the area of an angular sector of a circle, calculated on defined values for circle radius and angle of the sector.

Syntax:
CirSectArea(x,y) – Where x is the radius of the circle and y is the angle of the sector.

Example:
CirSectArea(100, 90) will return the area of a 90° sector of a circle with a radius of 100 resulting in a value of 7,853.982 entered into the Quantity column.

CirSegArea

This function returns the area of a segment of a circle.

Syntax:
CirSegArea(x,y) – Where x is the Radius and y is the distance from the secant to the centre of the circle.

Note: The distance (y variable) must be less than the radius (x variable).

Example:
CirSegArea(5,3) will return the area of a segment 3 units away from the centre of the circle with a radius of 5 resulting in a value of 11.182 entered into the Quantity column.

CirSegAreaRem

CirSegAreaRem can be used to return the remaining area of the circle less the segment area defined.

In usage, the function appears the same as the CirSegArea formula where you define the segment, however rather than returning the area of the segment, this formula returns the remaining area of the circle after that segment is removed.

Syntax:
CirSegAreaRem(x,y) – Where x is the Radius, y is the distance from the secant to the centre of the circle.

Note: The distance (y variable) must be less than the radius (x variable).

Example:
CirSegAreaRem(5,3) will return the remaining area of a circle with a radius of 5 that has a segment 3 units away from the centre resulting in a value of 67.357 entered into the Quantity column.

ConeSlopeArea

This function is used to calculate the surface area of the sloped face of a cone.

Syntax:
ConeSlopeArea(x,y) – Where x is the base radius and y is the height from the base to the vertex.

Example:
ConeSlopeArea(10, 7.5) will return the surface area of the sloped face of a cone with a base radius of 10 and a height to the vertex of 7.5 resulting in a value of 392.699 entered into the Quantity column.

ConeVol

The ConeVol function returns the volume of a cone.

Syntax:
ConeVol(x,y)  – Where x is the radius of the base of the cone and y is the height.

Example:
ConeVol(10, 15) will return the volume of a cone with a base radius of 10 and a height of 15 resulting in a value of 1,570.796 entered into the Quantity column.

Cos

This function is used to find the Cosine of a specified angle.

Syntax:
Cos(x) – Where x is the angle to return the Cosine of.

Example:
Cos(70) will return the Cosine of 70° resulting in a value of 0.342 entered into the Quantity column.

EllipseArea

EllipseArea returns the area of an ellipse of a specified size.

Syntax:
EllipseArea(x,y) – Where x is the largest diameter and y is the smallest diameter.

Example:
EllipseArea(20, 10) will return the area of an ellipse that has a large diameter of 20 and a small diameter of 10 resulting in the value 157.080 entered into the Quantity column.

Exp

This function is used to return Exponential growth rate of a specified value.

Syntax:
Exp(x) – Where x is the number to find the exponential growth of.

Example:
Exp(4) will return the exponential growth of the number 4 resulting in a value 54.598 entered into the Quantity column.

Hrect

The Hrect function is used to return the length of the hypotenuse of a triangle.

Syntax:
Hrect(x, y) where x is the length of the triangle and y is the height of the triangle.

Example:
Hrect(25, 60) would give the hypotenuse for a triangle with a length of 25 and height of 60 resulting in a value of 65 entered into the Quantity column.

If

The If statement evaluates a logic test and returns a value based on the result.

If statements can be nested within each other to perform multiple tests on multiple values and return different values for all possible outcomes of the tests.

Syntax:
If(“Logic Condition”, Value if True, Value if False).

Example:
If(10>5, 1,2)
This statement will test if 10 is greater than 5, and if it is, return 1 if not, return 2 to the quantity column.

See the If statement article for a full discussion of the potential functions available.

Log

The Log function returns the Base 10 Log of a specified value.

Syntax:
Log(x) – Where x is the value you want to get the base 10 log of.

Example:
Log(100) will return the base 10 log of 100 resulting in a value of 2 entered into the Quantity column.

Max

The Max function is used to return the maximum value within a specified set of numbers.

Syntax:
Max(x) – Where x is the set of numbers to find the highest value in.

Example:
Max(1, 4, 5) returns the highest value from the range of 1, 4 & 5 resulting in a value of 5 entered into the Quantity column.

Min

The Min function is used to return the minimum value within a specified set of numbers.

Syntax:
Min(x) – Where x is the set of numbers to find the lowest value in.

Example:
Min(1, 4, 5) returns the lowest value from the range of 1, 4 & 5 resulting in a value of 1 entered into the Quantity column.

Mod

The Mod function is used to return the remaining value after dividing one operand by a second.

Syntax:
Mod(x, y) – Where x is the dividend and y is the divisor.

Example:
Mod(10, 4) returns the remaining value after a value of 10 is divided by a value of 4 resulting in a value of 2 entered into the Quantity column.

Pi

The Pi function returns the value of Pi accurate to 14 decimals.

Syntax:
Pi() – There are no parameters needed for the Pi function.

Example:
Pi() will return the value of 3.14159265358979.

PyramidVol

Use this function to return the volume of a Pyramid.

Syntax:
PyramidVol(x,y) – Where x is the area of the pyramid’s base and y is the height of the pyramid.

Example:
PyramidVol(10,5) will return the volume of a Pyramid with a base area of 10 and a height of 5 resulting in a value of 16.667 entered in the quantity column.

Q / SetQuantity

Q or SetQuantity is used to place the specified value in the quantity column.

Syntax:
Q(x) or SetQuantity(x) – Where x is the value that will be entered into the Quantity column.

Example:
Q(50) or SetQuantity(50) will place a value of 50 in the quantity of the selected line.

RectArea

The RectArea function outputs the area of a rectangle of the specified dimensions.

Syntax:
RectArea(x,y) – Where x represents the length of the rectangle and y represents the width of the rectangle.

Example:
RectArea(10,5) will return the area of a rectangle with a length of 10 and width of 5 resulting in a value of 50 entered into the Quantity column.

RectCircum

The RectCircum function returns the length of the outer perimeter of a rectangle of the specified dimensions.

Syntax:
RectCircum(x,y) – Where x represents the length of the rectangle and y represents the width of the rectangle.

Example:
RectCircum(10, 5) will return the perimeter distance around a rectangle with a length of 10 and width of 5 resulting in a value of 30 ented into the Quantity column.

RelativeSum

The RelativeSum() or Rsum() function returns the total of all cost estimate lines up to either the previous RelativeSum() function or top of the cost estimate.

This is useful for quickly adding subtotals to different sections of your Cost Estimate.

Syntax:
RelativeSum() or Rsum() – There are no parameters needed for the RelativeSum function.

Example:
If a RelativeSum() is entered on lines 10 and 20, the RelativeSum on line 20 will sum the total of all lines sequentially above to the next RelativeSum() on line 10. The RelativeSum on line 10 will sum all lines to the top of the Cost Estimate.

Round

Rounds the specified value to the number of decimal places defined (away from 0 for midpoint values).

The number values can either be manually entered or a define.

Syntax:
Round(x, y) – Where x is the number to be rounded and y is the number of decimal places to round to.

Example:
Round(100.626, 2) will round the value 100.626 to two decimal places resulting in a value of 100.63 entered into the Quantity column.

Rounddn

The Rounddn function is used to round a value down to the number of decimal places defined.

The number values and either be manually entered or a define.

Syntax:
Rounddn(x, y) – Where x is the number to be rounded down and y is the number of decimal places to round to.

Example:
Rounddn(102.37, 1) will round 102.37 down to one decimal place resulting in a value of 102.3 entered into the Quantity column.

In an item with a schedule quantity of 18.65, Rounddn(#QTY) will return 18.00 to the quantity column).

Roundup

The Roundup function is used to round a value up to the number of decimal places defined.

The number values and either be manually entered or a define.

Syntax:
Roundup(x, y) – Where x is the number to be rounded up and y is the number of decimal places to round to.

Example:
Roundup(102.33, 1) will round 102.34 UP to one decimal place resulting in a value of 102.4 entered into the Quantity column.

In an item with a schedule quantity of 18.65, Roundup(#QTY) will return 19.00 to the quantity column).

Sin

Returns the Sine of a specified value.

Syntax:
Sin(x) – Where x is the angle to return the Sine of.

Example:
Sin(45) will return the Sine of 45° resulting in a value of 0.707 entered into the Quantity column.

SphereArea

The SphereArea function returns the surface area of a sphere of a specified size.

Syntax:
SphereArea(x) – Where x is the radius of the sphere.

Example:
SphereArea(10) will return the surface area of a Sphere that has a radius of 10 resulting in a value of 1,256.637 entered into the Quantity Column.

SphereVol

The SphereVol function returns the volume of a sphere of a specified size.

Syntax:
SphereVol(x) – Where x is the radius of the sphere.

Example:
SphereVol(10) will return the area of a Sphere that has a radius of 10 resulting in a value of 4,188.790 entered into the Quantity Column.

Sqrt

The Sqrt function returns the square root of a specified value.

Syntax:
Sqrt(x) – Where x represents the number to find the square root of.

Example:
Sqrt(9) will return the square root of 9 resulting in a value of 3 entered into the Quantity column.

St

The St function sums the Total of the specified lines and to produce a Rate by dividing the cumulative Total by a specified quantity.

Syntax:
St(x:y,z) – Where x and y are the start and end points of the lines to be referenced and z is the number to divide the total by.

Example:
St(1:5,10) will sum the totals of all contributing lines between 1 and 5, then divides the cumulative total by 10 to produce a rate.

Sum

The sum function is used to sum (add) the Total of the lines specified within the formula.

Syntax:
Sum(x:y) – Where x and y are the start and end points of the lines to be referenced.

Example:
Sum(1:15) will total all lines between line 1 and 15 and return the Total values.

Sum(1:7, 10:25) will total all the lines between lines 1:7 as well as all lines between 10:25.

SumUserDef

The SumUserDef() function is used to sum the Totals of lines that contain specific values in a specified User Defined Column.

Syntax:
SumUserDef(x,y) – Where x is the User Define Column being referred to by the ‘Column Reference’ and y is the criteria needed to be matched. The Column Reference is set when creating the User Defined Column in Project Properties.

Example:
SumUserDef(“AreaRef”, “Section1” ) will output the total sum of all lines in the AreaRef User Defined Column that contain the criteria Section1.

Tan

Returns the Tangent of a specified value.

Syntax:
Tan(x) – Where x is the angle to return the Tangent of.

Example:
Tan(45) will return the Tangent of 45° resulting in a value of 1 entered into the Quantity column.