+ Reply to Thread
Results 1 to 5 of 5

Formula with increasing value ranges - plumbing code calculation

Hybrid View

  1. #1
    Registered User
    Join Date
    05-30-2012
    Location
    Louisville, KY
    MS-Off Ver
    Excel 2010
    Posts
    10

    Formula with increasing value ranges - plumbing code calculation

    I'm trying to put together a spreadsheet to do some plumbing calculations for us based on the Kentucky Building Code (which has stupid fixture calculations btw). The code states that for every X increase in occupancy we need to add an additional fixture and for every Y increase over a certain value we need an additional fixture (varies by occupancy classification.)

    A. Occupancy = 100 or less - 1 WC
    B. Occupancy = 200 or less - +1 WC (2 total)
    C. Occupancy = 400 or less - +1 WC (3 total)
    D. Occupancy > 400 - +1 WC for each additional 400 people

    I understand the formula in theory but have no clue how to actually write it...
    if A<100 then 1, if A < 200 but > 101 then 2, if A > 200 but < 400 then 3, if A >500 then 3+1 for each additional increment of 500.
    Last edited by cellophane; 07-25-2012 at 04:17 PM.

  2. #2
    Forum Contributor CheshireCat's Avatar
    Join Date
    10-11-2011
    Location
    Victoria, Canada
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    258

    Re: Formula with increasing value ranges - plumbing code calculation

    Try:

    =IF(A2<=100,1,IF(A2<=200,2,3+INT(A2/500)))
    What happens if there are 450 people?
    Last edited by CheshireCat; 07-25-2012 at 04:00 PM.

  3. #3
    Registered User
    Join Date
    05-30-2012
    Location
    Louisville, KY
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Formula with increasing value ranges - plumbing code calculation

    Quote Originally Posted by CheshireCat View Post
    Try:

    =IF(A2<=100,1,IF(A2<=200,2,3+INT(A2/500)))
    What happens if there are 450 people?
    Thanks.

    Typo on my end. Editing first post to correct

  4. #4
    Forum Contributor CheshireCat's Avatar
    Join Date
    10-11-2011
    Location
    Victoria, Canada
    MS-Off Ver
    Excel 2003 & 2010
    Posts
    258

    Re: Formula with increasing value ranges - plumbing code calculation

    In that case:

    =IF(A2<=100,1,IF(A2<=200,2,3+INT(A2/400)))

  5. #5
    Registered User
    Join Date
    05-30-2012
    Location
    Louisville, KY
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Formula with increasing value ranges - plumbing code calculation

    That does the trick. Thanks a million!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1