 |
|
|
                          
|
Formula/Functions
Formulae, functions and operatorsThe 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 entriesFormulae 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
Back to Top
Dimensions in FormulaeDimensions from the Element Data- "Internal" References in formulae
Dimensions from the Room Data - "External" References in formulae
Back to Top
Special formula functions
Back to Top
Mathematical Precedence in formulaeFormulae 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 FormulaeThe 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 FormulaeThe 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 ) expThe 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 FormulaeThe 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 FormulaeA 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 FormulaeIt 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 FormulaeIn 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 FormulaeIt 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 FormulaeAll 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 formulaeAside 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
Heights
Areas
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 FormulaeThe numerical constant pi will return the value 3.1416.
Back to Top
Special Functions in FormulaeTo 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:
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 FormulaeIt 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 RangesAn 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 RangesA 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 RangesAn 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 RangesA 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 FormulaeRanges 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 formulaeThe 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 FormulaeThe 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 FormulaeTo 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)
|
|
Send mail to
|
with questions or comments about this web site.