Mercantile Plumbing calcs

[Codeman]

Cell B5 will contain the total Occupant load which is an input I have to
provide because it will vary from job to job.

Cell C9 is a value of 500 and will not change. It is the Plumbing Code’s
ratio of 1-WC plumbing fixture for every 500-people.

Cell C13 is the answer from B5 divided by C9 where the numerical value is
ROUNDEDUP to the next whole number but with some additional input as
addressed below. This cell will be the plumbing fixture count or
requirements.
Here is the catch for C13 (Note: the same formula will be used in C15);
I want C13 to provide several answers as follows:
1. When the total Occupant load in B5 is 50 or less C13 is to read “Uni-sexâ€
2. When the total Occupant load in B5 is 51 or more C13’s numerical answer
will be derived from (B5C9) x 50%. The answer would be the required number
of fixtures to be provided for each sex. If 51 people are the occupant load,
then by Code I will be required to have separate sex restrooms. I can divide
the total fixture count by half between the sexes once the occupant load is
51 or more.

I will have a cell for Men (C13) and Women (C15) with the same formulas. Do
not forget that the numerical answer if it contains decimals is to be ROUNDED
UP to the next whole number. (ex: 0.01 must be rounded up to 1)

Now to another Cell B18;
=IF((C13+C15)<6,"N/A","REQUIRED") is the current formula and it will have to
be modified to provide three answers as follows.
1. If the occupant load in B5 is 50 or under then B18 should read “ALLOWEDâ€
and
2. if the occupant load in B5 is 51 or more then B18 should read “N/A†and
(See NOTE)
3. if C13+C15 totals 6 or more then B18 should read “REQUIREDâ€. (See NOTE)
NOTE: there maybe a conflict of #1 or #2 with #3 because #3 is totaling
fixtures required for Men and Women and #1 or #2 is based on total occupant
loads. As the occupant load increases over 51 each sex restroom will have an
increase in fixtures base on half of the fixture count used for each sex.
Since the numerical value is rounded up then a total occupant load of 2001
will push the aggregate total of Men and Women fixtures to 6 and will invoke
the Uni-sex “REQUIREDâ€. A total occupant load of 2000 will not require
uni-sex.
I am using this cell to flag when another type of Uni-sex restroom is
required. If the total fixture count in Men (C13) + Women (C15) is 6 or more
then the Code requires a Uni-sex (Family room) in addition to the separate
sex restrooms.
I do not need the quotation marks to read. This is different from the
Assembly occupancies I receive help on earlier this week.

 

[Luke M]

C13 & C15's formulas:
=IF(B5<=50,"Uni-sex",ROUNDUP(0.5*B5/C9,0))

B18 formula:
=IF(B5<=50,"Allowed",IF(SUM(C13,C15)>=6,"Required","N/A"))

 

[Codeman]

Those all worked thanks.

However, two other Cells (B19 & D19) that are linked to results occurring in
C13 & 15 changed. I did not think it would be affected but it did.

=IF((C13)<6,"N/A","REQUIRED") is the formula in Cell B19
What changed is when C13 provides the answer “Uni-sex†B19 listed
“REQUIREDâ€. It should list “N/Aâ€. After testing some numbers in B5 it is
related to the 50 or less. When the occupant load is 50 or less in B5 then
B19 should also read “N/Aâ€. What is happening B19 reads Uni-sex in C13 which
is not a number and provides the incorrect answer.
Is there a way to get B19’s formula to read “Uni-sex†in C13 and provide the
“N/A†in addition to reading when 6 or more occur to read “REQUIRED�
This is the Ambulatory stall which is required when a total of 6 fixtures or
more occur in a separate sex restroom. The difference is Ambulatory is not
an aggregate of fixtures for the separate sex restrooms as was the Uni-sex.

 

[Luke M]

C19 formula:
=IF(SUM(C13)<6,"N/A","REQUIRED")

Since SUM treats texts as equal to zero, this still works. NOte that if you
want more control, could change this to:
=IF(OR(C13="Uni-sex",C13<6),"N/A","REQUIRED")

First formula is 2 calculation steps faster.

 

[MartinW]

It is the Plumbing Code’s
ratio of 1-WC plumbing fixture for every 500-people.

Wow, you would neeed security on the door and bookings by appointment only.

Cheers
Martin

 

[Codeman]

Thank you Luke M.

Martin W, the 1:500 is for Mercantile Occupancies. Other Occupancies have
much tighter ratios. Mercantile generally have large square footage from
wich the occupant load is based. EX: Ground floor occupant load is 1 person
per 60 sq ft of gross floor area. The Occupant load adds up and not everyone
in a store is going to the restroom at one time as they would in an Assembly
Occupancy during intermissions. Assembly Occupancy loads are base on net
used floor area of fixed seating.