Calculated Value in Key Schedule

[jon111712]

I may be making this much harder than it needs to be, so I thought I'd check here first. I'm working on creating a Plumbing Fixture Schedule that will pull the Occupancy Load from a room, then tell me how many fixtures are required. Not a big deal typically. The IBC has relatively straight forward numbers for most uses.

Where I'm running into a problem, is that WA state has amended the IBC to a range of fixtures instead of a fixed number; ie. instead of 1 WC for every 25 people, it's 1 WC for 1-25 people, 2 WC for 26-50, etc.

Now, I have a formula worked out in excel that is a bunch of nested IF statements that works the way I want it to, but I don't know where to put this in Revit. Ideally, it would be a Calculated Value in the Plumbing Fixture Key Schedule, but they aren't allowed. I could do a TON of nested IF statements in a Plumbing Fixture Schedule to list through the different room uses, but that seem more difficult than it should be.

Does anyone have any experience with this, or a workaround for this issue?

This all makes sense in my head, so I hope I've described the problem. Let me know if I need to clarify at all.

[twiceroadsfool]

It really needs to be a CV of the room or area schedule, not the plumbing fixture schedule.

Reason being, the Plumbing fixture schedule isnt going to recognize the AREA of the room... And the OLF is most typically going to be generated by the Area, yes?

I dont believe youll get the Plumbing schedule to read any information from the Room and the Area objects...

[jon111712]

Oops, that's the part where I didn't describe things well. The Schedule that I'm using to calculate my required plumbing fixtures is a room schedule, not a plumbing schedule like I wrote. We are calling it a Plumbing Fixture schedule in the model. Sorry about that.

It does seem that it needs to be a CV in the Room Schedule, but I don't know how to tell Revit to only apply the formula in CV-1 to Occupany Type A, and the formula in CV-2 to Occupancy Type B.

[twiceroadsfool]

You can do it, im just not sure youll be able to use the key schedule to do it. I have a formula here somewhere that used two different OLF's dependant on whether or not the room was a *restaurant* or not... It changed the CV's formulas based on different Back of House sizes and calculations. But the *rest8 parameter was a simple Yes/no for the Area (or room), it wasnt in a key schedule.

You shouldnt need a key schedule for that...

[jon111712]

I'd be interested in seeing how you do that. The only reason I thought of a key schedule right away was because there are so many different types of Occupancy Types that I need to account for. A Yes/No seems to work well if there is one or two options, but when I have 12, it seems like it would take a pretty hefty IF statement to run through them all.

[twiceroadsfool]

LOL, i tried to open it to get the formulas for copy and paste... But its an old 9.1/2008 model, and my POS workstation wont upgrade it.

I seem to recall a yes/no parameter, and a fairly LONG if statement. But im willing to bet it could be simplified. I only needed the two cases (restaurant or not) so that was sufficient. BUT, you could run the If statement using the embedded OLF parameter, and have THAt filled in by the key schedule...

Im just thinking out loud, but i cant see why that wouldnt work off hand. Ill revisit in the morning...

[patricks]

As an aside, how would you get Revit to automatically calculate when IBC calls for something like "1 fixture per 40 for the first 40, then 1 per 80 for the number exceeding 40"?

[twiceroadsfool]

Patrick- An If statement with a few integer parameters can do it...

OCC- Integer
OCClessone - Integer = OCC-40

FIXTURE- integer = If(OCC<40, 1, (OCClessone/80)+1)

thats down and dirty though, and you would have to take a few more steps to make sure it rounds that value up, which it wouldnt do. For instance, if you had 44 people, it would take 4/80 + 1 and round it down to one. But theres all sorts of ways to get it to always raise to the next integer...

[patricks]

meh... I'll just do the math and type it into our code review sheet. :p

For the projects that we do, I think I'd end up spending more time worrying if my formulas are right and if Revit got the calculation right that it would just be easier for me to calculate the numbers myself.

[twiceroadsfool]

That works too, lol. I had a bunch of projects in a row that all contained about 80 spaces each, that needed their own egress calcs. Plus, the demising locations between all 80 spaces was in CONSTANT flux, from concept through construction. So it was easier to let it do the math on a day to day basis. Someone would move a wall 6 inches west per the updated lease plan, and youd never see it on the 1/32" plan...

But the formulas are pretty easy to write.... ;)

[jon111712]

They way I see it, I end up verifying all the numbers from the Revit calculations anyway, but if it can give me a head start as to the general ballpark that I need to be looking in, then I'm grateful.

Back in school, we had an professor that always asked if our calculations passed our "internal **** detector". My detector was always a bit faulty, so I use Revit alot to validate what I think may be close to the right answer.

[twiceroadsfool]

LOL, once you get it set up, you can have faith in it. You just need to be meticulous in setting up your process.

For instance, i use areas for these cals, instead of rooms. Rooms have too many quirks. Walls not in the physical space but constrained to the same level as the room are sunndely bounding, and diminish Square Footage numbers... Plus sometimes you need to calculate entire regions with multiple rooms, and i dont like unbounding those internal rooms, as then it generates errors when i want those rooms scheduled. It also flops getting SF's for those rooms later.

So i built the whole thing as an area plan. I never had an issue sending out those egress sheets with calcs without looking at them.... Although certain members on the team did, LOL.... But thats a different discussion. :)

[jon111712]

I definitely have faith in the calculations, but as I mentioned, I've got a faulty "internal **** detector" so when something doesn't make sense to me, I need to verify it. It's always because I'm wrong, not my numbers.

So, to kind of go back to my original question, I'm getting ready to go down this road of figuring out how to calculate these plumbing fixtures. I've got 13 different room occupancies to filter through, then run my plumbing fixture count against. Each formula plumbing fixture count formula looks like this:

IF(MaleOccupants<=25,1,(IF(MaleOccupants<=75,2,(IF(MaleOccupants<=125,3,(IF(MaleOccupants<=200,4,(IF(MaleOccupants<=300,5,(IF(MaleOccupants<=400,6,(((MaleOccupants-400)/150)+6))))))))))))

So to total IF statement would be IF(OccupancyA, Formula1, (IF(OccupancyB, Formula2)...etc...

Is my model going to hate me for running this long of an if statement? If so, what better option do I have? I'm leaving for a weeks vacation starting tomorrow, so I'd like to try to get this figured out before I leave if I can.

[twiceroadsfool]

LOL, it wont hate you. I have an IF statement from the model i mentioned above thats 4 lines long, on 8.5x11... LOL.

Word to the wise, type them out in notepad and Copy Paste them in to Revit. Cuz revit wont let you OUT of the if statement, until it works... Counting parenthesis gets ollllld...

[patricks]

Sure would be nice if parameter fields (and schedule fields for that matter) could be stretched vertically to show multiple lines.

[twiceroadsfool]

Would also be nice if things that were *broken* didnt automatically delete, lol. If you type in a formula, and it doesnt work, let you get out of the box and just put *invalid formula* for the value in the schedule.

This is a bone of contention i have with all things in Revit. if you edit a parameter so a family breaks, its gone. Id rather it turn red, but stay in its last configuration. Thats what they did in Digital Project.

So if i go in and edit a roof sketch, and its no longer valid, dont make me entirely abandon the sketch. let me hit finish, turn the roof red, tell me its a cluster****, and let me revisit it after i had a beer, LOL...

[jon111712]

Ok, so I think I may have found a cleaner solution to this mess...Since I can check a Text Field against an IF statement, I made a new project parameter. This parameter is called WA_Ammend_WC_Male_Formulas. Long, but it's an integer that allows me to assign a formula to each Occupancy Type. Then, in my Room Schedule where I'm calculating the Plumbing Fixtures, I'm running an IF statement against THAT field, checking the integer, then assigning it the proper formula.

In talking about Formulas, I check all my formulas in Excel first. In this case for instance, I have a cell that I name the same as my parameter that I'm calling from, ie MaleOccupants, then I can copy and paste my formulas straight from excel once I verify that they work. For me it's a bit more intuitive than using Notepad.

Anyways, just wanted to give everyone a status update. I think I'm close to getting this to work.

[jon111712]

I got it to work! I can post the formula if anyone is interested. Now I just need to go through the same exercise for the Women's WC, then the Lavs....

[greg.mcdowell]

Please do post. I'm curious... and thanks!

[jon111712]

Sorry I forgot about this thread, but I just re-found it while looking for something else. At any rate, here is one of the formulas that I wrote up for the plumbing calcs.

if(WA_Ammend_WC_Formula = 1, if((not(MaleOccupants > 25)), 1, if((not(MaleOccupants > 75)), 2, if((not(MaleOccupants > 125)), 3, if((not(MaleOccupants > 200)), 4, if((not(MaleOccupants > 300)), 5, if((not(MaleOccupants > 400)), 6, ((MaleOccupants - 400) / 200) + 6)))))), if(WA_Ammend_WC_Formula = 2, if((not(MaleOccupants > 100)), 1, if((not(MaleOccupants > 200)), 2, if((not(MaleOccupants > 400)), 3, (((MaleOccupants - 400) / 250) + 3)))), if(WA_Ammend_WC_Formula = 3, if((not(MaleOccupants > 100)), 1, if((not(MaleOccupants > 200)), 2, if((not(MaleOccupants > 400)), 3, (((MaleOccupants - 400) / 300) + 3)))), if(WA_Ammend_WC_Formula = 4, MaleOccupants / 150, if(WA_Ammend_WC_Formula = 5, if((not(MaleOccupants > 15)), 1, if((not(MaleOccupants > 35)), 2, if((not(MaleOccupants > 55)), 3, (((MaleOccupants - 55) / 50) + 3)))), if(WA_Ammend_WC_Formula = 6, 0, if(WA_Ammend_WC_Formula = 7, if((not(MaleOccupants > 10)), 1, if((not(MaleOccupants > 25)), 2, if((not(MaleOccupants > 50)), 3, if((not(MaleOccupants > 75)), 4, if((not(MaleOccupants > 100)), 5, ((MaleOccupants - 100) / 30) + 5))))), if(WA_Ammend_WC_Formula = 8, 0, if(WA_Ammend_WC_Formula = 9, if((not(MaleOccupants > 50)), 1, if((not(MaleOccupants > 100)), 2, if((not(MaleOccupants > 400)), 3, (((MaleOccupants - 400) / 300) + 3)))), if(WA_Ammend_WC_Formula = 10, 0, if(WA_Ammend_WC_Formula = 11, 0, if(WA_Ammend_WC_Formula = 12, if(not((MaleOccupants > 10)), MaleOccupants / 10, ((MaleOccupants - 10) / 25) + 1), if(WA_Ammend_WC_Formula = 13, MaleOccupants / 8, if(WA_Ammend_WC_Formula = 14, if((not(MaleOccupants > 10)), 1, if((not(MaleOccupants > 25)), 2, if((not(MaleOccupants > 50)), 3, if((not(MaleOccupants > 75)), 4, if((not(MaleOccupants > 100)), 5, ((MaleOccupants - 100) / 30) + 5))))), 0))))))))))))))

As an aside, I used the same method as was posted on the forums else where to correctly round up or round down to the nearest whole number. It basically puts this formula into a integer and a number, then compares the two.

For what it's worth, this was definitely written and verified in excel first, then copy and pasted into revit.

[cberteaux371801]

OMG.....my head is all a buzzzzzzz.....about to explode even......:shock:

I am no mathematician, Sadly, and trying to reverse engineer if formula is for the birds, maybe someone can help me. I am looking at Assembly A-1 1/25 male and 1/65 female WC. I got the formula to work for 500 Occ, sweet, but when change the Occ to 800 it doesn't calculate out right........what the heck am i missing.....

95297

[CADastrophe]

Hard to say without some more info. Can you list which values are wrong and what they should be? Also, did you mean to say "1/125 male", 'cause that's what it looks like your formula is doing.

[cberteaux371801]

Well, wc women should be 12 not 11. and yes a typo of 1/125 per IBC 2012 (sorry) im a bit flustered this morning with all of this......

here is the same image but with 500 Occ applied.... MWC:4 / WWC: 8 / LAV: 3 / DF: 1

95301

[CADastrophe]

No problem! Well just looking at "wcwomen", it looks like it's doing things correctly.

Occ = 800
Women = Occ/2 = 800/2 = 400
wcwomen = (Women+301)/65 = (701)/65 = 10.785 ---Integers round up---> 11

[cberteaux371801]

Formula = Yes working

Back checking with calculator not so much unless my mind is just past its peak for the day already...lol

800/65 = 12.30.......

so the (Women+301) I think is the issue, if i use a higher number then the rounding for the 500 Occ doesn't work....but does for 800

something in my logic is clearly amiss today................

thanks for the help by the way!!

[CADastrophe]

800/65 = 12.30.......

Where are you getting the 800? The original OCC value is getting halved and then 301 is added to it before it gets to the "/65" part of the formula, which leaves it at 701, not 800.

What is the reason for the "+301", anyway?


thanks for the help by the way!!

No problem - that's what I'm here for!

[cberteaux371801]

Ok... History...

I got these couple of unfinished families from another thread because they look very close to what our office uses as a standard with dummy text and line work (Bad) or inserted images from Excel (Also Bad). I am trying to reverse engineer all the Code stuff and have done a great job until I came across this little bugger. For the most part it all seems strait forward formula wise, I understand what is going on. the part I don't is as you pointed out, the +301. playing around with this number seems to force the calculated value higher or lower. I dunno tho.... Soooooo Here is the family to take a look at......

[CADastrophe]

What thread was that poached from - was that +301 explained at all there? The fact that it is a static value is going to create some potentially wonky numbers the farther the OCC gets away from 600, so there has got to be a reason for it being there.

[cberteaux371801]

I do not remember anything being explained in that thread, sadly :(

just a here you go / as is......

i can try to find it again, and when i do I'll link it here....

and I totally agree that the farther away you get the more Off the number gets. any ideas on better formulas?? I would just buy something, but I like the satisfaction of the learning process and knowing i had a part in the puzzle solving.......then returning the favor and helping others.

[CADastrophe]

Unfortunately, the answer does not lie in the formulas. It's gotta be driven by whatever Codes the creator based this family on. Now, if you want to alter this Family to adapt to your Codes, that's a different story, as the +301 may simply be removed.

[cberteaux371801]

Codes meaning IBC 2012 Table 2902.1....

and I'll def try just removing the +XXX numbers from the formulas. I'll post back if this solves this and re-post family to share with the world!!!

[cberteaux371801]

Definitely on the right track!!!!

how would i make Women / 500 or whatever force the formula return a value of 1 if the value equals 0???

this is what i have, revit likes the formula, but does not return a (1)

if(Women < 0, 1, Women / 500)

[CADastrophe]

Something like:

= if(Women / 750 < 1, 1, Women / 750)

[cberteaux371801]

Awesome, that makes perfect sense...almost done.....

now i guess the final question would be, how do you handle OCC like A-4, A-5, B where it states something like: 1 per 75 for the first 1,500 and 1 per 120 for the remainder exceeding 1,500???

[cberteaux371801]

OK fellow Reviteers,

So here is the end result I came up with, it uses the 2012 IBC for reference. Please note that these formulas for the most part can be used in schedules as well. We chose a family because it more closely resembles our office standards. Have fun with it, If you use it, make it your own....

Thanks Everyone for the Help!!!!

BIM the World

[cberteaux371801]

I have another question....

I have this formula that keeps on getting kicked back and revit is telling me the error seen in the screen shot, How do I return a value of checked Yes.

basically i want this formula to

A) if Occ Load Calc = 0, then make it 1 (EASY)
B) If Accessory Use is checked it will subtract that from the total of Calculated Round


Any help would be great!!!!!!95396

[Steve_Stafford]

The error message says that True isn't a parameter name. Yes/No parameters are not evaluated as true or false, it is simpler.

This is the syntax:


if(Accessory Use,if true result, if false result)

For example in a Parameter called Testing I can enter in the formula field: if(Accessory Use,4",8")
Checked (true) Testing will show 4"
Not Check (false) Testing will show 8"

[cberteaux371801]

Sooooo

I would need to say:

if(Accessory Use, -number, Occupant Load Calculated)

[Steve_Stafford]

... -number...

if -number is an actual parameter then yes.

[cberteaux371801]

Sweetness, Thanks Steve