Plumbing fixture calculations

[cellophane]

I have a basic spreadsheet setup with some help from folks here that works well but I would like to expand its functionality so I don't have to manually type in the values that change based on occupancy.

In the sheet I have the Occ. Classification (Mercantile, Office etc) and the calculations for area. These all work fine. Below that are the calculations for required fixtures which is where I'm not entirely sure how to proceed. The requirements change based on occupancy type - for example:

Mercantile
Occ. < 150 - 1 WC
Occ. < 300 - 2 WC

Office:
Occ. < 15 - 1 WC
Occ. < 35 - 1 WC

I would like to tie the required fixtures to the Occupancy Class in the top (green cell) so changing the class updates the values in the requirements.

The formula for the final calc is: =IF(C5<=201,1,IF(C5<=400,2,3+INT(C5/600))) I believe if I swap out the numeric values (in orange) for cell references it will work but I'm not 100% certain on that (or how the last part of the formula (3+INT) actually works ) Several occupancies would also end up needing more than 4 rows of numbers.

I'm enclosing the spreadsheet and a page from the code on fixture counts.

[rylo]

Hi

Just using your numbers in the formula, try the following.

Update the OccupancyLoading sheet with
C15: 201
D15: 400
E15: 600

Then the formula in F5 becomes
=IF(C5<=VLOOKUP(C1,OccupancyLoading!$A$1:$E$19,3,FALSE),1,IF(C5<=VLOOKUP(C1,OccupancyLoading!$A$1:$E$19,4,FALSE),2,3+INT(C5/VLOOKUP(C1,OccupancyLoading!$A$1:$E$19,5,FALSE))))

Hope that makes sense.

rylo

[protonLeah]

My take attached. Note, I used indirect(substitute) to access range names base on headers for the lookup.

[cellophane]

I might be in over my head... I'll do some reading on this indirect thing and see if I can sort it out. I think it's heading in the right direction but I think I'll end up needing a hundred different formulas.

rylo - why would I use those values in C15?

[protonLeah]

In sheet "TEST SHEET FOR FORUM" you have three tables:

Required WC per Code so I created the named range WaterCloset for A8:C11
Required Lav per Code in A14:C17, which I named Lavatory
Required Urinal per Code in A20:C23, named Urinal

To the right, you have three calculation area, for Male, Female and Lavatory

Two calculations are required for male: water closet and urinal. I wanted to use the value: "water closet" as the name of the range(A8:C11) but range names can't have spaces. Therefore I named the range "WaterCloset" without the space.

Then in the calculation formula I used SUBSTITUTE(E3," ",""), this simply replaces the space with a null so "water closet" becomes "watercloset" (the name of the range). Then the Indirect function passes that as a reference the named range WaterCloset used by VLookup. Note that Urinal and Lavatory have no spaces to squeeze out so they are passed as is.

The only reason I did it that way was because I thought the values/items in column E would make good range names to use instead of always using the cell addresses.

[cellophane]

This got pushed to the backburner and I haven't had a chance to look at it again but I wanted to at least say thanks for the help so far. One of these days I will get back to it