Worksheet functions take an argument, perform an action, and return a value or values. There are two basic types of functions: those that use the value(s) you enter, and those that use information from objects in the drawing. The arguments required by the two function types are different.
● Number or text arguments: Functions that begin with a lower case letter typically require a number value or a cell address as the argument. For example, the acos function returns the arccosine of the value that is specified in the function argument. The argument you enter can be a mathematical expression (such as 3/5), an address of a cell that contains a number (such as A12), or an actual number. The argument for all trigonometry functions must be in radians.
● Criteria arguments: Functions that begin with a capital letter must be applied to one or more specific objects in the drawing. In a cell in a database header row, a function is automatically applied to the object listed in each sub-row, so no criteria argument is required.
However, in a spreadsheet cell, you must enter criteria to select the objects the function applies to. For example, the Area function returns the combined area of all 2D objects that meet the criteria. To specify which objects to obtain the area of, either use the Insert > Criteria command on the Worksheet menu, or enter the criteria manually. For details about how to specify criteria such as the object type, class, or visibility, see the developer oriented documentation here:
http://developer.vectorworks.net/index.php/VS:Search_Criteria#Search_Criteria_Tables
http://developer.vectorworks.net/index.php/VS:Function_Reference_Appendix#attrCrit
The following table lists all of the worksheet functions available, as well as what kind of argument the function takes.
You may want to display an attribute associated with a drawing object in the worksheet (such as the object’s class, or which layer it is on); see Retrieving Object Attributes in a Worksheet.
Function (argument) |
Description |
Example |
Related Functions |
|
acos(number) |
The arccosine of a number. The arccosine is the angle whose cosine is number. The returned angle is given in radians in the range 0 to pi. Number is the cosine of the angle, and must be from -1 to 1. |
=acos(3/5) (returns the angle for which the cosine value is 3/5) |
cos |
|
Angle(criteria) |
The angle (measured from horizontal) of the objects that meet the specified criteria, in degrees. Use this function to return the angles of lines and walls (measured from horizontal), the span angles of arcs, and the slope angles of slabs. |
Database header cell: =Angle (returns the angle of each object in the database) Spreadsheet cell: =Angle((t=arc)&(n='arc-1')) (returns the sweep angle of the arc object named “arc-1” in the drawing) |
|
|
Area(criteria) |
The total area of 2D objects that meet the specified criteria, based on the Area units in the Units dialog box |
Database header cell: =Area (returns the area of each object in the database) Spreadsheet cell: =Area(t=rect) (returns the combined area of all rectangle objects in the drawing) |
Perim |
|
asin(number) |
The arcsine of a number. The arcsine is the angle whose sine is number. The returned angle is given in radians in the range -pi/2 to pi/2. To express the arcsine in degrees, use the rad2deg function (or multiply the result by 180/pi). Number is the sine of the angle and must be from -1 to 1. |
=asin(A3) (returns the angle for which the sine value is given in cell A3) |
sin |
|
atan(number) |
The arctangent of a number. The arctangent is the angle whose tangent is number. The returned angle is given in radians in the range -pi/2 to pi/2. To express the arctangent in degrees, use the rad2deg function (or multiply the result by 180/pi). Number is the tangent of the angle in question. |
=atan(4/3) (returns the angle for which the tangent value is 4/3) |
tan |
|
average(number1, number2...) |
The average (mean) of the arguments |
=average(85,70,95) (returns the average of the three numbers) |
max, min, sum |
|
BotBound(criteria) |
The bottom 2D boundary (minimum y coordinate) of the objects that meet the specified criteria |
Database header cell: =BotBound (returns the bottom 2D boundary of each object in the database) Spreadsheet cell: =BotBound(t=locus) (returns the bottom 2D boundary of the locus that has the lowest bottom 2D boundary value in the drawing) |
LeftBound, RightBound, TopBound |
|
ComponentArea |
The area of one side of the specified wall or slab component, minus any holes. Index is the 1-based index identifying the component. |
Database header cell: =ComponentArea(2) (returns the area of the second component for each wall or slab object in the database) Spreadsheet cell: =ComponentArea(t=wall,1) (returns the combined area of the first components for all walls in the drawing) |
ComponentVolume, ComponentName |
|
ComponentName |
The name of the specified wall or slab component. Index is the 1-based index identifying the component. |
Database header cell: =ComponentName(2) (returns the name of the second component for each wall or slab object in the database) Spreadsheet cell: =ComponentName(t=wall,1) (returns the name of the first component for all walls in the drawing) |
ComponentVolume, ComponentArea |
|
ComponentVolume |
The volume of the specified component, minus any holes. Index is the 1-based index identifying the component. |
Database header cell: =ComponentVolume(2) (returns the volume of the second component for each wall or slab object in the database) Spreadsheet cell: =ComponentVolume(t=wall,1) (returns the combined volume of the first components for all walls in the drawing) |
ComponentArea, ComponentName |
|
concat(text1, text2, text3) |
Joins several text strings into one text string |
=concat(B3,', ',B4) (returns the contents of cells B3 and B4 as a single string, separated by a comma and a space) |
|
|
cos(number) |
The cosine of a given angle. Number is the angle in radians for which the cosine is calculated. |
=cos(deg2rad(23)) (converts a 23-degree angle to its radian equivalent, and returns the cosine of the angle) |
acos |
|
Count(criteria) |
The number of objects that meet the specified criteria |
Database header cell: =Count (returns the total number of objects for each row in the database) Spreadsheet cell: =Count(s='simple sofa') (returns the total number of symbol objects named “simple sofa” in the drawing) |
|
|
CurtWallFrameLength(criteria, class) |
The combined length of the curtain wall frames that meet the specified criteria and are in the specified class. To find all frames in a curtain wall, use an empty class name. |
Database header cell: =CurtWallFrameLength('') (returns the combined length of the curtain wall frames for each curtain wall in the database) Spreadsheet cell: =CurtWallFrameLength(t=wall, '') (returns the combined length of the curtain wall frames for all curtain walls in the drawing) |
CurtWallPnlAreaNet, CurtWallPnlAreaGross |
|
CurtWallPnlAreaGross(criteria, class) |
The combined gross area of the curtain wall panels in the walls that meet the specified criteria and are in the specified class. The gross area includes portions of the panel covered by frames. To find all panels in a curtain wall, use an empty class name. |
Database header cell: =CurtWallPnlAreaGross('') (returns the combined gross area of the curtain wall panels for each curtain wall in the database) Spreadsheet cell: =CurtWallPnlAreaGross(t=wall, '') (returns the combined gross area of the curtain wall panels for all curtain walls in the drawing) |
CurtWallFrameLength, CurtWallPnlAreaNet |
|
CurtWallPnlAreaNet (criteria, class) |
The net area of the curtain wall panels in the walls that meet the specified criteria and are in the specified class. The net area includes only the visible area bounded by frames. To find all panels in a curtain wall, use an empty class name. |
Database header cell: =CurtWallPnlAreaNet ('Class-1') (returns the combined net area of the curtain wall panels assigned to the class “Class-1” for each curtain wall in the database) Spreadsheet cell: =CurtWallPnlAreaNet(t=wall, 'Class-1') (returns the combined net area of the curtain wall panels assigned to the class “Class-1” for all curtain walls in the drawing) |
CurtWallFrameLength, CurtWallPnlAreaGross |
|
deg2rad(number) |
Converts a number from degrees to radians. Number is the value in degrees to be converted to radians. |
=deg2rad(47) (converts the 47-degree angle measurement to its radian equivalent) |
|
|
exp(number) |
e raised to the power of number. The constant e equals 2.71828182845904, the base of the natural logarithm. Number is the exponent applied to the base e. |
=exp(2) (returns the numeric value of e raised to the power of 2) |
ln |
|
GetIfcProperty (Vectorworks Architect or Landmark required) |
The value of a specific IFC property associated with an IFC object. The criteria is a string with two elements separated by a period. The first element is either an IFC entity or PSet name, and the second element is the name of the IFC property. |
=GETIFCPROPERTY ('ifcfurnishingelement.name') (returns the Name value for IFC objects whose IFC entity is IfcFurnishingElement) |
|
|
Height(criteria) |
The combined delta y (height) of objects that meet the specified criteria |
Database header cell: =Height (returns the height (delta y) for each object in the database) Spreadsheet cell: =Height(sel=true) (returns the combined height (delta y) value of the selected objects in the drawing) |
Width |
|
if ((logical_test), value_if_true, value_if_false) |
Use value_if_true if logical_test is true, value_is_false if logical_test is false. Use this function to conduct conditional tests on values and formulas and to branch based on the results of that test. The outcome of the test determines the value returned by the If function. The logical_test can be any value or expression that can be evaluated to true or false. Up to seven If statements can be nested as value_if_true, value_if_false arguments. Boolean statements within an if statement must be in parentheses. Text within an if statement should be enclosed within quotation marks. |
=if((C7>100),100,C7) when commas are used as decimal separators by the operating system, use semicolons instead: =if((C7>100);100;C7) (if the value in cell C7 is greater than 100, the value in this cell is 100; otherwise, the value in this cell is the same as the value in cell C7) |
|
|
Image(criteria) (Vectorworks Design Series required) |
The image associated with the object that meets the specified criteria. In the cell format, specify whether to show a thumbnail of the object, or the 2D attributes applied to the object. |
Database header cell: =Image (returns the image for each object in the database) Spreadsheet cell: =Image(s='cabinet') (returns the image of the symbol named “Cabinet”) |
|
|
int(number) |
Removes any fractional part of a number. Number is the real number to be changed to an integer. |
=int(B9) (returns the value in cell B9 without its fractional component) |
round |
|
IsFlipped(criteria) |
The flipped state of the objects that meet the specified criteria |
Database header cell: =IsFlipped (returns the flip state for each object in the database) Spreadsheet cell: =IsFlipped(PON=window) (returns the total number of window objects in the drawing that are flipped) |
|
|
LeftBound(criteria) |
The left side 2D boundary (minimum x coordinate) of the objects that meet the specified criteria |
Database header cell: =LeftBound (returns the left 2D boundary for each object in the database) Spreadsheet cell: =LeftBound(t=locus) (returns the left 2D boundary of the leftmost locus in the drawing) |
BotBound, TopBound, RightBound |
|
Length(criteria) |
The length of lines, walls, or path-based objects that meet the specified criteria |
Database header cell: =Length (returns the length for each object in the database) Spreadsheet cell: =Length(t=line) (returns the total length of all line objects in the drawing) |
|
|
ln(number) |
The natural logarithm (base e). Number is the positive real number for which the logarithm is calculated. |
=ln(12) (returns the natural logarithm of 12) |
exp |
|
log(number) |
The base 10 logarithm. Number is the positive real number for which the logarithm is calculated. |
=log(2) (returns the base 10 logarithm of 2) |
ln |
|
max(number1, number2,...) |
The largest number in the list of arguments. Number is 1 – 14 numbers for which the maximum value is to be found. |
=max(C5,C7,C9) (returns the largest of the numbers that are in cells C5, C7, and C9) |
min |
|
min(number1, number2,...) |
The smallest number in the list of arguments. Number is 1 – 14 numbers for which the minimum value is to be found. |
=min(C5,C7,C9) (returns the smallest of the numbers that are in cells C5, C7, and C9) |
max |
|
ObjectType(criteria) |
The numeric object type ID of objects that meet the specified criteria For a list of object type IDs, see the developer oriented documentation here: http://developer.vectorworks.net/index.php/VS:Function_Reference_Appendix#objects |
Database header cell: =ObjectType (returns the object type value for each object in the database) Spreadsheet cell: =ObjectType(sel=true) (returns the object type value of the selected object; for example, the object type value for a light is 81) |
|
|
Perim(criteria) |
The combined perimeter of objects that meet the specified criteria |
Database header cell: =Perim (returns the perimeter for each object in the database) Spreadsheet cell: =Perim(sel=true) (returns the total perimeter of all selected objects) |
|
|
rad2deg(number) |
Converts a number from radians to degrees. Number is the value in radians to be converted to degrees. |
=rad2deg(0.5235987) (converts the radian angle measurement to its degree equivalent) |
|
|
RightBound(criteria) |
The right side 2D boundary (maximum x coordinate) of the objects that meet the specified criteria |
Database header cell: =RightBound (returns the right 2D boundary for each object in the database) Spreadsheet cell: =RightBound(t=rect) (returns the right 2D boundary of the rightmost rectangle in the drawing) |
BotBound, TopBound, LeftBound |
|
RoofArea_Heated |
The heated area of the roof (minus the eve overhang) along the slope, combined for all objects that meet the specified criteria |
Database header cell: =RoofArea_Heated (returns the heated area for each roof and roof face object in the database) Spreadsheet cell: =RoofArea_Heated (st=roofface) (returns the combined heated area of all roof face objects in the drawing) |
RoofArea_HeatedProj |
|
RoofArea_HeatedProj |
The heated area of the roof (minus the eve overhang) projected to the layer plane, combined for all objects that meet the specified criteria |
Database header cell: =RoofArea_HeatedProj (returns the heated area for each roof and roof face object in the database, as projected to the layer plane) Spreadsheet cell: =RoofArea_Heatedproj(t=roof) (returns the combined heated area of all roof objects in the drawing, as projected to the layer plane) |
RoofArea_Heated |
|
RoofArea_Total |
The total area of the roof along the slope |
Database header cell: =RoofArea_Total (returns the total area for each roof and roof face object in the database) Spreadsheet cell: =RoofArea_Total(st=roofface) (returns the combined total area of all roof face objects in the drawing) |
RoofArea_TotalProj |
|
RoofArea_TotalProj |
The total area of the roof, projected to the layer plane |
Database header cell: =RoofArea_TotalProj (returns the total area for each roof and roof face object in the database, as projected to the layer plane) Spreadsheet cell: =RoofArea_Totalproj(t=roof) (returns the combined total area of all roof objects in the drawing, as projected to the layer plane) |
RoofArea_Total |
|
round(number) |
Rounds the specified number to the nearest whole number |
=round(D11) (returns the value in cell D11 rounded to the nearest whole number) |
int |
|
sin(number) |
The sine of a given angle. Number is the angle in radians for which the sine is calculated. |
=sin(deg2rad(32)) (converts a 32-degree angle to its radian equivalent, and returns the sine of the angle) |
asin |
|
SlabStyleName (Vectorworks Architect required) |
The name of a slab style |
Database header cell: =SlabStyleName (returns the name of the slab style for each slab object in the database) |
|
|
SlabThickness (Vectorworks Architect required) |
The combined thickness of slab objects (floors and roof faces) that meet the specified criteria |
Database header cell: =SlabThickness (returns the thickness for each object in the database) Spreadsheet cell: =SlabThickness(PON=slab) (returns the combined thickness of all slab objects in the drawing) |
|
|
sqrt(number) |
A positive square root. Number is the number for which the square root is calculated. |
=sqrt(D27) (returns the square root of the number in cell D27) |
|
|
Substring(text/function, delimiter, index) |
Splits a single string into an array of strings using a delimiter, and outputs each string at the specified index |
=SUBSTRING(('kitchen;bedroom;bathroom;basement', ';', 2) (returns “bedroom,” which is the second substring in the specified string) |
|
|
sum(number1, number2,...) |
The sum of all numbers in the list of arguments. Number is 1 – 14 numbers for which the sum is calculated. |
=sum(A2,A10..A12) (returns the sum of the numbers contained in cells A2, A10, A11, and A12) |
Average |
|
SurfaceArea(criteria) |
The total surface area of all objects that meet the criteria, based on the Area units in the Units dialog box |
Database header cell: =SurfaceArea (returns the surface area for each object in the database) Spreadsheet cell: =SurfaceArea(st=sphere) (returns the total surface area of all sphere objects in the drawing) |
|
|
tan(number) |
The tangent of the given angle. Number is the angle in radians for which the tangent is calculated. |
=tan(deg2rad(32)) (converts a 32-degree angle to its radian equivalent, and returns the tangent of the angle) |
atan |
|
TopBound(criteria) |
The top 2D boundary (maximum y coordinate) of the objects that meet the specified criteria |
Database header cell: =TopBound (returns the top 2D boundary for each object in the database) Spreadsheet cell: =TopBound(sel=true) (returns the top 2D boundary of the topmost selected object) |
BotBound, LeftBound, RightBound |
|
value(text) |
Converts a text string that represents a number to a number |
=value('2e3') (returns the numeric value of 2 times 10 raised to the power of 3) |
|
|
Volume(criteria) |
The total volume of all objects that meet the criteria, based on the Volume units in the Units dialog box. |
Database header cell: =Volume (returns the volume for each object in the database) Spreadsheet cell: =Volume(t=xtrd) (returns the total volume of all extrude objects in the drawing) |
|
|
WallArea_Gross |
The combined 2D gross surface area of one wall face for walls that meet the specified criteria |
Database header cell: =WallArea_Gross (returns the gross surface area of one wall face for each wall object in the database) Spreadsheet cell: =WallArea_Gross(t=wall) (returns the combined gross surface area of one wall face for all walls in the drawing) |
WallArea_Net |
|
WallArea_Net |
The combined 2D net surface area of one wall face, without door, window, and inserted symbol areas, for walls that meet the specified criteria |
Database header cell: =WallArea_Net (returns the net surface area of one wall face, minus inserted object areas, for each object in the database) Spreadsheet cell: =WallArea_Net(t=wall) (returns the combined net surface area of one wall face, minus inserted object areas, for all walls in the drawing) |
WallArea_Gross |
|
WallAverageHeight |
The average height of a wall, including wall peaks and different starting and ending heights |
Database header cell: =WallAverageHeight (returns the average height for each wall object in the database) Spreadsheet cell: =WallAverageHeight((t=wall)& (returns the average height of all walls that are selected in the drawing) |
|
|
WallStyleName (Vectorworks Architect or Landmark required) |
The name of a wall style |
Database header cell: =WallStyleName (returns the name of the wall style for each wall object in the database) |
|
|
WallThickness |
The combined thickness of wall objects that meet the specified criteria |
Database header cell: =WallThickness (returns the thickness for each wall object in the database) Spreadsheet cell: =WallThickness(t=wall) (returns the combined thickness of all walls in the drawing) |
|
|
Width(criteria) |
The combined delta x (width) of objects that meet the specified criteria |
Database header cell: =Width (returns the width (delta x) for each object in the database) Spreadsheet cell: =Width(sel=true) (returns the combined width (delta x value) of the selected object) |
Height |
|
XCenter(criteria) |
The x coordinate of the center point of an object that meets the specified criteria |
Database header cell: =XCenter (returns the x coordinate value of the center of the 2D boundary for each object in the database) Spreadsheet cell: =XCenter(sel=true) (returns the x coordinate value of the center of the 2D boundary of the selected object) |
YCenter, ZCenter, XCoordinate |
|
XCoordinate(critiera) |
The x coordinate of the insertion point for symbols, point plug-in objects, and loci. The returned value is relative to the user origin. |
Database header cell: =XCoordinate (returns the x coordinate value for each object in the database) |
YCoordinate, ZCoordinate, XCenter |
|
YCenter(criteria) |
The y coordinate of the center point of an object that meets the specified criteria |
Database header cell: =YCenter (returns the y coordinate value of the center of the 2D boundary for each object in the database) Spreadsheet cell: =YCenter(sel=true) (returns the y coordinate value of the center of the 2D boundary of the selected object) |
XCenter, ZCenter, YCoordinate |
|
YCoordinate(criteria) |
The y coordinate of the insertion point for symbols, point plug-in objects, and loci. The returned value is relative to the user origin. |
Database header cell: =YCoordinate (returns the y coordinate value for each object in the database) |
XCoordinate, ZCoordinate, YCenter |
|
ZCenter(criteria) |
The z coordinate of the center point of an object that meets the specified criteria |
Database header cell: =ZCenter (returns the z coordinate value of the center of the 3D boundary for each object in the database) Spreadsheet cell: =ZCenter(sel=true) (returns the z coordinate value of the center of the 3D boundary of the selected object) |
XCenter, YCenter, ZCoordinate |
|
ZCoordinate(criteria) |
The z coordinate of the insertion point for symbols, point plug-in objects, and loci. The returned value is relative to the user origin. |
Database header cell: =ZCoordinate (returns the z coordinate value for each object in the database) |
XCoordinate, YCoordinate, ZCenter |
~~~~~~~~~~~~~~~~~~~~~~~~~