David Poiron Posted November 2, 2017 Share Posted November 2, 2017 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. Quote Link to comment
Pat Stanford Posted November 3, 2017 Share Posted November 3, 2017 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? Quote Link to comment
David Poiron Posted November 3, 2017 Author Share Posted November 3, 2017 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 Quote Link to comment
David Poiron Posted November 4, 2017 Author Share Posted November 4, 2017 I think the problem is that the factor column is not taken as a number but perhaps text. Quote Link to comment
Pat Stanford Posted November 4, 2017 Share Posted November 4, 2017 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 Quote Link to comment
David Poiron Posted November 6, 2017 Author Share Posted November 6, 2017 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. Quote Link to comment
Pat Stanford Posted November 6, 2017 Share Posted November 6, 2017 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. Quote Link to comment
David Poiron Posted November 8, 2017 Author Share Posted November 8, 2017 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. Quote Link to comment
Pat Stanford Posted November 8, 2017 Share Posted November 8, 2017 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 Quote Link to comment
David Poiron Posted November 8, 2017 Author Share Posted November 8, 2017 Pat, thanks for taking the time to help with this! Much appreciated. Quote Link to comment
Shortnort Posted April 22, 2022 Share Posted April 22, 2022 I used Pat's worksheet above, and played with it a bit, and voila!!! Quote Link to comment
Recommended Posts
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.