Jump to content
Developer Wiki and Function Reference Links Read more... ×
David Poiron

Worksheet Database Row Calculations

Recommended Posts

I have a worksheet with database rows showing areas and occupant load factors from space objects. I would like to do a calculation of the occupant load based on the values of these two fields (area/occupant load factor). I understand that calculations in database fields may only be possible using scripting. Does anyone know how this might be accomplished? I've seen a file with a scripted calculation using one parameter, but not two, although I cannot seem to be able to make one parameter work.

Share this post


Link to post

What calculation do you want to do?  You should be able to do any normal calculation using a many field values as you want without needing to resort to scripting.

 

So if the database starts in Row 3 and Area is in Column A and the Load Factor is in Column B, then you should be able to put a formula into the Column C database header of:   =A3/B3 and get what you want.

 

If this does not work, can you post a stripped down version of the file so we can try it?

Share this post


Link to post

David,

 

You are correct. The User Fields in the PIOs are defined as Text type fields, not Numbers.

 

Change your formula to =D4/Value(F4) and you should get what you need.

 

The only thing to be careful of there is that only numbers or things that can be converted to numbers (i.e. 12" or 17mm) are stored in the User Field or the Value function will not be able to properly convert it to a number.

 

 

  • Like 1

Share this post


Link to post

Thank Pat. This did work. The next roadblock seems to be getting rid of a division by zero error. I tried using an IF statement to check the denominator for zero, and then use zero instead of the D4/value(F4)

 =IF((VALUE(F4)=0), '0', (D4/VALUE(F4)))

 but it behaves strangely or does not substitute a zero for the error. On a different version of the same worksheet I used the criteria to get rid of the objects with a zero factor but if I want to show this object as zero persons in the calculation I cannot get it to work.

Share this post


Link to post

the if is being very strange here. I enter the formula and it simplifies to something very different.

 

As a work around break it into two columns and hide one.

 

Set one column with a formula of =Value(F4)=0  (I am going to say this is in Column G.

 

Set the formula in Column F to =IF(G4,0,D4/Value(F4))

 

Set the Column width of G to zero and you should be good to go.

Share this post


Link to post

I assume that the formula in column H should be =IF(G4,0,D4/Value(F4)). Still does not work for me however. I think IF is broken. Does not work for me in 2017 either.

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

 

7150 Riverwood Drive, Columbia, Maryland 21046, USA   |   Contact Us:   410-290-5114

 

© 2018 Vectorworks, Inc. All Rights Reserved. Vectorworks, Inc. is part of the Nemetschek Group.

×