Skip to content

Instantly share code, notes, and snippets.

@jsb2505

jsb2505/Timber.txt

Last active October 3, 2023 21:38
Show Gist options
  • Save jsb2505/5c178fd15d776f03fefdf2f7cda13eb9 to your computer and use it in GitHub Desktop.
Save jsb2505/5c178fd15d776f03fefdf2f7cda13eb9 to your computer and use it in GitHub Desktop.
Download ZIP
A module of timber functions for excel AFE
Raw
Timber.txt
Materials = {"Softwood", "Hardwood", "Glulam", "LVL", "Green_Oak"};
Material_Properties = {
"strength_class",
"f_m_y_k",
"f_v_k",
"f_c_90_k",
"E_0_mean",
"G_mean",
"E_005",
"G_005",
"ρ_k",
"ρ_mean",
"s_factor"
};
Softwood_Table = {
// strength_class, f_m_y_k, f_v_k, f_c_90_k, E_0_mean, G_mean, E_005, G_005, ρ_k, ρ_mean, s_factor
"C14", 14, 3, 2, 7000, 440, 4700, "NA", 290, 350, "NA";
"C16", 16, 3.2, 2.2, 8000, 500, 5400, "NA", 310, 370, "NA";
"C18", 18, 3.4, 2.2, 9000, 560, 6000, "NA", 320, 380, "NA";
"C20", 20, 3.6, 2.3, 9500, 590, 6400, "NA", 330, 400, "NA";
"C22", 22, 3.8, 2.4, 10000, 630, 6700, "NA", 340, 410, "NA";
"C24", 24, 4, 2.5, 11000, 690, 7400, "NA", 350, 420, "NA";
"C27", 27, 4, 2.5, 11500, 720, 7700, "NA", 360, 430, "NA";
"C30", 30, 4, 2.7, 12000, 750, 8000, "NA", 380, 460, "NA";
"C35", 35, 4, 2.7, 13000, 810, 8700, "NA", 390, 470, "NA";
"C40", 40, 4, 2.8, 14000, 880, 9400, "NA", 400, 480, "NA";
"C45", 45, 4, 2.9, 15000, 940, 10000, "NA", 410, 490, "NA";
"C50", 50, 4, 3, 16000, 1000, 10700, "NA", 430, 520, "NA"
};
Hardwood_Table = {
// strength_class, f_m_y_k, f_v_k, f_c_90_k, E_0_mean, G_mean, E_005, G_005, ρ_k, ρ_mean, s_factor
"D18", 18, 3.5, 4.8, 9500, 590, 8000, "NA", 475, 570, "NA";
"D24", 24, 3.7, 4.9, 10000, 630, 8400, "NA", 485, 580, "NA";
"D27", 27, 3.8, 5.1, 10500, 660, 8800, "NA", 510, 610, "NA";
"D30", 30, 3.9, 5.3, 11000, 690, 9200, "NA", 530, 640, "NA";
"D35", 35, 4.1, 5.4, 12000, 750, 10100, "NA", 540, 650, "NA";
"D40", 40, 4.2, 5.5, 13000, 810, 10900, "NA", 550, 660, "NA";
"D45", 45, 4.4, 5.8, 13500, 840, 11300, "NA", 580, 700, "NA";
"D50", 50, 4.5, 6.2, 14000, 880, 11800, "NA", 620, 740, "NA";
"D55", 55, 4.7, 6.6, 15500, 970, 13000, "NA", 660, 790, "NA";
"D60", 60, 4.8, 10.5, 17000, 1060, 14300, "NA", 700, 840, "NA";
"D65", 65, 5, 11.3, 18500, 1160, 15500, "NA", 750, 900, "NA";
"D70", 70, 5, 12, 20000, 1250, 16800, "NA", 800, 960, "NA";
"D75", 75, 5, 12.8, 22000, 1380, 18500, "NA", 850, 1020, "NA";
"D80", 80, 5, 13.5, 24000, 1500, 20200, "NA", 900, 1080, "NA"
};
Glulam_Table = {
// strength_class, f_m_y_k, f_v_k, f_c_90_k, E_0_mean, G_mean, E_005, G_005, ρ_k, ρ_mean, s_factor
"GL24h", 24, 3.5, 2.5, 11500, 650, 9600, 540, 385, 420, "NA";
"GL28h", 28, 3.5, 2.5, 12600, 650, 10500, 540, 425, 460, "NA";
"GL32h", 32, 3.5, 2.5, 14200, 650, 11800, 540, 440, 490, "NA";
"GL36h", 36, 3.5, 2.5, 15000, 650, 12200, 540, 455, 520, "NA";
"GL24c", 24, 3.5, 2.5, 11000, 650, 9100, 540, 365, 400, "NA";
"GL28c", 28, 3.5, 2.5, 12500, 650, 10400, 540, 390, 420, "NA";
"GL32c", 32, 3.5, 2.5, 13500, 650, 11200, 540, 400, 440, "NA";
"GL36c", 36, 3.5, 2.5, 14700, 650, 11900, 540, 430, 465, "NA"
};
LVL_Table = {
// strength_class, f_m_y_k, f_v_k, f_c_90_k, E_0_mean, G_mean, E_005, G_005, ρ_k, ρ_mean, s_factor
"LVL-S44", 44, 5.7, 3.4, 13500, 600, 11600, 400, 480, 510, 0.12;
"LVL-Q28", 28, 5.7, 9, 10000, 600, 8300, 400, 480, 510, 0.12;
"LVL-Q32", 32, 5.7, 9, 10500, 600, 8800, 400, 480, 510, 0.12
};
Green_Oak_Table = {
// strength_class, f_m_y_k, f_v_k, f_c_90_k, E_0_mean, G_mean, E_005, G_005, ρ_k, ρ_mean, s_factor
"TH1", 30, 3, 8, 10000, 600, 8000, "NA", 569, 680, "NA";
"TH2", 24.4, 3, 8, 6720, 420, 6590, "NA", 598, 704, "NA";
"THA", 40, 3.8, 8.8, 11000, 700, 9400, "NA", 595, 713, "NA";
"THB", 30, 3, 8, 10000, 600, 8000, "NA", 584, 692, "NA"
};
Get_Property_Index = LAMBDA(material_property, XMATCH(material_property, Material_Properties));
Get_Properties_Value = LAMBDA(strength_class, material_property, material,
LET(
material_index, XMATCH(material, Materials),
material_table, CHOOSE(
material_index,
Softwood_Table,
Hardwood_Table,
Glulam_Table,
LVL_Table,
Green_Oak_Table
),
strength_class_property_index, Get_Property_Index("strength_class"),
material_property_index, Get_Property_Index(material_property),
strength_classes, CHOOSECOLS(material_table, strength_class_property_index),
strength_class_index, XMATCH(strength_class, strength_classes),
INDEX(material_table, strength_class_index, material_property_index)
)
);
Get_γ_M = LAMBDA(material,
IFS(
OR(material = "Softwood", material = "Hardwood", material = "Green_Oak"),
1.3,
material = "Glulam",
1.25,
material = "LVL",
1.2
)
);
Get_K_c_90 = LAMBDA(material, [bearing_support_condition],
IF(
OR(
ISOMITTED(bearing_support_condition),
bearing_support_condition = "No enhancement",
AND(material <> "Softwood", material <> "Glulam")
),
1,
IFS(
bearing_support_condition = "Continuous",
IF(material = "Softwood", 1.25, 1.5),
bearing_support_condition = "Discrete",
IF(material = "Softwood", 1.5, 1.75)
)
)
);
Get_K_strut = LAMBDA(is_strutted, IF(is_strutted, 0.97, 1));
Get_K_n = LAMBDA(material,
IFS(
OR(material = "Softwood", material = "Hardwood", material = "Green_Oak"),
5,
material = "Glulam",
6.5,
material = "LVL",
4.5
)
);
Get_K_form = LAMBDA(material,
IF(
OR(
material = "Softwood",
material = "Hardwood",
material = "Green_Oak",
material = "Glulam",
material = "LVL"
),
1.2,
NA()
)
);
Get_K_mod = LAMBDA(service_class, load_duration,
LET(
SC, service_class,
LD, MATCH(
load_duration,
{"Permanent", "Long term", "Medium term", "Short term", "Instantaneous"},
0
),
SC_1, {0.6; 0.7; 0.8; 0.9; 1.1},
SC_2, {0.6; 0.7; 0.8; 0.9; 1.1},
SC_3, {0.5; 0.55; 0.65; 0.7; 0.9},
INDEX(CHOOSE(SC, SC_1, SC_2, SC_3), LD)
)
);
Get_K_def = LAMBDA(service_class, material,
IFS(
OR(material = "Softwood", material = "Hardwood", material = "Glulam", material = "LVL"),
CHOOSE(service_class, 0.6, 0.8, 2),
material = "Green_Oak",
CHOOSE(service_class, 1.6, 1.8, 2)
)
);
Get_K_h = LAMBDA(height, material, [LVL_size_factor],
LET(
s, IF(ISOMITTED(LVL_size_factor), 0.12, LVL_size_factor),
IFS(
OR(material = "Softwood", material = "Hardwood", material = "Green_Oak"),
IF(height <= 150, MIN((150 / height) ^ 0.2, 1.3), 1),
material = "Glulam",
IF(height <= 600, MIN((600 / height) ^ 0.1, 1.1), 1),
material = "LVL",
MIN((300 / height) ^ s, 1.2)
// size effect factor for LVL is declared by manufacturer
)
)
);
Get_K_sys = LAMBDA(is_load_sharing, IF(is_load_sharing, 1.1, 1));
Get_K_cr = LAMBDA(material,
IF(
OR(
material = "Softwood",
material = "Hardwood",
material = "Green_Oak",
material = "Glulam"
),
0.67,
1
)
);
Get_K_crit = LAMBDA(relative_slenderness,
LET(
λ_rel_m, relative_slenderness,
IFS(
λ_rel_m <= 0.75,
1,
λ_rel_m <= 1.4,
1.56 - 0.75 * λ_rel_m,
λ_rel_m > 1.4,
1 / λ_rel_m ^ 2
)
)
);
/**
Notch shear strength reduction factor
*/
Get_k_v = LAMBDA(
notch_face,
material,
height,
notch_depth,
bearing_length,
distance_to_notch_from_edge_of_support,
[length_of_sloping_notch],
LET(
k_n, Get_K_n(material),
h, height,
h_n, notch_depth,
h_ef, h - h_n,
l_b, bearing_length,
x, IF(
notch_Face = "Top",
distance_to_notch_from_edge_of_support,
(l_b / 2) + distance_to_notch_from_edge_of_support
),
α, Get_Notch_Ratio(h, h_n),
x_2, IF(ISOMITTED(length_of_sloping_notch), 0, length_of_sloping_notch),
IF(
notch_face = "Top",
1,
/* Possible enhancement strength enhancement possible if using BS 5268-2, Cl 2.10.4
IF(x > h_ef,
1,
(h * (h_ef - x) + (x * h_ef)) / h_ef^2
), */
MIN( // Bottom
(k_n * (1 + 1.1 * (x_2 / (h - h_ef)) ^ 1.5 / SQRT(h))) /
(SQRT(h) * (SQRT(α * (1 - α)) + 0.8 * x * SQRT((1 / α) - α ^ 2) / h)),
1
)
)
)
);
/**
EC5 Eq 6.31
*/
Get_Critical_Bending_Stress = LAMBDA(breadth, height, effective_length, strength_class, material,
LET(
b, breadth,
h, height,
l_eff, effective_length,
E_005, Get_Properties_Value(strength_class, "E_005", material),
I_z, h * b ^ 3 / 12,
I_t, Get_Torsional_Moment_Of_Inertia(b, h),
G_005, Get_G_005(material, strength_class, b, h),
W_el_y, Get_Elastic_Modulus(b, h),
(PI() / (l_eff * w_el_y)) * (SQRT(E_005 * I_z * G_005 * I_t))
)
);
Get_Relative_Slenderness = LAMBDA(characteristic_bending_strength, critical_bending_stress,
LET(
f_m_y_k, characteristic_bending_strength,
σ_m_crit, critical_bending_stress,
SQRT(f_m_y_k / σ_m_crit)
)
);
Get_Constant_For_Unit_Impulse_Velocity = LAMBDA(a_lim,
IF(a_lim <= 1, 180 - 60 * a_lim, 160 - 40 * a_lim)
);
Get_Deflection_Limit_For_1kN_Point_Load = LAMBDA(floor_span,
LET(L, floor_span, IF(L <= 4000, 1.8, 16500 / L ^ 1.1))
);
Get_Fundamental_Frequency = LAMBDA(floor_span, joist_stiffness_per_metre, mass,
LET(
L, floor_span / 10 ^ 3,
EI_l, joist_stiffness_per_metre / 10 ^ 6,
m, mass,
(PI() / (2 * L ^ 2)) * SQRT(EI_l / m)
)
);
Get_Impulse_Velocity_Limit = LAMBDA(b_lim, fundamental_frequency, modal_damping_ratio,
LET(f_1, fundamental_frequency, zeta, modal_damping_ratio, b_lim ^ (f_1 * zeta - 1))
);
Get_Impulse_Velocity_Response = LAMBDA(n_40, mass, floor_width, floor_span,
LET(
m, mass,
b, floor_width / 10 ^ 3,
L, floor_span / 10 ^ 3,
(4 * (0.4 + 0.6 * n_40)) / (m * b * L + 200)
)
);
Get_Instanteous_Deflection_Under_Point_load = LAMBDA(
k_dist,
k_amp,
equivalent_length,
joist_stiffness,
LET(
l_eq, equivalent_length,
EI_J, joist_stiffness,
(1000 * k_dist * l_eq ^ 3 * k_amp) / (48 * EI_J)
)
);
Get_Max_Trimmer_Span = LAMBDA(
span_of_joists,
breadth_of_each_trimmer,
depth_of_trimmer,
grade_of_timber,
LET(
l, span_of_joists,
b, breadth_of_each_trimmer,
h, depth_of_trimmer,
gr, IF(grade_of_timber = "C24", 1.075, 1),
0.165 * (-0.308 * l / 1000 + 3.38) * (0.0214 * b + 1.12) * (0.0149 * h - 0.073) * 1000 * gr
)
);
Get_Max_Trimming_Joist_Span = LAMBDA(
span_of_trimmed_joists,
span_of_untrimmed_joists,
span_of_supported_trimmer_beam,
breadth_of_each_trimmer,
depth_of_trimmer,
grade_of_timber,
LET(
d_1, span_of_trimmed_joists,
d_2, span_of_untrimmed_joists,
d_3, span_of_supported_trimmer_beam,
b, breadth_of_each_trimmer,
h, depth_of_trimmer,
gr, IF(grade_of_timber = "C24", 1.075, 1),
0.032 * (-0.31 * d_3 / 1000 + 3.8) * (-1.52 * d_1 / d_2 + 3.93) * (0.022 * b + 1.79) *
(0.0196 * h - 0.16) * 1000 * gr
)
);
/**
Returns α
*/
Get_Notch_Ratio = LAMBDA(height, notch_depth, LET(h, height, h_n, notch_depth, (h - h_n) / h));
Get_Number_Of_First_Order_Modes = LAMBDA(
fundamental_frequency,
floor_width,
floor_span,
floor_stiffness_per_metre,
joist_stiffness_per_metre,
LET(
f_1, fundamental_frequency,
b, floor_width / 10 ^ 3,
L, floor_span / 10 ^ 3,
EI_b, floor_stiffness_per_metre / 10 ^ 6,
EI_l, joist_stiffness_per_metre / 10 ^ 6,
(((40 / f_1) ^ 2 - 1) * (b / L) ^ 4 * (EI_l / EI_b)) ^ 0.25
)
);
/**
Aspect ratio coefficient for torsional moment of inertia. No exact formulas for non-circular cross-sections exist
*/
Get_Torsion_Coefficient_Beta = LAMBDA(breadth, height,
LET(b, breadth, h, height, (1 / 3) - 0.21 * (b / h) * (1 - (b ^ 4) / (12 * h ^ 4)))
);
Get_Torsional_Moment_Of_Inertia = LAMBDA(breadth, height,
LET(b, breadth, h, height, β, Get_Torsion_Coefficient_Beta(b, h), β * h * b ^ 3)
);
/**
5th percentile shear modulus derived from EC5 Eq6.31,Eq6.32 & EN 384
*/
Get_G_005 = LAMBDA(material, strength_class, breadth, height,
LET(
b, breadth,
h, height,
E_005, Get_Properties_Value(strength_class, "E_005", material),
β, Get_Torsion_Coefficient_Beta(b, h),
α, (48 + (2 / 3)) * β, //https://blogs.napier.ac.uk/cwst/5th-percentile-shear-modulus/
IFS(
material = "Softwood",
E_005 / α,
OR(material = "Hardwood", material = "Green_Oak"),
E_005 / 16,
OR(material = "Glulam", material = "LVL"),
Get_Properties_Value(strength_class, "G_005", material)
)
)
);
Get_Beam_Selfweight_per_m = LAMBDA(breadth, height, material, strength_class,
LET(
b, breadth,
h, height,
ρ_mean, Get_Properties_Value(strength_class, "ρ_mean", material),
ρ_mean * (b / 1000) * (h / 1000) * (9.81 / 1000)
)
);
Get_Shear_Stress = LAMBDA(design_shear_kN, breadth, height, material,
LET(
b, breadth,
h, height,
section_area, b * h,
K_cr, Get_K_cr(material),
(3 * design_shear_kN * 10 ^ 3) / (2 * section_area * K_cr)
)
);
Get_Shear_Strength = LAMBDA(
material,
strength_class,
service_class,
is_load_sharing,
load_duration,
LET(
γ_M, Get_γ_M(material),
K_sys, Get_K_sys(is_load_sharing),
K_mod, Get_K_mod(service_class, load_duration),
f_v_k, Get_Properties_Value(strength_class, "f_v_k", material),
K_mod * K_sys * f_v_k / γ_M
)
);
Get_Bending_Stress = LAMBDA(breadth, height, material, design_moment_kNm,
LET(
b, breadth,
h, height,
W_el_y, Get_Elastic_Modulus(b, h),
design_moment_kNm * 10 ^ 6 / W_el_y
)
);
Get_Bending_Strength = LAMBDA(
height,
material,
strength_class,
service_class,
is_load_sharing,
load_duration,
[LVL_size_factor],
LET(
h, height,
γ_M, Get_γ_M(material),
K_sys, Get_K_sys(is_load_sharing),
K_mod, Get_K_mod(service_class, load_duration),
K_h, IF(
material = "LVL",
Get_K_h(h, material, Get_Properties_Value(strength_class, "s_factor", material)),
Get_K_h(h, material)
),
f_m_y_k, Get_Properties_Value(strength_class, "f_m_y_k", material),
K_h * K_mod * K_sys * f_m_y_k / γ_M
)
);
Get_Buckling_Strength = LAMBDA(
breadth,
height,
effective_length_mm,
material,
strength_class,
bending_strength_kNm,
LET(
b, breadth,
h, height,
L_ef, effective_length_mm,
f_m_y_k, Get_Properties_Value(strength_class, "f_m_y_k", material),
σ_m_crit, Get_Critical_Bending_Stress(b, h, L_ef, strength_class, material),
λ_rel_m, Get_Relative_Slenderness(f_m_y_k, σ_m_crit),
K_crit, Get_K_crit(λ_rel_m),
K_crit * bending_strength_kNm
)
);
Get_Bearing_Stress = LAMBDA(bearing_length_mm, breadth, design_shear_kN,
LET(
l_b, bearing_length_mm,
b, breadth,
bearing_area, b * l_b,
design_shear_kN * 1000 / (bearing_area)
)
);
Get_Bearing_Strength = LAMBDA(
material,
strength_class,
service_class,
load_duration,
[bearing_support_condition],
LET(
γ_M, Get_γ_M(material),
K_mod, Get_K_mod(service_class, load_duration),
K_c_90, IF(
ISOMITTED(bearing_support_condition),
Get_K_c_90(material),
Get_K_c_90(material, bearing_support_condition)
),
f_c_90_k, Get_Properties_Value(strength_class, "f_c_90_k", material),
K_c_90 * K_mod * f_c_90_k / γ_M
)
);
Get_Second_Moment_Of_Area = LAMBDA(breadth, height, LET(b, breadth, h, height, b * h ^ 3 / 12));
Get_Elastic_Modulus = LAMBDA(breadth, height, LET(b, breadth, h, height, b * h ^ 2 / 6));
/* Returns the max deflection from a UDL.
Include service_class argument if instantaneous + creep required.*/
Get_Flexural_Deflection = LAMBDA(
UDL_kN_per_m,
length_mm,
material,
strength_class,
breadth,
height,
[service_class_include_for_creep],
[imposed_combination_factor],
LET(
UDL, UDL_kN_per_m,
L, length_mm,
b, breadth,
h, height,
ϕ_2, IF(ISOMITTED(imposed_combination_factor), 1, imposed_combination_factor),
K_def, IF(ISOMITTED(service_class), 0, Get_K_def(service_class, material)),
E_0_mean, Get_Properties_Value(strength_class, "E_0_mean", material),
I_y, Get_Second_Moment_Of_Area(b, h),
((5 * UDL * L ^ 4) / (384 * E_0_mean * I_y)) * (1 + ϕ_2 * K_def)
)
);
Get_Shear_Deflection = LAMBDA(
UDL_kN_per_m,
length_mm,
material,
strength_class,
breadth,
height,
[service_class],
[imposed_combination_factor],
LET(
UDL, UDL_kN_per_m,
L, length_mm,
b, breadth,
h, height,
k_form, Get_K_form(material),
ϕ_2, IF(ISOMITTED(imposed_combination_factor), 1, imposed_combination_factor),
K_def, IF(ISOMITTED(service_class), 0, Get_K_def(service_class, material)),
G_mean, Get_Properties_Value(strength_class, "G_mean", material),
I_y, Get_Second_Moment_Of_Area(b, h),
((K_form * UDL * L ^ 2) / (8 * G_mean * b * h)) * (1 + ϕ_2 * K_def)
)
);
Get_Final_Deflection = LAMBDA(
permanent_UDL,
imposed_UDL,
length_mm,
material,
strength_class,
breadth,
height,
service_class,
imposed_combination_factor,
LET(
g_k, permanent_UDL,
q_k, imposed_UDL,
L, length_mm,
b, breadth,
h, height,
ϕ_2, imposed_combination_factor,
δ_flex_g, Get_Flexural_Deflection(g_k, L, material, strength_class, b, h, service_class),
δ_flex_q, Get_Flexural_Deflection(
q_k,
L,
material,
strength_class,
b,
h,
service_class,
ϕ_2
),
δ_v_g, Get_Shear_Deflection(g_k, L, material, strength_class, b, h, service_class),
δ_v_q, Get_Shear_Deflection(q_k, L, material, strength_class, b, h, service_class, ϕ_2),
SUM(δ_flex_g, δ_flex_q, δ_v_g, δ_v_q)
)
);
/**
Auto designs timber beam size to smallest depth through a list of standard breadths & depths
*/
Get_Auto_Designed_Timber_Size_List = LAMBDA(
service_class,
load_duration,
is_load_sharing,
strength_class,
material,
permanent_UDL,
imposed_UDL,
is_restrained,
length_mm,
effective_length_factor,
imposed_combination_factor,
deflection_limit,
[b_index],
[h_index],
LET(
L, length_mm,
K_ef, effective_length_factor,
ϕ_2, imposed_combination_factor,
δ_lim, deflection_limit,
L_ef, K_ef * L,
b_index, IF(ISOMITTED(b_index), 1, b_index),
h_index, IF(ISOMITTED(h_index), 1, h_index),
breadths, IFS(
material = "Softwood",
{38, 47, 63, 75},
material = "Hardwood",
{38, 52, 63, 75, 100, 125, 150},
AND(material = "Glulam", h_index <= 4),
{65, 90, 115, 140},
AND(material = "Glulam", h_index <= 5),
{90, 115, 140},
AND(material = "Glulam", h_index <= 7),
{90, 115, 140, 165},
AND(material = "Glulam", h_index <= 8),
{115, 140, 165, 190},
AND(material = "Glulam", h_index > 8),
{140, 165, 190},
material = "LVL",
{27, 33, 39, 45, 51, 57, 63, 75},
AND(material = "Green_Oak", h_index <= 5),
{50, 75, 100, 125, 150},
AND(material = "Green_Oak", h_index > 5),
{100, 125, 150, 175, 200, 225, 250}
),
b_max, COUNT(breadths),
b, INDEX(breadths, b_index),
heights, IFS(
material = "Softwood",
{75, 100, 120, 150, 175, 200, 225, 250, 300},
material = "Hardwood",
{50, 60, 70, 80, 90, 100, 120, 140, 160, 180, 200, 220, 240, 260, 280, 300},
AND(b <= 65, material = "Glulam"),
{225, 270, 315, 360},
AND(b <= 90, material = "Glulam"),
{225, 270, 315, 360, 405, 450, 495},
AND(b <= 115, material = "Glulam"),
{225, 270, 315, 360, 405, 450, 495, 540},
AND(b >= 140, material = "Glulam"),
{225, 270, 315, 360, 405, 450, 495, 540, 585, 630, 675},
material = "LVL",
{200, 260, 300, 360, 400, 450, 500, 600, 900, 1800},
material = "Green_Oak",
{100, 125, 150, 175, 200, 225, 250, 275, 300}
),
h_max, COUNT(heights),
h, INDEX(heights, h_index),
b_index_next, IF(b_index = b_max, 1, b_index + 1),
h_index_next, IF(b_index = b_max, h_index + 1, h_index),
g_swt, Get_Beam_Selfweight_per_m(b, h, material, strength_class),
g_k, permanent_UDL,
g_kt, SUM(g_k, g_swt),
q_k, imposed_UDL,
// Bending
design_moment_kNm, Loading.Get_Design_Bending_Moment(g_kt, q_k, L),
bending_stress, Get_Bending_Stress(b, h, material, design_moment_kNm),
bending_strength, Get_Bending_Strength(
h,
material,
strength_class,
service_class,
is_load_sharing,
load_duration
),
bending_UR, bending_stress / bending_strength,
// Shear
shear_strength, Get_Shear_Strength(
material,
strength_class,
service_class,
is_load_sharing,
load_duration
),
design_shear_kN, Loading.Get_Design_Shear_Force(g_kt, q_k, L),
shear_stress, Get_Shear_Stress(design_shear_kN, b, h, material),
shear_UR, shear_stress / shear_strength,
// LTB
buckling_strength, Get_Buckling_Strength(
b,
h,
L_ef,
material,
strength_class,
bending_strength
),
LTB_UR, IF(OR(b >= h, is_restrained), "Not Req'd", bending_stress / buckling_strength),
//
buckling_strength_next, Get_Buckling_Strength(
b,
h + 5,
L_ef,
material,
strength_class,
bending_strength
),
LTB_UR_next, IF(
OR(b >= h, is_restrained),
"Not Req'd",
bending_stress / buckling_strength_next
),
// Deflection
δ_fin, Get_Final_Deflection(
g_kt,
q_k,
L,
material,
strength_class,
b,
h,
service_class,
ϕ_2
),
δ_UR, δ_fin / δ_lim,
//
results, VSTACK(h, bending_UR, shear_UR, LTB_UR, δ_UR),
//
IF(
AND(bending_UR < 1, shear_UR < 1, δ_UR < 1),
IF(
OR(is_restrained, LTB_UR = "Not Req'd"),
results,
IF(
OR(LTB_UR < 1, LTB_UR_next > LTB_UR), // as h increases LTB gets worse
results,
IF(
AND(b_index = b_max, h_index = h_max),
results,
Get_Auto_Designed_Timber_Size_List(
service_class,
load_duration,
is_load_sharing,
strength_class,
material,
g_k,
q_k,
is_restrained,
L,
K_ef,
ϕ_2,
δ_lim,
b_index_next,
h_index_next
)
)
)
),
IF(
AND(b_index = b_max, h_index = h_max),
results,
Get_Auto_Designed_Timber_Size_List(
service_class,
load_duration,
is_load_sharing,
strength_class,
material,
g_k,
q_k,
is_restrained,
L,
K_ef,
ϕ_2,
δ_lim,
b_index_next,
h_index_next
)
)
)
)
);
/**
Auto designs timber beam size to smallest depth for a given breadth
*/
Get_Auto_Designed_Timber_Size_Depth = LAMBDA(
service_class,
load_duration,
is_load_sharing,
strength_class,
material,
permanent_UDL,
imposed_UDL,
is_restrained,
length_mm,
effective_length_factor,
imposed_combination_factor,
deflection_limit,
breadth,
[height],
LET(
L, length_mm,
K_ef, effective_length_factor,
ϕ_2, imposed_combination_factor,
δ_lim, deflection_limit,
L_ef, K_ef * L,
b, breadth,
h, IF(ISOMITTED(height), 75, height),
g_swt, Get_Beam_Selfweight_per_m(b, h, material, strength_class),
g_k, permanent_UDL,
g_kt, SUM(g_k, g_swt),
q_k, imposed_UDL,
h_next, h + 5, // change value of iteration here
// Bending
design_moment_kNm, Loading.Get_Design_Bending_Moment(g_kt, q_k, L),
bending_stress, Get_Bending_Stress(b, h, material, design_moment_kNm),
bending_strength, Get_Bending_Strength(
h,
material,
strength_class,
service_class,
is_load_sharing,
load_duration
),
bending_UR, bending_stress / bending_strength,
// Shear
shear_strength, Get_Shear_Strength(
material,
strength_class,
service_class,
is_load_sharing,
load_duration
),
design_shear_kN, Loading.Get_Design_Shear_Force(g_kt, q_k, L),
shear_stress, Get_Shear_Stress(design_shear_kN, b, h, material),
shear_UR, shear_stress / shear_strength,
// LTB
buckling_strength, Get_Buckling_Strength(
b,
h,
L_ef,
material,
strength_class,
bending_strength
),
LTB_UR, IF(OR(b >= h, is_restrained), "Not Req'd", bending_stress / buckling_strength),
//
buckling_strength_next, Get_Buckling_Strength(
b,
h + 5,
L_ef,
material,
strength_class,
bending_strength
),
LTB_UR_next, IF(
OR(b >= h, is_restrained),
"Not Req'd",
bending_stress / buckling_strength_next
),
// Deflection
δ_fin, Get_Final_Deflection(
g_kt,
q_k,
L,
material,
strength_class,
b,
h,
service_class,
ϕ_2
),
δ_UR, δ_fin / δ_lim,
//
results, VSTACK(h, bending_UR, shear_UR, LTB_UR, δ_UR),
//
IF(
AND(bending_UR < 1, shear_UR < 1, δ_UR < 1),
IF(
OR(is_restrained, LTB_UR = "Not Req'd"),
results,
IF(
OR(LTB_UR < 1, LTB_UR_next > LTB_UR), // as h increases LTB gets worse
results,
Get_Auto_Designed_Timber_Size_Depth(
service_class,
load_duration,
is_load_sharing,
strength_class,
material,
g_k,
q_k,
is_restrained,
L,
K_ef,
ϕ_2,
δ_lim,
b,
h_next
)
)
),
Get_Auto_Designed_Timber_Size_Depth(
service_class,
load_duration,
is_load_sharing,
strength_class,
material,
g_k,
q_k,
is_restrained,
L,
K_ef,
ϕ_2,
δ_lim,
b,
h_next
)
)
)
);
/**
Auto designs timber beam size to smallest breadth for a given depth
*/
Get_Auto_Designed_Timber_Size_Breadth = LAMBDA(
service_class,
load_duration,
is_load_sharing,
strength_class,
material,
permanent_UDL,
imposed_UDL,
is_restrained,
length_mm,
effective_length_factor,
imposed_combination_factor,
deflection_limit,
height,
[breadth],
LET(
L, length_mm,
K_ef, effective_length_factor,
ϕ_2, imposed_combination_factor,
δ_lim, deflection_limit,
L_ef, K_ef * L,
h, height,
b, IF(ISOMITTED(breadth), 40, breadth),
b_next, b + 5, // change value of iteration here
g_swt, Get_Beam_Selfweight_per_m(b, h, material, strength_class),
g_k, permanent_UDL,
g_kt, SUM(g_k, g_swt),
q_k, imposed_UDL,
// Bending
design_moment_kNm, Loading.Get_Design_Bending_Moment(g_kt, q_k, L),
bending_stress, Get_Bending_Stress(b, h, material, design_moment_kNm),
bending_strength, Get_Bending_Strength(
h,
material,
strength_class,
service_class,
is_load_sharing,
load_duration
),
bending_UR, bending_stress / bending_strength,
// Shear
shear_strength, Get_Shear_Strength(
material,
strength_class,
service_class,
is_load_sharing,
load_duration
),
design_shear_kN, Loading.Get_Design_Shear_Force(g_kt, q_k, L),
shear_stress, Get_Shear_Stress(design_shear_kN, b, h, material),
shear_UR, shear_stress / shear_strength,
// LTB
buckling_strength, Get_Buckling_Strength(
b,
h,
L_ef,
material,
strength_class,
bending_strength
),
LTB_UR, IF(OR(b >= h, is_restrained), "Not Req'd", bending_stress / buckling_strength),
// Deflection
δ_fin, Get_Final_Deflection(
g_kt,
q_k,
L,
material,
strength_class,
b,
h,
service_class,
ϕ_2
),
δ_UR, δ_fin / δ_lim,
//
results, VSTACK(h, bending_UR, shear_UR, LTB_UR, δ_UR),
//
IF(
AND(bending_UR < 1, shear_UR < 1, δ_UR < 1),
IF(
OR(is_restrained, LTB_UR = "Not Req'd"),
results,
IF(
LTB_UR < 1,
results,
Get_Auto_Designed_Timber_Size_Breadth(
service_class,
load_duration,
is_load_sharing,
strength_class,
material,
g_k,
q_k,
is_restrained,
L,
K_ef,
ϕ_2,
δ_lim,
h,
b_next
)
)
),
Get_Auto_Designed_Timber_Size_Breadth(
service_class,
load_duration,
is_load_sharing,
strength_class,
material,
g_k,
q_k,
is_restrained,
L,
K_ef,
ϕ_2,
δ_lim,
h,
b_next
)
)
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment