FREEWARE DOWNLOADS

Formulae Functions & Operators

Introduction
Worksheets
Editing Commands
Running Calculix
Autocode
Autotrad
Abstract
Elements
Formulae Functions & Operators
Creating New Elements
Room Data
Vertical Data
Pipelines
Element Schedules
Worked-Up Data
Descriptions
Validation
General Bill Data
Site Layout
Formatting
Processing
Pricing
Valuations
Printing
File Maintenance
Master Libraries
Filing Utilities

 

Formula/Functions

  1. Formulae, functions and operators
  2. Formula entries
  3. Maths operators in formulae
  4. Dimensions in Formulae
  5. Special formula functions
  6. Mathematical Precedence in formulae
  7. Maths operators in Formulae
  8. + - * / in Formulae
  9. Trigonometrical Functions sin ( cos tan asn acn atn ) exp 
  10. Number Operators int rnd rup rt in Formulae
  11. int (in Formulae)
  12. rnd ( In Formulae)
  13. rup (in Formulae)
  14. rt (in Formulae)
  15. Brackets () [] {} (in Formulae)
  16. OR | (in Formulae)
  17. Component sizes in Formulae
  18. Quantities from other Items in Formulae
  19. Additional Dimensions - Variable parameters in Formulae
  20. Numerical Values in Formulae
  21. Internal References in Formulae
  22. Dimensions from the room data - external references in formulae
  23. Constants in Formulae
  24. Special Functions in Formulae
  25. Ranges in Formulae
  26. Under q; U exp, limit 1 in Formula Ranges
  27. Range q; R exp, limit 1, limit 2 in Formula Ranges
  28. Over q; O exp, limit 1 in Formula Ranges
  29. Equals q; E exp, limit 1 in Formula Ranges
  30. Example of a range
  31. Use of ranges generally in Formulae
  32. ADDERS q;a999 (a/999) in formulae
  33. Adder Codes in Formulae
  34. Using Adders in Formulae
  35. Incorporating Adder Quantities into the Bill (in Formulae)
  36. Adder Code Descriptions in Formulae
  37. Item adders q;n exp (a/n) in formulae
  38. Nested Elements q"aaaa99" in formulae
  39. Dimensions in text q;t field (a/t) in Formulae
  40. Formula memory buffer

 

Formulae, functions and operators

The Room Data and the Element data contain all of the measurement information that the Quantity Surveyor would normally use to prepare the Bill of Quantities. This Chapter describes how the computer uses this measurement data to calculate the quantities. Whereas traditional measurement processes record quantities as actual numbers calculix tries, wherever possible, to store the quantities as formulae expressing, in general terms, how the quantities are calculated.

e.g. the amount of plaster on a wall 6.000 long x 2.400 high will be held in calculix in the formula wall length x wall height. Expressed in this form the formula represents the general case and is valid for every wall of it's type whereas using the dimensions 6 x 2.4 is obviously of very limited value since it only applies to identical walls.

By getting the computer to look up the wall length and room height when it calculates the quantity we are able to maximise the amount of work done by the computer and in this way we introduce real Computer Generated Quantities.

As a result of the calculix approach a number of sophisticated features are possible to reduce the risk of error and to improve the accuracy of the final Bill. Some of these features involve the use of Formulae and these are explained in this Chapter.

 

Back to Top

Formula entries

Formulae are traditional mathematical expressions of how the computer is to obtain the quantity for each and every item in the Bill. It follows that every Bill item must have a Formula which describes which dimensions are needed for the calculation and how the calculation is to be performed.

The Formulae themselves are very similar to those used in Computer Spreadsheets and as such should present no difficulty. The principal area of interest is the method of referencing which is used to enable the computer to access the Room and Element data.

Where numbers are expected these can include complex expressions provided that they are enclosed by brackets so that they will resolve to a number before further processing. Throughout this section any reference to a number will include a numerical expression and vice versa.

The Formulae are subjected to extensive and pedantic error checking. Formulae processing disregards all spaces. Formulae may therefore incorporate spaces if this assists with presentation. The following terms are valid for use in formulae.

 

Back to Top

Maths operators in formulae

+ (add)
- (subtract)
* (multiply)
/ (divide) 
^ (raise to the power of)

 

sin
cos
tan
asn
acs
atn
int
rnd
rup
rt
()
[]
{}
 | (OR)

 

Back to Top

Dimensions in Formulae

Dimensions from the Element Data- "Internal" References in formulae

l, w, h, q, 99l, 99w, 99h, 99q
L, W, H, A, B, C
numerical values

Dimensions from the Room Data - "External" References in formulae

il, ml, ol
pr na, ga, fa
sh, fh, ch, ff

 

Back to Top

Special formula functions

RANGE
Under - q;U exp,1
Range - q;R exp,1,2
Over - q;O exp,1
Equals - q;E exp,1
 ADDER q;a999 (a/999)
ITEM ADDER q;n exp (a/n)
NESTED ELEMENT q"AAAA99"
DIMENSION IN TEXT q;t field (a/t)

 

Back to Top

Mathematical Precedence in formulae

Formulae are evaluated in accordance with the usual rules of mathematical precedence i.e. () ; ^ ; * / ; + - and brackets must be used as necessary to ensure the correct order of processing.

 

Back to Top

Maths operators in Formulae

The operations that can be performed in the Calculix Formulae are extensive and include all the usual mathematical processes. The following entries are allowed:

Back to Top
 

+ - * / in Formulae

The basic maths operators for addition, subtraction, multiplication and division ( + - * / ) can be used.

NOTE the ASCII symbols used for multiplication and division.

The power of a number is obtained by using ^ followed by the number representing the power required.

 

Back to Top
 

Trigonometrical Functions sin ( cos tan asn acn atn ) exp

The standard Trigonometrical functions sine; cosine and tangent followed by an integer or expression will return the appropriate value. The functions will only operate on an integer or expression producing an integer result.

The inverse trig. functions arcsine asn, arccosine acn and arctangent atn can also be used. These will return the angle, in degrees, appropriate to the specified number.

 

Back to Top
 

Number Operators int rnd rup rt in Formulae

The number operators perform on the fixed point number (or expression) that follows the operator. Where expressions are used they must be contained in brackets.

Back to Top
 

int (in Formulae)

returns the whole number part of the number or expression

i.e. it discards the decimal part to the right of the decimal point ( int 6.7 == 6 ).

e.g. -0.175*length deducts the total area of brickwork displaced by a 0.175 deep lintel. whereas -int(.175/.075)*.075*length deducts only the full courses of brickwork displaced by the same lintel.

 

Back to Top
 

rnd ( In Formulae)

Returns the number or expression rounded to the nearest whole number.

(rnd 6.7 == 7 ; rnd 6.3 == 6).

 

Back to Top
 

rup (in Formulae)

Returns the number or expression rounded up to the next whole number.

( rup 6.3 == 7 ; rup 6.7 == 7 )

 

Back to Top
 

rt (in Formulae)

Returns the square root of the number or expression.

 

Back to Top
 

Brackets () [] {} (in Formulae)

Brackets are used to ensure the correct mathematical precedence in a formula. Three sets of brackets are available; Round brackets (); Square brackets [] and Curly brackets or Braces {. There is no special significance to any of the brackets except that when used they must be used in matching pairs. There is no requirement to use different types of brackets in overlapping expressions and the same brackets can be used as often as required in any formula. The choice is provided to aid readability if required.

 

Back to Top
 

OR | (in Formulae)

The | OR operator is a highly specialised feature for use with RANGES.

The procedure with RANGES is that a series of items are the subject to a test the result of which is that, in any given situation, only one of the items can survive. It is sometimes useful to use the quantity from the Range in the Formula for another item but this can't be done directly because it isn't known which of the items will survive.

The basic procedure is to ADD the Formulae of all of the items in the RANGE together. This will include the quantity of the only valid item which will contain the correct quantity. The problem is that all of the unsuccessful items will have a quantity of NULL and the ADD operator can't perform maths on NULL values. The OR operator performs like an ADD operator except that it will treat NULL values as if they were ZERO. In addition the OR operator checks to make sure that there is only one real value being used. If there is more than one non-null value it will report an error.

 

Back to Top
 

Component sizes in Formulae

A large number of the calculations performed by the Quantity Surveyor in working out the quantity of an item involve the dimensions of a closely related component. Girthing, side-casts, calculation of the area of a door for painting etc. etc. all involve dimensions of related components. In the Calculix system all of the component sizes are stored in the Element Data and these can be accessed by reference to the appropriate l, w and h fields.

References to simply l, w or h will access the dimension in the l, w and h columns of the line in question. Reference to these fields on any other line must be prefixed with the appropriate line number,

e.g. 2w refers to the dimension contained in the w field on line 2.

 

Back to Top
 

Quantities from other Items in Formulae

It often happens that the same formula is used by several different items. It also happens that some items can use the formula of another item as a PART of its formula. In these cases the complete formula can be referenced by the character q (stands for quantity). If q is used on it's own it will use the value of the formula on the current line. To use the value of a formula from some other line q must be preceded by the appropriate line number.

This device can also be used to create formulae which are too long to fit onto one line.

e.g.4q will return the value of the formula on line 4.

NOTE The value q is the number which will be returned by the entire formula line and not just the formula proper. Where SPECIAL FUNCTIONS are used these will modify the value of q according to their function. For example if a formula evaluates to 11 but is then subjected to a RANGE which excludes the item the value of q will be altered to NULL. Any reference to this formula line will therefore also acquire the value NULL. Formulae lines which contain special functions must, for this reason, be used carefully to ensure that the two items move in perfect harmony with each other.

e.g. Painting on skirting would probably not be needed if the skirting was omitted so using the skirting formula in the painting item will have the elegant effect of harmonising the two items. If the skirting item becomes NULL then so does the painting item.

The scratch pad can be very useful in setting up calculations which are used by several items.

 

Back to Top
 

Additional Dimensions - Variable parameters in Formulae

In addition to dimensions which are the sizes of components themselves there are other dimensions which are intimately associated with some Elements although not themselves actually component sizes. The invert level and cover level of a manhole, for instance, are vital to the quantities of several items in a manhole and yet they are not themselves sizes of individual components. These dimensions are called VARIABLES and are placed in the first six fields of the ELEMENT worksheet. The fields are labelled L, W, H, A, B and C respectively and the data in them can be accessed by using these references.

The extent to which the VARIABLE data is used crucially depends on the type of Element. Some Elements will make extensive use of these fields and others will not use them at all. If the information is relevant to an Element but is not itself a size of one of the components then the data should be inserted in one of the Variable data fields.

 

Back to Top
 

Numerical Values in Formulae

It is important to remember that actual dimensions can be used in Formulae whenever convenient. Whilst they will almost certainly not be as flexible as a cross reference to some other value it will often be practically expedient to enter the dimension directly and not worry about the relationships with other components. Just remember that the values will almost certainly have to be changed if you want to use that Element again.

To assist with the use of these functions, all numbers in formulae can be appended with mm for millimetres or km for kilometres, as an alternative to decimal metres, if required to improve the readability of the worksheet.

 

Back to Top
 

Internal References in Formulae

All of the above dimensions are contained on the Element worksheet and are therefore immediately available when the Element is processed. Because formulae containing this type of data can be processed without having to look up any other data these type of references are called Internal References.

 

Back to Top
 

Dimensions from the room data - external references in formulae

Aside from the component sizes all of the quantity surveyor's calculations are based on the size of the Building itself. Calculix analyses the building into it's constituent rooms for this purpose and therefore all of these dimensions are obtained from the appropriate room data. Because the computer has to go away and look up the room data these dimensions are called External references.

The following External References are currently in use:

Lengths

il- internal length as input
ml- mean length of wall
ol- outside length of wall
pr- perimeter of a room

Heights

sh- storey height
fh- floor height
ch- ceiling height
ff - floor finish thickness

Areas

na- net floor area
ga- gross floor area
fa- floor area

It is helpful to understand one further distinction between internal and external references. Internal references are fixed for an entire contract whereas external references change from room to room and from floor to floor. The method of processing external references is therefore to enter a room, look up the necessary information, perform the calculations and move on.

There is only a definitive value for a Formula containing External References when the Formula is evaluated for a particular room. It is the flexibility of the External References that enables calculix to GENERATE the quantities for the bill.

 

Back to Top
 

Constants in Formulae

The numerical constant pi will return the value 3.1416.

 

Back to Top
 

Special Functions in Formulae

To use the computer to maximum advantage, Calculix contains some special functions to deal with situations of particular interest to Quantity Surveyors.

The Special Functions are designed to follow the Formula proper and provide some additional processing which uses or modifies the value of the basic formula. The whole of the formula expression must precede the Special Function and a semi-colon ; will usually be used to separate the Formula from the Special Function, and the Special Functions from each other, if more than one is used.

The special functions currently supported are:

Ranges
Adders
Item Adders
Nested Elements
Dimensions in Text

All of these functions have been specially developed for quantity surveying purposes and their use will greatly increase the speed and flexibility of use of the calculix software.

 

Back to Top
 

Ranges in Formulae

It is a frequent requirement of quantity surveying that items must be arranged in groups in order of ascending size. Calculix provides for this by using special functions called Ranges.

A Range is divided into four sections:-

U (under) R (range) O (over) and E (equals)

 

Back to Top
 

Under q; U exp, limit 1 in Formula Ranges

An Under will pass the value q of the formula to the left of the semi-colon if the expression is less than or equal to the value of limit 1. Otherwise it will pass a NULL value for the quantity and the item will be disregarded in the Bill.

 

Back to Top
 

Range q; R exp, limit 1, limit 2 in Formula Ranges

A Range will pass the value q of the formula to the left of the semi-colon if the expression is greater than limit 1 and less than or equal to limit 2. Otherwise it will pass a NULL value for the quantity of q and the item will be disregarded in the Bill.

 

Back to Top
 

Over q; O exp, limit 1 in Formula Ranges

An Over will pass the value q of the formula to the left of the semi-colon if the expression is greater than limit 1. Otherwise it will pass a NULL value for the quantity q and the item will be disregarded in the Bill.

 

Back to Top
 

Equals q; E exp, limit 1 in Formula Ranges

A special extension to the Range facility is the test for equality between two items. The Equals function will pass the value q of the formula if the expression is Equal to limit 1.

 

Back to Top
 

Example of a range

                            # Formula

Excavation ne 1.00    1    ml * h * W; Uh, 1

              ne 2.00    2    ml * h * W; Rh, 1,2

           ne 4.00    3    ml * h * W; Oh, 2

Cart Away             4    1q | 2q | 3q

In this example ml is the length of a trench, h is the depth and W is the Width. So that ml x h x W is the volume of trench excavation and forms the Basic quantity of the item.

1. This line checks to see if the expression h is Under or equal to the value 1. If it is this item will return a real value ml x h x W for the quantity q. If it isn't, it will return a NULL value.

2. This line checks to see if the expression h is in the Range exceeding 1 and not exceeding 2. If it is the item will return a real value ml x h x W for the quantity q. If it isn't, it will return a NULL value.

3. This line checks to see if the expression h is Over the value 2. If it is the item will return a real value of ml x h x W for the quantity q. Otherwise it will return a NULL value.

4. The quantity of cart away is required for the relevant item of excavation only. Since, however, the procedure of the RANGE is such that only one item can have a real value and all the others must be NULL, we can use a special operator to get the desired result. The special operator | (vertical line or bar) will return a real value from a list of values where only one value can be real.

It is important to notice that whilst the "formula" produces the Basic quantity, the Actual quantity q is the final value of each line. Where a formula is followed by a special function this may modify the formula and leave q with a NULL value. References to the q value of another line must bear this is mind. Otherwise it may accidentally have a NULL value by mistake.

It is wise to always start a RANGE with an Under and end it with an Over. This ensures that there are no "loose ends".

 

Back to Top
 

Use of ranges generally in Formulae

Ranges have two main applications:-

(i) to automatically cater for SMM requirements to specify items according to a size classification. e.g. depth of excavation not exceeding 250mm; over 250mm but not exceeding 1m; over 1m but not exceeding 2m, etc.

The Range will be used on a set of items covering the usually encountered depths and each will refer to the "excavation depth" to see if the item is relevant. For any given installation the Range will automatically select the appropriate item and the inappropriate items will be ignored by the Bill process.

The excavation example is a case of an open ended range and in such a case it is desirable to specify the largest item in any Element as an Over since in this way even if the Range is inadvertently exceeded, an item will still be generated, albeit with only the nearest description. If the maximum depth covered by a group of items is exceeded and a Range is used for the largest item, none of the items will satisfy the criterion and no Bill item will be produced.

Note that a Range could be used in place of an Under if the limit 1 of the Range was entered as 0. The syntax of limit 1, however is "greater than" and the use of a Range in this way does not adequately cover the possibility of limit 1 being = 0.

(ii) Ranges also allow the system to test a deduction before passing it on to the Bill to ensure that it satisfies the SMM minimum deduction requirements. In this case an Over will be used comparing the formula quantity q with the minimum deduction value from the SMM inserted as limit 1. If q is less than or equal to limit 1 the deduction will not be made, but if it is greater than limit 1 the deduction will be made in the usual way.

 

Back to Top
 

ADDERS q;a999 (a/999) in formulae

The SMM requires that some descriptions should include quantities which outline the general extent of the work.

e.g. CONCRETE FRAMED STRUCTURE Approx. 174 m{

The value 174 is an accumulation of all the appropriate items in the Bill and calculix provides for this situation with the use of Special Functions called Adders.

 

Back to Top
 

Adder Codes in Formulae

The requirement is met by the use of Adders. In order that sufficient Adders are available for normal purposes 999 are available and each one is identified by a number from 1 to 999. Each Adder code is preceded by the letter a to identify it as an Adder code.

 

Back to Top
 

Using Adders in Formulae

To have the quantity q of any item added to the contents of an Adder you merely enter the Adder code at the end of the Formula using a semi-colon ; to separate it from the Formula.

As many items as required may contribute to the adder. In some cases like the "CONCRETE FRAMED STRUCTURE Approx. a/999 m{", the Adder will be inserted against all concrete items in the contract. In other cases such as "The following in a/999 Nr manholes" an item such as the manhole cover will be selected as generating the appropriate quantity and the Adder will be attached to the formula for that item. Every time the element is reprocessed the Adder will also be adjusted. Sometimes it may be necessary to use a scratchpad line to create the required quantity.

A Formula can contain more than one Adder but each one must be preceded by a semi-colon to separate it from the other parts of the Formula. Further adjustments can be made to Adders by using the Adder code on a scratchpad line as necessary.

 

Back to Top
 

Incorporating Adder Quantities into the Bill (in Formulae)

The numerical contents of an adder will be printed out in any description containing the appropriate Adder Code. As many descriptions may contain the Adder Code as required and up to 999 Adder Codes can be created as needed. Note that a description may contain more than one Adder, as required, but each one must, of course, have the appropriate code. Note also that the adder routine simply exchanges the adder code for the numerical contents of the corresponding accumulator and any associated units are a separate piece of text which must be included as part of the Description itself.

To assist in distinguishing between Adder codes and catalogue reference numbers the code used in the description field has a / added to it separating the a from the number. This / is not used in the corresponding entry in the formula.

 

Back to Top
 

Adder Code Descriptions in Formulae

Each Adder code can have a description explaining the purpose of the Adder. The Adder code Description is displayed when the <F11> key is pressed whilst the cursor is located in the formula field. A list of Adders and their descriptions is also available by pressing the <HOME> key.

The Adder code Description is created or edited by using the Item Adder option on the Description Menu.

Adder code descriptions are stored in both Master and Contract Description Libraries. All Contract rogues are automatically entered in the Contract Library.

 

Back to Top
 

Item adders q;n exp (a/n) in formulae

Some Bill of Quantity items require the description to say how many items are included. In this special case the item code itself uniquely identifies which items should be added together and there is therefore no need to use an Adder code.

Where the adder is working at item level enter an n after the formula followed by the number you want to add to the adder each time the item is processed. The number will, of course, usually be one. A semi-colon separator should be inserted between the formula and the n.

The item adder is incorporated into the description text by including an a/n in the description at the appropriate position.

 

Back to Top
 

Nested Elements q"aaaa99" in formulae

It is often convenient for an item within an Element to call forward a number of related Bill items. Calculix does this by allowing an Element to include another Element. The Element code is recognised by being enclosed in inverted commas and it must be preceded by a valid formula. Normally the formula will be the numerical value one but it can be any valid formula expression.

The Element is treated in exactly the same way as previously described and no special rules apply to Elements when used in this way.

Because the Element code is identified by being enclosed in quotes the computer has no trouble understanding the formula and in this case, therefore, there is no need for the semi-colon separator between the formula and the Element code.

This "nesting" of Elements, one inside the other, can be performed up to 5 deep (i.e. an Element within an Element within an Element within an Element within an Element). If more than 5 nests are used the Message "Too many nested Elements" will appear in the Calculate Bill Report. This limitation is arbitrary and could be replaced if necessary. There is no restriction to the number of times that nested Elements can be used.

 

Back to Top
 

Dimensions in text q;t field (a/t) in Formulae

The component sizes of each item can be automatically incorporated in the description of the item using the a/l, a/w and a/h feature. It is sometimes necessary, however, to also include some other dimension that occurs elsewhere in the Element. Typically this might include a reference to one of the Variable parameters in the L, W, H, A, B, or C fields but it might just as easily be some other dimension or even a calculated value based on one or more component size.

This is done by entering the text code t after the formula followed by the field reference that contains the required dimension. The usual semi-colon ; separator is required after the formula and before the t.

The dimension is printed out in the item description by incorporating a/t in the description at the appropriate position.

ANY cell in the Element can be referenced by the Text variable using the usual calculix notation eg L, A, 1l, 4h, 19q and the function can also use values calculated in scratch pad lines by referencing the formula quantity in the usual way.

The field following the t may also be a formula and if a formula is used the whole of the formula must be enclosed in brackets. If the field value is a variable, brackets are not required.

Only ONE dimension can currently be used in each item but there is no restriction on the number of items that can use the function.

 

Back to Top
 

Formula memory buffer

In the same way that Item Codes can be buffered so can Formulae. If the cursor is in the formula field and <F11> is pressed, the formula is buffered. Pressing <F11> again on an empty field will reproduce the buffered formula.

 

Back to Top

 
 

 

Home Services

Send mail to with questions or comments about this web site.
Copyright E-Quantities Ltd.