###  Blog ### iTWO costX® Consultant Series: iTWO costX® Functions

For this month’s Consultant Series blog, UK-based Senior Consultant Milan Parmar has examined 30 useful iTWO costX® functions, providing summaries of how they work and examples of when you might use them to enhance your estimating.

There are a variety of iTWO costX® functions which can be utilised to calculate expressions and draw out values from different sheets. These will help you to become more proficient with iTWO costX®, especially with Workbooks.

Most functions outlined below can be used in the Workbook View, however some can be used in the Dimension Expression Editor such as XNUMBER.

The Examples & Description section shows what the formula looks like and provides a brief explanation on syntax and the parameters/arguments required. The Use Case/Scenario sections will give an explanation on how one might come across the formula in question and a scenario in which it could be used.

Custom Formula:
XGETAREA

Examples & Description:
=XGETAREA(“GFA”)
=XGETAREA(“GFA”,0)

Takes one or two arguments, the name of the dimension group (required), and number of decimal places to roundup to (optional), and it returns the total area of the specified dimension group. Result is displayed in green.

Use Case/Scenario
When dragging a Dimension Group into a Workbook, if Area is chosen as the Quantity Type. This formula will automatically be created.
The measurement of any surface area from a plan, elevation or 3D model including GFA, floor slabs and room areas.

Custom Formula:
XGETCONSTANT

Examples & Description:
=XGETCONSTANT(“Floor Plate 8 mm (Kg/m2)”)
=XGETCONSTANT(“610UB101”,2)

Takes one or two arguments, the name of the Constant (required), and number of decimal places to roundup to (optional), and it returns the value for the specified Constant. Result is displayed in green.

Use Case/Scenario
When dragging a Constant from the Constants List Window, a live-linked value will be created in the workbook.
Examples include density of steel members (kg/m3 or kg/m) and factors for roof pitch.

Custom Formula:
XGETCOUNT

Examples & Description:
=XGETCOUNT(“Chairs”)
Requires one argument, the name of the dimension group, and it returns the number of dimensions in the specified dimension group. Result is displayed in green.

Use Case/Scenario
When dragging a Dimension Group into a Workbook, if Count is chosen as the Quantity Type. This formula will automatically be created
The number of objects in a plan, elevation or 3D model including doors, fixtures and furniture.

Custom Formula:
XGETCUSTOM

Examples & Description:
=XGETCUSTOM(“Concrete Columns”,”Column Anchor Bolts”)

Takes two or three arguments: the name of the dimension group (required), the name of the Custom Quantity (required), and number of decimal places to roundup to (optional). It returns the value of the specified Custom Quantity for the specified dimension group. Result is displayed in green.

Use Case/Scenario
When dragging a Dimension Group into a Workbook, if a Custom Quantity is chosen as the Quantity Type. This formula will automatically be created with the relevant number appended to the formula e.g. XGETCUSTOM2()
The expression and calculation of a custom quantity from a dimension group includes excavation, working space, formwork, bolts, valves and supports.

Custom Formula:
XGETHEIGHT

Examples & Description:
=XGETHEIGHT(“Columns”)
=XGETHEIGHT(“Walls”,2)

Takes one or two arguments, the name of the dimension group (required), and number of decimal places to roundup to (optional), and it returns the value of the default height property for the specified dimension group. Result is displayed in green.

Use Case/Scenario
When dragging a Dimension Group into a Workbook, if Height is chosen as the Quantity Type. This formula will automatically be created.
Returns the Default Height of the chosen dimension group. Includes height of room, thickness of slab or height of building for a façade calculation.

Custom Formula:
XGETGFA

Examples & Description:
=XGETGFA()
=XGETGFA(0)

Returns the current value for the building’s GFA (Gross Floor Area). GetGFA() may also be given one parameter, the number of decimal places to roundup. Result is displayed in green.

Use Case/Scenario
When Dimension Groups have the ‘Add to GFA’ box checked, they will automatically accumulate as the Building’s GFA. When this function is used, it returns the value of the current building’s GFA.

Custom Formula:
XGETLENGTH

Examples & Description:
=XGETLENGTH(“GFA”,0)
=XGETLENGTH(“Columns”)

Takes one or two arguments, the name of the dimension group (required), and number of decimal places to roundup to (optional), and it returns the total length of the specified dimension group. Result is displayed in green.

Use Case/Scenario
When dragging a Dimension Group into a Workbook, if Length is chosen as the Quantity Type. This formula will automatically be created.
Linear measurement from a plan, elevation or 3D model including walls, ductwork, pipework, perimeter of slab, kerbs and columns.

Custom Formula:
XGETLENGTHBREAKDOWN

Examples & Description:
=XGETLENGTHBREAKDOWN(“Division Walls”)

=XGETLENGTHBREAKDOWN(“152 x152 x UC37”)

e.g. 1/3.0 2/3.2 2/20.5 2/20.6 4/23.5
Takes one argument, the name of the dimension group, and it returns a list showing the counts of each unique length within the dimension group and what that length is.

Use Case/Scenario
Returns the count of each unique length in a dimension group.
An example use case would be to report the number of steel members of each length as supplementary information.

Custom Formula:
XGETNAMEDCELL

Examples & Description:
=XGETNAMEDCELL(“Roof Area”)
=XGETNAMEDCELL(“Prelims”,2)
=XGETNAMEDCELL(“CP1″,”Tax”,2)

Takes one, two or three arguments, the name of the workbook (optional), the cell name (required) and number of decimal places to roundup to (optional), and it returns the value of the specified named cell. Result is displayed in green.

Use Case/Scenario
Simply right-click any cell in a workbook and click Name Cell. When dragging a Named Cell from the Workbook Values List Window, a live-linked string/number is automatically created.
Examples include referencing values from the Calc sheet such as percentage for preliminaries or mark-up factors. Named Cells can also be used in Custom Reports to reference a dynamic input such as ‘Client Name’ or ‘Document Type.’

Custom Formula:
XGETPROJECTCELL

Examples & Description:
=XGETPROJECTCELL(“International Airport”,”Hanger 15″,1,”Outline Cost Plan”‘,”Steelwork”)
=XGETPROJECTCELL(“The Smith Project”,”Building 234″,0,”Estimate”‘,”External Works”,2)

Takes five or six arguments, the name of the project, the name of the building, the revision number of the required specific revision (or 0 to use the value from the current revision), the name of the workbook, the cell name and number of decimal places to roundup to (optional), and it returns the value of the specified named cell. Result is displayed in green.

Use Case/Scenario
Using the Filter in the Workbook Values List Window, one can search and has access to all Projects, Buildings and Workbooks in the iTWO costX® database listing all the Named Cells within them.
When dragging a value from here into the current Workbook, a Project Cell will automatically be created.
Examples include the ability to reference total values from another Workbook for benchmarking, labour norms which have been created as Named Cells in other Projects.

Custom Formula:
XGETRATE

Examples & Description:
=XGETRATE(“Rate Library 1″,”1.1.3”,2)
=XGETRATE(“1.1.3”)
=XGETRATE(“1.1.3”,2)
=XGETRATE(“Rate Library 1″,”1.1.3”)
=XGETRATE(A1,2)

Takes between one and three arguments. The three arguments in order are the name of the rate library (optional), the item code (required) and the number of decimal places to roundup to (optional), and it returns the specified rate from the specified (or default) rate library. Result is displayed in green.

Use Case/Scenario
When dragging a Rate from the Rate Library list window into the Workbook, this formula is automatically created.
Examples include linear rates for Labour, Plant, Material, Subcontractor. They can also be built up to create composite rates. Gang rates can be built from labour rates and assemblies can be produced using a mixture/recipe of live-linked rates. Rates can be built up in a Workbook, directly in System Administration or from an Excel spreadsheet and imported into iTWO costX®.

Custom Formula:
XGETRATEDESCRIPTION

Examples & Description:
=XGETRATEDESCRIPTION(“Rate Library 1″,”1.1.3”)
=XGETRATEDESCRIPTION(“1.1.3”)

Takes one or two arguments, the name of the rate library (optional) and the rate item code (required), and it returns the specified rate’s description from the specified (or default) rate library. Result is displayed in green.

Use Case/Scenario
When dragging a Rate into the Workbook, if the ‘Use Rate Description’ checkbox is ticked, the Dimension Group Name will attempt to match with an Item Code in the Default Rate Library. If a match is found, the description from the rate will be used rather than the Dimension Group Name.
This is useful when Dimension Group names are generic and the rate description is more fitting for the bill item.
For example:
Dimension group name = “920mm Doors”.
Item code = “920mm Doors”
Rate Description – “Single timber door, veneered both sides; 920 x 2040 x 40 thick including hardwood timber frame and all hardware complete.

Custom Formula:
XGETSPACEDCOUNTRND

Examples & Description:
=XGETSPACEDCOUNTRND(“External Walls”,0.9)
=XGETSPACEDCOUNTRND(“Foundations”,0.6,0)

Takes two or three arguments, the name of the dimension group (required), the spacing (required), and the number of decimal places to roundup to (optional), and it returns the total number of weep-holes/starter-bars etc. at the specified centres for the selected dimension group based on the length of each dimension rounded to three decimal places. Result is displayed in green.

Use Case/Scenario
This formula can be used to find the number of instances in a length or perimeter after specifying the spacing.
Examples include the number of vertical bars in a basement wall, number of studs in a partition or number of valves in a pipework system.

Custom Formula:
XGETSPACEDLENGTH

Examples & Description:
=XGETSPACEDLENGTH(“Internal Walls”,0.45)
=XGETSPACEDLENGTH(“Reo”,0.4,0)

Takes two or three arguments, the name of the dimension group (required), the spacing (required), and the number of decimal places to roundup to (optional), and it returns the total length of bars/studs etc. at the specified centres for the selected dimension group based on the length and height of each dimension. Result is displayed in green.

Use Case/Scenario
This formula can be used to find the total length of bars or studs after specifying the spacing. The Default Height is used to multiply the number of instances to return the total length. Rounding is optional.

Custom Formula:
XGETSPACEDLENGTHRND

Examples & Description:
=XGETSPACEDLENGTHRND(“Internal Walls”,0.45)
=XGETSPACEDLENGTHRND(“Reo”,0.4,0)

Takes two or three arguments, the name of the dimension group (required), the spacing (required), and the number of decimal places to roundup to (optional), and it returns the total length of bars/studs etc. at the specified centres for the selected dimension group based on the length and height of each dimension rounded to three decimal places. Result is displayed in green.

Use Case/Scenario
This formula can be used to find the total length of bars or studs after specifying the spacing. The Default Height is used to multiply the number of instances to return the total length. Rounding is optional.

Custom Formula:
XGETVALUE

Examples & Description:
=XGETVALUE(“Builders Margin Allowance”)

=XGETVALUE(“Wastage on Concrete”,2)

Takes one or two arguments, the name of the Value (required), and number of decimal places to roundup to (optional), and it returns the value for the specified Value. Result is displayed in green.

Use Case/Scenario
When dragging a Value from the Value List Window, a live-linked value will be created in the workbook.
Examples include allowances, wastage factors, coefficients for steel and concrete.

Custom Formula:
XGETVOLUME

Examples & Description:
=XGETVOLUME(“GFA”)
=XGETVOLUME(“GFA”,2)

Takes one or two arguments, the name of the dimension group (required), and number of decimal places to roundup to (optional), and it returns the total volume for the specified dimension group. Result is displayed in green.

Use Case/Scenario
When dragging a Dimension Group into a Workbook, if Volume is chosen as the Quantity Type. This formula will automatically be created.
Examples include the volume derived from areas from a plan or elevation. Volume can be extracted more easily from a 3D model.

Custom Formula:
XGETWALLAREA

Examples & Description:
=XGETWALLAREA(“FECA”)
=XGETWALLAREA(“GFA”,0)

Takes one or two arguments, the name of the dimension group (required), and number of decimal places to roundup to (optional), and it returns the total wall area for the specified dimension group. Result is displayed in green.

Use Case/Scenario
When dragging a Dimension Group into a Workbook, if Wall Area is chosen as the Quantity Type. This formula will automatically be created.

Examples include the total surface area derived from entering heights from Area and Length measurements for blockwork, wall finishes and formwork.

Custom Formula:
XGETWEIGHT

Examples & Description:
=XGETWEIGHT(“200 UC 46”)
=XGETWEIGHT(“16mm Ø rebar”,3)

Takes one or two arguments, the name of the dimension group (required), and number of decimal places to roundup to (optional), and it returns the total weight for the specified dimension group. Result is displayed in green.

Use Case/Scenario
When dragging a Dimension Group into a Workbook, if Weight is chosen as the Quantity Type. This formula will automatically be created.
In Measured Dimensions, Weights can be derived from Length, Area or Volume measurements. Typical examples include tonnage of steel columns and beams, reinforcement in concrete and ductwork.

Custom Formula:
XGETWIDTH

Examples & Description:
=XGETWIDTH(“Footings”)
=XGETWIDTH(“Walls”,1)

Takes one or two arguments, the name of the dimension group (required), and number of decimal places to roundup to (optional), and it returns the value of the default width property for the specified dimension group. Result is displayed in green.

Use Case/Scenario
When dragging a Dimension Group into a Workbook, if Width is chosen as the Quantity Type. This formula will automatically be created.
Returns the Default Width of the chosen dimension group. Includes width of strip foundation or width of concrete when measuring from a cross section.

Custom Formula:
XGETWORKBOOKTOTAL

Examples & Description:
=XGETWORKBOOKTOTAL(“Budget”)
=XGETWORKBOOKTOTAL(“CP1”,2)

Takes one or two arguments, the name of the workbook (required), and number of decimal places to roundup to (optional), and it returns the total for the specified workbook. Result is displayed in green.

Use Case/Scenario
When dragging the Workbook Name from the Workbook Values List Window, a live-linked string/number is automatically created which is the total of the chosen Workbook.
Examples include referencing values from the same Project for benchmarking and to track total values in another Workbook. Can only be used with Buildings from the same Project.

Custom Formula:
XSUMQTY

Examples & Description:
=XSUMQTY(2)
Takes one parameter, the number of decimal places to roundup. Returns the total of the values in the Quantity column of the Qty sub-sheet (column G of the Cost sub-sheet). Result is displayed in blue.

Use Case/Scenario
When double clicking on a Quantity cell and drilling down into a Quantity Breakdown. This formula is automatically created by iTWO costX.
If a build up for a quantity is required, for example, multiple dimensions groups must be used and calculations need to be made, use of this formula is ideal. Typical examples include excavation, backfilling, waterproofing and formwork plus many more.

Custom Formula:
XSUMQTYUSER

Examples & Description:
=XSUMQTYUSER(2)

=XSUMQTYUSER(3,2)

Takes one or two parameters, the user column number from the Qty sub-sheet and the number of decimal places to roundup. Returns the total of the values in the specified user column of the Qty sub-sheet (e.g.XSUMQTYUSER(3) will sum the column User3 of the Qty sub-sheet).

Use Case/Scenario
This formula can be used to sum the values from a selected column in the Quantity Breakdown Sheet.
An example would be to sum all deductions in a user column in the Quantity Breakdown sheet giving users the ability to utilise and report the information on the level above.

Custom Formula:
XSUMRATE

Examples & Description:
=XSUMRATE(0)
Takes one parameter, the number of decimal places to roundup. Returns the total of the values in the Include column of the Rate sub-sheet (column H of the Cost sub-sheet). Result is displayed in blue.

Use Case/Scenario
When double clicking on a Rate cell and drilling down into a Rate Breakdown. This formula is automatically created by iTWO costX.
If a rate build-up is required, using a combination of linear rates and composite rates, use of this formula is ideal. Common examples include a mixture of resources (labour, plant and materials).

Custom Formula:
XSUMRATEUSER

Examples & Description:
=XSUMRATEUSER(2)
=XSUMRATEUSER(3,2)

Takes one or two parameters, the user column number from the Rate sub-sheet and the number of decimal places to roundup. Returns the total of the values in the specified user column of the Rate sub-sheet (e.g.XSUMRATEUSER(2) will sum the column User2 of the Rate sub-sheet).

Use Case/Scenario
This formula can be used to sum the values from a selected column in the Rate Breakdown Sheet.
Extremely useful when users wish to report total labour hours and material prices. The ability to multiply this formula with the Quantity allows common workflows to be implemented.

Custom Formula:
XSUMTOT

Examples & Description:
=XSUMTOT(0)
Takes one parameter, the number of decimal places to roundup. Returns the total of the values in the Total column of the Cost sub-sheet (column H of the Cost sub-sheet). Result is displayed in blue.

Use Case/Scenario
When double clicking on a Subtotal cell and drilling down into a Cost Breakdown. This formula is automatically created by iTWO costX®.
Workbook levels are used in iTWO costX® to create a hierarchical structure and to breakdown costs. Common examples would be 3 level breakdowns for cost plans or 2 level breakdowns for Bills of Quantities.

Custom Formula:
XSUMTOTQTY

Examples & Description:
=XSUMTOTQTY(0)
Takes one parameter, the number of decimal places to roundup. Returns the total of the values in the Quantity column of the Cost sub-sheet (column C of the Cost sub-sheet). Result is displayed in blue.

Use Case/Scenario
This formula can be used to sum the Quantity values from the Cost Breakdown Sheet.
Extremely useful to report Elemental Unit Quantities (EUQs) on the level above to gain a better understanding of an element. Can be used in conjunction with Exclude Quantities to enable more accurate values.

Custom Formula:
XSUMUSER

Examples & Description:
=XSUMUSER(6)
=XSUMUSER(1,0)

Takes one or two parameters, the user column number from the Cost sub-sheet and the number of decimal places to roundup. Returns the total of the values in the specified user column of the Cost sub-sheet (e.g.XSUMUSER(6) will sum the column User6 of the Cost sub-sheet).

Use Case/Scenario
This formula can be used to sum the values from a selected column in the Cost Breakdown Sheet.
Commonly used to sum values from a level below which contain labour and material costs. Also used to group sections of work into User columns.

Custom Formula:
XNUMBER

Examples & Description:
=XNUMBER(“ABC 100 DEF 200 GHI 300”, 2)

=XNUMBER(B10,2)

Takes two arguments, the first of or referring to a text string, and the second an integer representing the nth number to abstract from the string (e.g =XNUMBER(“ABC 100 DEF 200 GHI 300,2) will return 200).

Use Case/Scenario
This formula returns a numerical value in a string which has been specified in the formula. For example, I have the following string:
“70 x 70 x 57”
I would like my formula to return the 3rd number which is 57. Therefore, my formula would be as follows:
=XNUMBER(“70 x 70 x 57”,3)
The 3 after the comma indicates that I would like to return the 3rd number.
Common examples include referencing the weight of steel member like the above, or using it to extract number from strings in a BIM schedule.

Custom Formula:
XTEXT

Examples & Description:
XTEXT(text)
Evaluates its argument as text and returns the result as text.
XTEXT(“2+3”) returns 2+3

Use Case/Scenario
This formula will evaluate an argument as text instead of evaluating the expression.