Skip to content

Instantly share code, notes, and snippets.

@jsb2505
Last active June 15, 2023 10:11
Show Gist options
  • Save jsb2505/1e616b709303549e2674ce52555bae01 to your computer and use it in GitHub Desktop.
Save jsb2505/1e616b709303549e2674ce52555bae01 to your computer and use it in GitHub Desktop.
Excel Lambda function to find alpha value
/*
A function to calculate alpha as given by Appendix G in SCI P398
INPUTS: m, m2 and e
RETURNS: value of alpha
Example:
=GetAlpha(40,45,35)
Returns:
5.345
Note:
The last optional parameter is used for recurrsion and should not be inputted by the user.
The function does not check the validity of the input.
The result is accurate to the nearest 0.005.
*/
GetAlpha = LAMBDA(m, m_2, e, [alpha_DONT_INPUT],
LET(
lambda_1, m / (m + e),
lambda_2, m_2 / (m + e),
alpha, IF(ISOMITTED(alpha_DONT_INPUT), 4.45, alpha_DONT_INPUT),
lambda_1_temp, GetLambda1(alpha, lambda_2),
IF(
(lambda_1 / lambda_1_temp) < 1,
GetAlpha(m, m_2, e, alpha + 0.005),
MIN(alpha, 8.00)
)
)
);
GetLambda1 = LAMBDA(alpha, lambda_2,
LET(
lambda_1_lim, 1.25 / (alpha - 2.75),
lambda_2_lim, alpha * lambda_1_lim / 2,
IF(
lambda_2 >= lambda_2_lim,
lambda_1_lim,
lambda_1_lim + (1 - lambda_1_lim) * ((lambda_2_lim - lambda_2) / lambda_2_lim) ^ (0.185 * alpha ^ 1.785)
)
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment