Last active
August 7, 2023 01:40
-
-
Save sittim/f6d657dc0f660af7ae3ed4657e5116a9 to your computer and use it in GitHub Desktop.
Excel Automation
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// --- 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 | |
) | |
); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment