Cost Estimate Formula List

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

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 (/).

This list can also be accessed by using CTRL + Space in the formula column.

FUNCTION

DESCRIPTION

Percent / %

The Percent or % function marks up the resource quantity of the cost estimate lines referenced by the nominated percentage value.

The value of this function is added to the overall total of Cost Estimate.

Syntax:
Percent(x:y,z,w) or %(x:y,z,w) – Where x and y are the start and end points of the lines to be referenced, z is the percentage to mark up the values within the range and w is the resource type to be included.

If left blank, the w variable will include all resource types.

Example:
Percent(1:5,25) will markup all resource quantities by 25% between lines 1 and 5 and add the value in the Total column.

Percent(1:5,25, L) will markup all the labour resource quantities by 25% between lines 1 and 5 and add the value in the Total column.

#LQ

The #LQ function returns the value in the Quantity column of the specified line.

Syntax:
#LQx – Where x is the line number to be referenced.

Example:
#LQ2 will return the value of the Quantity column on Line 2.

#LP

The #LP define returns the value within the Production column of the specified line.

This define is only available in Production mode Cost Estimates.

Syntax:
#LPx – Where x is the line number to be referenced.

Example:
#LP2 will return the value of the Production column on Line 2.

#LN

The #LN function returns the value in the Number column of the specified line.

This define is only available within Production and Time mode Cost Estimates.

Syntax:
#LNx – Where x is the line number to be referenced.

Example:
#LN2 will return the value of the Number column on Line 2.

#QTY

The “#QTY returns the schedule quantity of the item you’re currently editing.

This define is automatically created and cannot be redefined.

Syntax:
#QTY – There are no extra parameters that need to be set for the #QTY define.

Example:
If you are accessing the Cost Estimate for line 8 of your Direct Cost Schedule, and the Quantity of the line is 12, the #QTY define will return a value of 12.

#DUR

The #DUR function returns the duration of the Cost Schedule for the line you are currently editing.

This define is automatically created by the Duration column in the Cost Schedule, however, can also be defined as a Global Define or Local Define.

Syntax:
#DUR – There are no extra parameters that need to be set for the #DUR define.

Example:
If you are accessing the Cost Estimate for line 22 of your Direct Cost Schedule, and Line 22 has a duration of 15, using the #DUR in the direct cost estimate will return a value of 15.

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.

As with CirArea (above), CirCircum can be divided to get the length of an arc of any percentage of a circle.

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) must be less than the radius (x).

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.

Def / Define

Creates a local define that represents a value specified in the formula’s arguments.

Local defines are only available for use within the Cost Estimate in which they are defined.

Local defines must be created above the lines in which they are to be used but can be redefined at any later point. When re-defining a define, the updated value will take effect for the lines below where it was re-defined.

Syntax:
define(#x,y) or def(#x,y) – Where #x is what you decide to call your define, and y is the specified value.

Example:
define(#Area, 300) creates a local define called #Area, that when used, results in a value of 300 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.

GetDirectCostResUnit

The GetDirectCostResUnit function is used to reference the total usage of a nominated resource unit from the Direct Cost Schedule.

This function is only available within an Overhead Cost Estimate.

Syntax:
GetDirectCostResUnit(“x”) – Where x is the resource unit to be referenced.

Example:
GetDirectCostResUnit(“mhr”) will return the total usage of the resource unit mhr from the Direct Cost Schedule and enter the value into the Quantity column.

GetDirectCostResUse

The GetDirectCostResUse function is used to reference the total usage of a nominated resource from the Direct Cost Schedule.

This function is only available within an Overhead Cost Estimate.

Syntax:
GetDirectCostResUse(“x”) – Where x is the resource name to be referenced.

Example:
GetDirectCostResUse(“labourer”) will return the total usage of the resource labourer from the Direct Cost Schedule and enter the value into the Quantity column.

GetDirectCostValue

The GetDirectCostValue function is used to reference the total value of the nominated resource types from the Direct Cost Schedule.

This function is only available within an Overhead Cost Estimate.

Syntax:
GetDirectCostValue(“x”) – Where x is the resource type to be referenced. If left blank the GetDirectCostValue function will use all resource types.

Example:
GetDirectCostValue(M) will return the total of all Material costs from the Direct Cost Schedule and enter the value into the Quantity column.

GetDirectCostValue() will return the total of all resource types from the Direct Cost Schedule and enter the value 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.

Ln

This function returns the natural log of a specified value.

Syntax:
Ln(x) – Where x is the value you want to get the natural log of.

Example:
Ln(25) would give the natural log of 25 resulting in a value of 3.219 entered into the Quantity column.

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.

NPQ/PQN/QNP

The NPQ function is used to place the specified value in the Number, Production and Quantity columns of a Production mode Cost Estimate respectively.
Genesis edition only.

Syntax:
NPQ(x,y,z) – Where x is the value entered into the Number column, y is the value entered into the Production column and z is the value entered into the Quantity column.

The NPQ function does not need to be written in a specific order, however, when entered in a different order the parameters will be entered into different columns.

Example:
NPQ(5,10,15) will enter a value of 5 into the Number column, 10 into the Production column and 15 into the Quantity column.

PQN(5,10,15) will enter a value of 5 into the Production column, 10 into the Quantity column and 15 into the Number 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).

RtRes

Calculates a Rate of Resource Usage for a specific Resource name or Resource Name with a Wildcard.

Syntax:
RtRes(x:y,”z”,w) – Where x and y are the start and end points of the lines to be referenced, z is the resource name to be referenced and w is the number to determine the rate.

Example:
RtRes(1:4,”Labour”, 10) will take the sum of lines 1 through 4 where resource name is Labour and divide by 10 to find the rate for the resource.

RtRes(1:4,”Lab*”, 10) will take the sum of lines 1 through 4 where resource name begins with Lab (* is the wild card, whereby you might have Labour, Labour-hire, Labour-tradies etc.

All resources starting with Lab will be included.

RtResUnit

Calculates the rate of usage for a specified resource unit.

Syntax:
RtResUnit(x:y,”z”,w) – Where x and y are the start and end points of the lines to be referenced, z is the resource unit to be referenced and w is the number to determine the rate.

Example:
RtResUnit(1:30, “mhr”, 25) will return the rate of the mhr unit by totalling the units consumed between lines 1 and 30 then dividing the value by 25 and entering the result into 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.

Sm%

The SumPercent formula calculates a percentage of the totals of specified lines and puts that percentage in the quantity column.

Syntax:
SumPercent(x:y,z) or Sm%(x:y,z) – Where x and y are the start and end points of the lines to be referenced and z is the percentage of the sum.

Example:
SumPercent(1:6, 25) or Sm%(1:6, 25) will return 25% of the cumulative total of lines 1 to 6 and enter the value into the Quantity column.

Smq

The Smq function sums the quantity of the specified lines.

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

Example:
Smq(1:10) will return the sum of the quantities on lines 1 through 10 and enter the value into the Quantity column.

SmqTag

The SmqTag formula will return the sum of the quantities of the specified lines containing the defined tag.

Syntax:
SmqTag(x:y,”z”) – Where x and y are the start and end points of the lines to be referenced and z is the tag to be referenced.

Example:
SmqTag(1:30, “Concrete”) will return the sum of the quantities of lines 1 through 30 that have the tag “Concrete” and enter the value into the Quantity column.

SmRes

SmRes calculates the total resource usage for a specified resource within the specified lines.

Syntax:
SmRes(x:y,”z”) – Where x and y are the start and end points of the lines to be referenced and z is the resource to be referenced.

Example:
SmRes(1:30, “Truck”) will return the total resource usage for “Truck” in lines 1 through 30 and enter the value into the Quantity column.

SmResUnit

SmResUnit calculates the total resource usage for a specific resource unit.

Syntax:
SmResUnit(x:y,”z”) – Where x and y are the start and end points of the lines to be referenced and z is the resource unit to be referenced.

Example:
SmResUnit(1:30, “mhr”) will calculate the total mhrs used on lines 1 through 30 and enter the value into the Quantity column.

SmTag

The SmTag function will return the sum of all the specified lines that contain the defined tag.

Syntax:
SmTag(x:y,”z”) – Where x and y are the start and end points of the lines to be referenced and z is the tag to be referenced.

Example:
SmTag(1:30, “Concrete”) will return the sum of the totals of all lines between 1 and 30 that have the tag “Concrete” and enter the value 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:
S qrt(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.

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.

CHECK OUT THESE TIPS AND TRICKS VIDEOS