Excel Automation

gistfile1.txt

// --- Workbook module ---
// A file of name definitions of the form:
//    name = definition;

// --- Workbook module ---

// Rows => ;  Columns => ,
// arr, {a, b, c;
//       d, e, f}
// Access row 1 => index(arr, , 1)
// Access row 1, column 2 => index(arr, 2, 1)

// ----- Temprature compensation, square releationship
tc_data_sq = {
    32, 56830, 13233337;
    40, 50859, 13472181;
    50, 44833, 13773488;
    60, 39218, 14110402;
    70, 33383, 14518841;
    80, 28774, 14887567;
    90, 24752, 15249551;
    100, 19265, 15798256;
    120, 15127, 16294825;
    140, 3620, 17905815;
    160, -2174, 18832860
};

// Calculate Temperature Compensation Factor
k_of_temps = LAMBDA(temp_f,
    LET(
        idx, SUM(IF(INDEX(tc_data_sq, , 1) <= temp_f, 1, 0)),
        m_l, INDEX(tc_data_sq, idx, 2),
        b_l, INDEX(tc_data_sq, idx, 3),
        m_l * temp_f + b_l
    )
);

// Compensted
dtof_cmpt = LAMBDA(temp, dtof, //
    IF(
        OR(ISBLANK(temp), ISBLANK(dtof)), // when the data set is blank
        "",
        INT((k_of_temps(temp) * dtof) / (2 ^ 24))
    )
);

// ===== Anomalie Rejection Filter ======
dtof_filter = LAMBDA(data,
    IFS(
        ROWS(data) <> 8,
        "ERROR: Wrong data size, 8 required",
        OR(ISBLANK(data)),
        "",
        TRUE,
        LET(
            avg_l, AVERAGE(data),
            vari_l, SUM(MAP(data, LAMBDA(a, (a - avg_l) ^ 2)) / 1.25),
            this_l, INDEX(data, 5),
            result, IF(
                (this_l - avg_l) ^ 2 < vari_l,
                this_l,
                (INDEX(data, 4) + INDEX(data, 6)) / 2
            ),
            zero_flow, IF(avg_l < 1000, 0, INT(result)),
            res, IF(OR(ISBLANK(data)), "", zero_flow),
            res
        )
    )
);

// ----- Slope of -----
slope_flt = LAMBDA(dtofs,
    LET(
        avg, AVERAGE(dtofs),
        bef, AVERAGE(INDEX(dtofs, {1, 2, 3, 4, 5}, )),
        aft, AVERAGE(INDEX(dtofs, {7, 8, 9, 10, 11})),
        IF(ABS(bef - aft) > 0.15 * avg, INDEX(dtofs, 6), avg)
    )
);

// ---
slope_of = LAMBDA(dtof, meter_id,
    LET(
        slopes, meter_calibration(meter_id),
        dtofx, INDEX(slopes, , 1),
        idx, COUNT(IF(dtofx < dtof, 1, "")),
        rres, IF(idx <= 0, 1, idx),
        INDEX(slopes, rres, )
    )
);

// --------------------------------------------------------------
to_vfr = LAMBDA(dtof, meter_id,
    IF(OR(ISBLANK(dtof)),
       "",
        LET(
            dtoff, slope_flt(dtof),
            xmb, slope_of(dtoff, meter_id), //
            m_l, INDEX(xmb, , 2),
            b_l, INDEX(xmb, , 3),
            dtoff * m_l + b_l
        )
    )
);

// ----- Reynolds Number ---------------------------------------- //
kinematic_v_data = {
    32, -0.035758, 3.0737;
    34, -0.032173, 2.9518;
    39, -0.030235, 2.8762;
    40, -0.02605, 2.7088;
    50, -0.01988, 2.4003;
    60, -0.01572, 2.1507;
    70, -0.01253, 1.9274;
    80, -0.01016, 1.7378;
    90, -0.00842, 1.5812;
    100, -0.0071, 1.4492;
    110, -0.00607, 1.3359;
    120, -0.00524, 1.2363;
    130, -0.00449, 1.1388;
    140, -0.00396, 1.0646;
    150, -0.00339, 0.9791;
    160, -0.00293, 0.9055;
    170, -0.00254, 0.8392;
    180, -0.00224, 0.7852
};

kinematic_v = LAMBDA(temp_f,
    // Calculate Temperature Compensation Factor
    LET(
        idx, SUM(IF(INDEX(kinematic_v_data, , 1) <= temp_f, 1, 0)),
        m_l, INDEX(kinematic_v_data, idx, 2),
        b_l, INDEX(kinematic_v_data, idx, 3),
        m_l * temp_f + b_l
    )
);

speed_of_sound_data = {
    32, 8.625, 4327;
    40, 7.6, 4368;
    50, 6.6, 4418;
    60, 5.7, 4472;
    70, 4.8, 4535;
    80, 4.1, 4591;
    90, 3.5, 4645;
    100, 2.7, 4725;
    120, 2.1, 4797;
    140, 0.5, 5021;
    160, -0.3, 5149
};

speed_of_sound = LAMBDA(temp_f,
    LET(
        idx, SUM(IF(INDEX(speed_of_sound_data, , 1) <= temp_f, 1, 0)),
        m_l, INDEX(speed_of_sound_data, idx, 2),
        b_l, INDEX(speed_of_sound_data, idx, 3),
        m_l * temp_f + b_l
    )
);

flow_velocity = LAMBDA(temp_f, DTOF,
    LET(sof, speed_of_sound(temp_f), (3 * sof ^ 2 * DTOF) / 1460000000000)
);

flow_diameter_full = LAMBDA(DTOF, vfr, temp_f,
    LET(
        f_v, flow_velocity(temp_f, DTOF),
        vfr_cfps, vfr * 0.0022280104075936,
        a_l, vfr_cfps / f_v,
        SQRT(4 * a_l / PI()) * 12
    )
);

flow_diameter = LAMBDA(DTOF, vfr, temp_f,
    LET(V_l, flow_velocity(temp_f, DTOF), 0.639138237996442 * SQRT(VFR / V_l))
);

m_flow_dia = 0.635771251;

// Flow (GPM) to speed (speed/sec)
// gpm_to_ft_sec(<flow in GPM>, <Internal Daimeter in Inches>)
gpm_to_ft_sec = LAMBDA(q_gpm, id_in,
    LET(
        cu_ft_sec, q_gpm * 0.00222800925925925, //  cu ft sec
        c_a, (PI() * (id_in / 12) ^ 2) / 4,
        cu_ft_sec / c_a
    )
);

// Reynolds Number
// reynolds_n(<flow (GPM)>, <temperature (F)>, <pipe ID (in)>)
reynolds_n_full = LAMBDA(q_gpm, temp_f, id_in,
    LET(
        k_v, kinematic_v(temp_f),
        speed, gpm_to_ft_sec(q_gpm, id_in),
        temp, (speed * (id_in / 12)) / (k_v * 0.00001),
        k_v
    )
);

// Reynolds Number
// reynolds_n(<flow (GPM)>, <temperature (F)>, <pipe ID (in)>)
reynolds_n = LAMBDA(temp_f, DTOF,
    LET(
        D, 0.637427494623679,
        c_l, speed_of_sound(temp_f),
        k_l, kinematic_v(temp_f),
        // c_l^2 * DTOF / (k_l * 91856937)
        (D * c_l ^ 2 * DTOF) / (k_l * 58400000)
    )
);

rn_k_data = {
    0, 0.94; //
    2400, 0.95;
    4000, 0.98;
    400000, 0.998
};

rn_k = LAMBDA(q_gpm, temp_f,
    IF(
        q_gpm = 0,
        1,
        LET(
            rn, reynolds_n(ABS(q_gpm), temp_f),
            idx, SUM(IF(INDEX(rn_k_data, , 1) < rn, 1, 0)),
            x_1, INDEX(rn_k_data, idx, 1),
            x_2, INDEX(rn_k_data, idx + 1, 1),
            y_1, INDEX(rn_k_data, idx, 2),
            y_2, INDEX(rn_k_data, idx + 1, 2),
            interpolate(rn, x_1, x_2, y_1, y_2)
        )
    )
);

vfr_tc = LAMBDA(vfr_gpm, temp_f, id_in, IF(ISNUMBER(vfr_gpm), vfr_gpm * rn_k(vfr_gpm, temp_f), 0));

// ----- Utility Function -----
// --- Range
range = LAMBDA(data, MAX(data) - MIN(data));

// --- Interpolate
interpolate = LAMBDA(x, x_1, x_2, y_1, y_2,
    LET(slope, (y_2 - y_1) / (x_2 - x_1), intcp, y_1 - x_1 * slope, slope * x + intcp)
);

// ### Below is the data particular to the meters ###
data_1245 = {
    1631, 32617, 0;
    5047, 22771, -0.021618901;
    9623, 22882, -0.020543302;
    14393, 23848, -0.003506506;
    21186, 22645, -0.035570534;
    69745, 23123, -0.016232436;
    161650, 22976, -0.035529162;
    999999, 23161, 0.020593577
};

data_1233 = {
    1696, 33921, 0;
    5206, 23401, -0.022477232;
    9994, 23939, -0.017477339;
    14971, 24886, -0.00158811;
    22095, 23746, -0.030467526;
    72333, 23923, -0.023610013;
    167114, 23695, -0.052619522;
    999999, 24175, 0.087212895
};

data_1249 = {
    1642, 32845, 0;
    5109, 23110, -0.021062877;
    9648, 22698, -0.025073138;
    14392, 23718, -0.006793351;
    21292, 23000, -0.025733308;
    69863, 23129, -0.020576361;
    161422, 22890, -0.052148697;
    999999, 23212, 0.045706319
};

data_1248 = {
    1958, 39160, 0;
    5573, 24102, -0.031238513;
    10330, 23782, -0.034353921;
    15180, 24249, -0.025977726;
    22143, 23211, -0.05398566;
    71327, 23421, -0.045422105;
    163188, 22965, -0.105842707;
    999999, 23318, 0.001482998
};

data_1243 = {
    1609, 32171, 0;
    4933, 22166, -0.022567128;
    9442, 22544, -0.018835476;
    14094, 23256, -0.006012844;
    20908, 22716, -0.020431467;
    68605, 22713, -0.020559238;
    158284, 22420, -0.06000197;
    999999, 22528, -0.026209928
};

data_1244 = {
    1586, 31711, 0;
    4986, 22670, -0.019941509;
    9572, 22930, -0.017444477;
    14407, 24177, 0.004078881;
    21301, 22977, -0.027029786;
    70410, 23385, -0.010852418;
    162578, 23042, -0.055703597;
    999999, 23074, -0.046019799
};

data_1241 = {
    1612, 32243, 0;
    5027, 22763, -0.020822558;
    9595, 22844, -0.020041252;
    14385, 23948, -0.000671614;
    21153, 22560, -0.0376263;
    70315, 23410, -0.003577248;
    161387, 22768, -0.088306983;
    999999, 22781, -0.084382981
};

data_1226 = {
    1684, 33684, 0;
    5112, 22853, -0.023699053;
    9896, 23917, -0.013745108;
    14786, 24452, -0.004691159;
    21768, 23274, -0.035301118;
    71449, 23658, -0.020130284;
    163383, 22983, -0.108713036;
    999999, 23102, -0.072199286
};

// ---
meter_calibration = LAMBDA(meter_id,
    SWITCH(
        meter_id,
        1226, data_1226,
        1249, data_1249,
        1245, data_1245,
        1243, data_1243,
        1244, data_1244,
        1233, data_1233,
        1241, data_1241,
        1248, data_1248
    )
);

period_data = LAMBDA(meter_id,
    SWITCH(
        meter_id,
        1245, 0.22,
        1233, 0.203,
        1249, 0.271,
        1248, 0.186,
        1243, 0.254,
        1244, 0.169,
        1241, 0.237,
        1226, 0.152
    )
);