Jump to content
Developer Wiki and Function Reference Links ×

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.

Link to comment

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?

Link to comment

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
Link to comment

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.

Link to comment

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.

Link to comment
  • 4 years later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...