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: If left blank, the w variable will include all resource types. Example: 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: Example: |
#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: Example: |
#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: Example: |
#QTY |
The “#QTY returns the schedule quantity of the item you’re currently editing. This define is automatically created and cannot be redefined. Syntax: Example: |
#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: Example: |
Arccos |
The Arccos function will return the Arc cosine of a specified value in either degrees or radians. Syntax: Arccos(x, "y") where x is the the value, and y is the desired result (either degree or radian) Example: Note: Degrees: Radians: |
Arcsin |
The Arcsin function will return the arc sine of a specified value in either degrees or radians. Syntax: Example: Note: Degrees: Radians: |
Arctan |
The Arctan function will return the arc tangent of a specified value in either degrees or radians. Syntax: Example: Note: Degrees: Radians: |
Ceiling |
The Ceiling formula will round the specified value up to the nearest whole number. Syntax: Example: |
CirArea |
The CirArea function will return the area of a circle of the radius defined. Syntax: CirArea(x)/y – Where x the radius and the total area is then divided by y. Example: |
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: Example: |
CirFrustumVol |
CirFrustumVol returns the volume of the frustum of a cone calculated on defined values for upper and lower radii and the height. Syntax: Example: 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: Example: |
CirSegArea |
This function returns the area of a segment of a circle. Syntax: Note: The distance (y) must be less than the radius (x). Example: |
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: Note: The distance (y variable) must be less than the radius (x variable). Example: |
ConeSlopeArea |
This function is used to calculate the surface area of the sloped face of a cone. Syntax: Example: |
ConeVol |
The ConeVol function returns the volume of a cone. Syntax: Example: |
Cos |
This function is used to find the Cosine of a specified angle. Syntax: Example: |
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: Example: |
EllipseArea |
EllipseArea returns the area of an ellipse of a specified size. Syntax: Example: |
Exp |
This function is used to return Exponential growth rate of a specified value. Syntax: Example: |
Floor |
The Floor formula will round the specified value down to the nearest whole number. Syntax: Example: |
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: Example: GetDirectCostResUnit(“we*”) will return the total usage of any resource unit prefixed with "we" 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: Example: GetDirectCostResUse(“lab*”) will return the total usage of prefixed with "lab" 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: Example: 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: Example: |
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: Example: 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: Example: |
Log |
The Log function returns the Base 10 Log of a specified value. Syntax: Example: |
Max |
The Max function is used to return the maximum value within a specified set of numbers. Syntax: Example: |
Min |
The Min function is used to return the minimum value within a specified set of numbers. Syntax: Example: |
Mod |
The Mod function is used to return the remaining value after dividing one operand by a second. Syntax: Example: |
Not |
The Not inverts a logical (true or false) value. Syntax: Example: |
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. Syntax: 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: 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: Example: |
PyramidVol |
Use this function to return the volume of a Pyramid. Syntax: Example: |
Q / SetQuantity |
Q or SetQuantity is used to place the specified value in the quantity column. Syntax: Example: |
RectArea |
The RectArea function outputs the area of a rectangle of the specified dimensions. Syntax: Example: |
RectCircum |
The RectCircum function returns the length of the outer perimeter of a rectangle of the specified dimensions. Syntax: Example: |
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: Example: |
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: Example: |
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: Example: 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: Example: 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: Example: 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: Example: RtResUnit(1:30, “*ton”, 25) will return the rate of any unit prefixed with "ton" 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: Example: |
Sm% |
The SumPercent formula calculates a percentage of the totals of specified lines and puts that percentage in the quantity column. Syntax: Example: |
Smq |
The Smq function sums the quantity of the specified lines. Syntax: Example: |
SmqTag |
The SmqTag formula will return the sum of the quantities of the specified lines containing the defined tag. Syntax: Example: SmqTag(1:30, “*con”) will return the sum of the quantities of lines 1 through 30 that are prefixed with "con" and enter the value into the Quantity column. |
SmRes |
SmRes calculates the total resource usage for a specified resource within the specified lines. Syntax: Example: SmRes(1:30, “*Tru”) will return the total resource usage for any resource prefixed with “Tru” 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: Example: SmResUnit(1:30, “*we”) will calculate the total of any resource prefixed with "we" 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: Example: SmTag(1:30, “*Con”) will return the sum of the totals of all lines between 1 and 30 prefixed with “Con” and enter the value into the Quantity column. |
SphereArea |
The SphereArea function returns the surface area of a sphere of a specified size. Syntax: Example: |
SphereVol |
The SphereVol function returns the volume of a sphere of a specified size. Syntax: Example: |
Sqrt |
The Sqrt function returns the square root of a specified value. Syntax: Example: |
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: Example: |
Sum |
The Sum function is used to sum (add) the Total of the lines specified within the formula. Syntax: Example: 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: Example: |