Plumbing and Piping Tools

PipeModule.code-snippets

/**Pipe ID (in.) based on Hazen-Williams formulation 
given Head Loss (ft. hd.), flow (GPM), Hazen Williams Roughness Coefficient (), 
and length (ft.) [100']*/
HazenWilliamsDiam = LAMBDA(Head, GPM, [RC], [Length],
    LET(RC_, IF(ISOMITTED(RC), 140, RC),
    L, IF(ISOMITTED(Length), 100, Length),
    (4.52 * (GPM/RC_)^1.852*L / (Head/2.31)) ^ (1/4.8704)
    )
);

/**Estimated Flow (gpm) based on Hazen-Williams formulation 
head-loss (ft.hd.), Hazen Williams Roughness Coefficient (), Pipe ID (in), 
and length (ft.) [100']*/
HazenWilliamsGPM = LAMBDA(Head, Diam, [RC], [Length],
    LET(RC_, IF(ISOMITTED(RC), 140, RC),
    L, IF(ISOMITTED(Length), 100, Length),
    RC_*((Head/2.31) * Diam^4.8704 / (4.52 * L)) ^ (1/1.852)
    )
);

/**Estimated headloss (ft. hd.) based on Hazen-Williams formulation 
given flow (GPM), Hazen Williams Roughness Coefficient (), Pipe ID (in), 
and length (ft.) [100']
https://en.wikipedia.org/wiki/Hazen%E2%80%93Williams_equation*/
HazenWilliamsHead = LAMBDA(GPM, Diam, [RC], [Length],
    LET(RC_, IF(ISOMITTED(RC), 140, RC),
    L, IF(ISOMITTED(Length), 100, Length),
    4.52 * (GPM/RC_)^1.852 / Diam^4.8704 * (2.31*L)
    )
);

/**Hazen Williams Roughness Coefficient by Material, ["Cu Type K","Cu Type L","Steel Sch 40","PEX"]
https://www.engineeringtoolbox.com/hazen-williams-coefficients-d_798.html*/
HazenWilliamsRC = LAMBDA(Material,
    LET(
        mats, {"Cu Type K","Cu Type L","Steel Sch 40","PEX"},
        RCs, {140,140,130,140},
        INDEX(RCs,MATCH(Material, mats, 0))
    )
);

/*Estimated domestic water demand (GPM) from IPC WSFU, FV = 1 - Flush Valves, 0 - Flush Tanks
Returns interpolated values, Source data from IPC Appendix E Table E103.3*/
DWSpeakFlow_IPC = LAMBDA(WSFU, FV,
    LET(
        FUs, {0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,25,30,35,40,45,50,60,70,80,90,
                100,120,140,160,180,200,225,250,275,300,400,500,750,
                1000,1250,1500,1750,2000,2500,3000,4000,5000},
        GPM_FT, {0,3,5,6.5,8,9.4,10.7,11.8,12.8,13.7,14.6,15.4,16,16.5,17,17.5,18,18.4,18.8,19.2,19.6,
                21.5,23.3,24.9,26.3,27.7,29.1,32,35,38,41,43.5,48,52.5,57,61,65,70,75,80,85,105,
                124,170,208,239,269,297,325,380,433,525,593},
        GPM_FV, {0,3,5,6.5,8,15,17.4,19.8,22.2,24.6,27,27.8,28.6,29.4,30.2,31,31.8,32.6,33.4,34.2,35,
                38,42,44,46,48,50,54,58,61.2,64.3,67.5,73,77,81,85.5,90,95.5,101,104.5,108,127,143,177,
                208,239,269,297,325,380,433,525,593},
        GPMs, IF(FV,GPM_FV,GPM_FT),
        INTERP1(WSFU,FUs,GPMs,1)
    )
);

/**Estimated Peak Flow (gpm) from total Water Supply Fixture Units (UPC), FV = 1 - Flush Valves, 0 - Flush Tanks*/
DWSpeakFlow_UPC = LAMBDA(WSFU, FV,
    IF((3000<WSFU)+(1>WSFU),"#OOB",
        LET(powers, {0.0,1.0,2.0,3.0,4.0,5.0,6.0},
            nfv_1, {1.73403E+00,8.63912E-01,-8.90669E-03,7.24690E-05,-3.50727E-07,8.93949E-10,-9.09681E-13},
            fv_1, {1.99939E+01,1.03184E+00,-1.19051E-02,1.01532E-04,-4.90447E-07,1.22172E-09,-1.21963E-12},
            nfv_2, {2.14668E+01,2.26539E-01,3.40072E-06,-8.96746E-08,6.33003E-11,-1.79898E-14,1.87446E-18},
            fv_2, {3.59563E+01,3.58996E-01,-4.39907E-04,4.11050E-07,-2.01037E-10,4.86877E-14,-4.61836E-18},

            cfs, IF(WSFU<=250, IF(FV, fv_1, nfv_1), IF(FV, fv_2, nfv_2)),
            
            SUMPRODUCT(WSFU^powers, cfs)
        )
    )
);

/**DB of pipe data, returns column of data from [NominalSize, Material, ID] */
pipeDB = LAMBDA(Parameter,
    LET(Param_, LOWER(Parameter),
        NominalSize_, TRANSPOSE({0.500,0.750,1.000,1.250,1.500,2.000,2.500,3.000,3.500,4.000,5.000,6.000,
                                8.000,10.000,12.000,0.500,0.750,1.000,1.250,1.500,2.000,2.500,3.000,3.500,
                                4.000,5.000,6.000,0.500,0.750,1.000,1.250,1.500,2.000,2.500,3.000,3.500,4.000, 
                                5.000,6.000,8.000,10.000,12.000,14.000,16.000,18.000,20.000,0.250,0.375,0.500,
                                0.625,0.750,1.000,1.250,1.500,2.000,2.500,3.000}),
        Material_, TRANSPOSE({"Cu Type K","Cu Type K","Cu Type K","Cu Type K","Cu Type K","Cu Type K","Cu Type K",
                                "Cu Type K","Cu Type K","Cu Type K","Cu Type K","Cu Type K","Cu Type K","Cu Type K",
                                "Cu Type K","Cu Type L","Cu Type L","Cu Type L","Cu Type L","Cu Type L","Cu Type L",
                                "Cu Type L","Cu Type L","Cu Type L","Cu Type L","Cu Type L","Cu Type L","Steel Sch 40",
                                "Steel Sch 40","Steel Sch 40","Steel Sch 40","Steel Sch 40","Steel Sch 40",
                                "Steel Sch 40","Steel Sch 40","Steel Sch 40","Steel Sch 40","Steel Sch 40",
                                "Steel Sch 40","Steel Sch 40","Steel Sch 40","Steel Sch 40","Steel Sch 40",
                                "Steel Sch 40","Steel Sch 40","Steel Sch 40","PEX","PEX","PEX","PEX","PEX",
                                "PEX","PEX","PEX","PEX","PEX","PEX"}),
        ID_, TRANSPOSE({0.527,0.745,0.995,1.245,1.481,1.959,2.435,2.907,3.385,3.855,4.805,5.741,7.583,
                        9.449,11.315,0.545,0.785,1.025,1.265,1.505,1.985,2.465,2.945,3.425,3.897,4.875,
                        5.845,0.622,0.824,1.049,1.38,1.61,2.067,2.469,3.068,3.548,4.026,5.047,6.065,7.981,
                        10.02,11.938,13.124,15,16.876,18.812,0.241,0.35,0.475,0.574,0.671,0.862,1.054,1.244,
                        1.629,2.011,2.398}),

        IF(Param_="nominalsize", NominalSize_,
            IF(Param_="material",Material_,
                IF(Param_="id", ID_,
                    "Invalid Parameter, provide NominalSize, Material, or ID")
            )
        )
    )
);


/**Pipe ID from Nominal Size and Material*/
pipeID = LAMBDA(NominalSize, Material,
    LET(
        mask, (pipeDB("material") = Material)*(pipeDB("nominalsize") = NominalSize)*1,
        FILTER(pipeDB("id"), mask)
        )
    );

/**Flow Velocity (ft/s) from GPM and pipe ID (inches)*/
PipeVelocity_Velocity = LAMBDA(GPM, ID,
    LET(
        Acs, ID^2*PI()/4/144, // pipe cross sectional area in ft2
        cr, 448.83, // ft3/s -> gpm
        vel, GPM/cr/Acs,
        vel
    )
);
/**Pipe ID (inches) from GPM and Flow Velocity (ft/s)*/
PipeVelocity_Size = LAMBDA(GPM, FlowVelocity,
    LET(
        minA, (GPM/448.83)/FlowVelocity*144,
        minD, (minA*4/PI())^0.5,
        minD
    )
);

/**GPM from pipe ID (inches) and Flow Velocity (ft/s)*/
PipeVelocity_GPM = LAMBDA(ID,FlowVelocity,
    LET(
        Acs, ID^2*PI()/4/144, // pipe cross sectional area in ft2
        cr, 448.83, // ft3/s -> gpm
        gpm, FlowVelocity*Acs*cr,
        gpm
    )
);

/**Nominal Pipe Size (round up) from material and ID (in.)*/
NominalPipeSize = LAMBDA(MATERIAL, ID,
    LET(
        MATS, pipeDB("MATERIAL"),
        MASK, MATS=MATERIAL,
        IDS, FILTER(pipeDB("ID"),MASK),
        NDS, FILTER(pipeDB("NominalSize"),MASK),
        XLOOKUP(ID,IDS,NDS,"#OOR",1,2)
    )
);