# 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

Attached is the file. I need to have D4/F4 for each subrow to give the number of persons per space object but it gives a #value! maybe something is not set properly.

worksheet_database_row_calc.vwx

##### Link to comment

I think the problem is that the factor column is not taken as a number but perhaps text.

##### 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.

• 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

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.

##### Link to comment

Yes, it appears the IF function is wonky, but you can make it work.

One column  =VALUE(F4)=0

One column =IF(H4, '0', G4)

Then hide the columns you don't want.   Your edited original file attached.

worksheet_database_row_calc-PTS.vwx

##### Link to comment

Pat, thanks for taking the time to help with this! Much appreciated.

##### Link to comment
• 4 years later...

I used Pat's worksheet above, and played with it a bit, and voila!!! ## 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. Reply to this topic...

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

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

×
×

• KBASE
• #### MARIONETTE

×
• Create New...